Импорт цен на конец дня для всех акций NASDAQ в SQL Server

Tags: SQL Server, T-SQL, Python

Thursday, February 15, 2018 10:32:04 PM

Как заполнить таблицу SQL Server с историческими ценами акций и объемами из Yahoo Finance для всех символов тикера NASDAQ? Как генерировать исторические данные нескольких последних лет ит изменить даты начала и окончания? Как определить проблемы с данными для сопоставления символов тикера между сайтом NASDAQ и веб-сайтом Yahoo?

Фондовые аналитики, консультационные центры на фондовом рынке и ведущие финансовые публикации часто обмениваются мнениями о том, какие акции предположительно будут расти и падать в будущем, и когда эти изменения должны произойти. Тем не менее, только фондовый рынок может с абсолютной уверенностью показать курс стоимости акций, и рынок ежедневно представляет нам данные о ценах и объемах в конце дня на бегущей строке. В результате заполнение таблицы SQL Server фактическими данными о ценах и объемах на конец дня для символов биржевого тикера может стать достаточно полезным . Символ (или значение) тикера представляет собой буквенно-цифровую строку, которая является коротким псевдонимом для имени компании.

SQL Server отлично подходит для хранения материала, в том числе исторических  биржевых цен, но он менее подходит для непосредственного взаимодействия с Google Finance и Yahoo Finance для загрузки исторических цен на множество различных символов тикера. С другой стороны, у Python есть специальные библиотеки, предназначенные для упрощения извлечения истории цен на акции и объема из Yahoo Finance и Google Finance. В этой статье объясняется, как использовать Python для сбора цен на символы тикера от Yahoo Finance и размещения собранных цен в SQL Server. Анализируя прошлую производительность, вы можете усилить свои возможности для выбора акций, которые позволят быстро и безопасно увеличить ваше благосостояние. Разумеется, подобные анализы могут помочь инвесторам, которые ищут рекомендации о том, как покупать и продавать акции для личной выгоды.

Администраторы баз данных и разработчики, использующие Microsoft SQL Server для хранения данных, а также Python для сбора данных с веб-сайтов, владеют инструментами для построения наборов данных о ценах и объемах тикера. Мы покажем,  как автоматизировать совокупность таблицы SQL Server со всеми символами тикера для обмена NASDAQ. Этот обмен очень популярен среди частных инвесторов, особенно тех, кто предпочитает быстрорастущие акции для технических компаний. На момент подготовки этой статьи имеется около 3 300 символов NASAQ.  Веб-сайт Yahoo Finance взаимодействует с внешней библиотекой pandas_datareader для Python, чтобы возвращать исторические данные о ценах и объеме для символов тикера в табличном формате. Данные собираются с первого торгового дня в 2014 году до начала ноября 2017 года, но код, который здесь представлен, позволяет легко изменить дату начала и окончания сбора данных.

Здесь представлен двухэтапный подход для сбора исторических данных о ценах и объемах для символов титров NASDAQ. Каждый шаг имеет три связанных с ним элемента кода:

  • сценарий Python для сбора данных с веб-сайта NASDAQ или веб-сайта Yahoo Finance
  • короткий командный файл Windows для вызова скрипта Python, который выполняет сбор данных
  • сценарий T-SQL, который может запускать пакетный файл Windows изнутри SQL Server Management Studio и заполнять таблицу SQL Server извлеченными и отобранными данными

Настройка для использования Python с SQL Server

Одна из важных причин, по которой Python настолько силен, объясняется наличием большого набора внешних библиотек, которые добавляют функциональность, не предоставленную встроенной библиотекой Python. Внешняя библиотека pandas_datareader облегчает сбор исторических цен и объемов как из Google Finance, так и из Yahoo Finance (а также других источников финансовых и экономических данных в Интернете). Внешняя библиотека BeautifulSoup облегчает чтение и загрузку содержимого HTML из URL-адресов, например, на веб-сайте NASDAQ с данными для всех перечисленных компаний. Прежде чем вы сможете использовать внешнюю библиотеку Python, вы должны

  • установить его на компьютер
  • ссылаться на него с помощью скрипта Python

Получение всех тикеров NASDAQ

Здесь мы используем три файла программы для создания файла txt со всеми значениями тикета NASDAQ.

  1. Все компании Nasdaq с beautifulsoup.py представляют собой файл сценария Python для отображения значений тикера и связанной информации для всех акций, перечисленных для торговли на бирже NASDAQ. Сценарий:
  • Направляет на URL, содержащий HTML-таблицу со строками для каждой компании, указанной на бирже NASDAQ
  • Использует внешнюю библиотеку BeautifulSoup для чтения таблицы HTML и отображения значений полей столбца таблицы с информацией о компаниях, зарегистрированных в NASDAQ.
  • Создает поток текстового контента с четырьмя строками для каждой зарегистрированной в NASDAQ компании; одна строка в каждом наборе из четырнадцати содержит значение тикера для компании.
  1. Все компании Nasdaq с beautifulsoup.bat представляют собой командный файл Windows с двумя командами.
  • Во-первых, он изменяет текущий каталог на папку с помощью интерпретатора Python (python.exe).
  • Во-вторых, он использует интерпретатор Python для запуска всех Nasdaq-компаний с файлом beautifulsoup.py. Его результат заносится в оболочку Windows, на которой запущен файл сценария Python.
  • Сохраняя все файлы сценариев Python в папке на компьютере под управлением SQL Server, вы делаете файлы сценариев Python доступными для упрощения ссылок по сценариям T-SQL. Здесь мы используем каталог C: \ python_programs для хранения файлов сценариев Python и файлов с соответствующим контентом.
  1. Сохраните  Nasdaq тикеры в file.sql -  файле sql с T-SQL-скриптом. Сценарий T-SQL:
  • Запускает все компании Nasdaq с файлом beautifulsoup.bat и сохраняет исходные результаты из командного файла Windows в таблице #Result SQL Server
  • Обрабатывает таблицу #Result для сохранения выбранной информации о зарегистрированных в NASDAQ компаниях, включая их значения тикера, в таблице SQL Server с именем #pivotsource; один столбец в таблице #pivotsource имеет значения тикера NASDAQ
  • Обрабатывает таблицу #pivotsource и создает новую таблицу (## nasdaq_tickers), содержащую только значения тикера для всех акций, включенных в NASDAQ; значения из таблицы ## nasdaq_tickers экспортируются в файл nasdaq_tickers.txt в пути c: \ python_programs

Ниже приведен список содержимого всех компаний Nasdaq с файлом beautifulsoup.py. Этот сценарий генерирует необработанный результат для потребления сценарием в пакетном файле Windows, отображающем все компании Nasdaq с beautifulsoup.bat.

#import BeautifulSoup
from bs4 import BeautifulSoup

#import request
import requests

#specify a web page from which to harvest ticker symbols
#and submit the returned html to BeautifulSoup for parsing
r=requests.get('http://www.nasdaq.com/screening/companies-by-industry.aspx?exchange=NASDAQ&pagesize=4000')
data = r.text
soup = BeautifulSoup(data, "html.parser")

#select a specific table from the html page
#loop through rows of the table
#loop through the cells of a row
#results in 14 lines of text per symbol

table = soup.find( "table", {"id":"CompanylistResults"} )

for row in table.findAll("tr"):
    for cell in row("td"):
        print (cell.get_text().strip())

 

Цель всех nasdaq-компаний с файлом beautifulsoup.bat - предоставить дескриптор для SQL Server Management Studio, чтобы иметь возможность вызывать все  nasdaq-компании с файлом beautifulsoup.py. Ниже приведены две команды в пакетном файле.

cd C:\Program Files\Python36python.exe "C:\python_programs\all nasdaq companies with beautifulsoup.py"

Как указано выше, триггерные части T-SQL-скрипта в тире сохраняются в файле file.sql. Выборка ниже для первой части фиксирует исходный вывод всех компаний Nasdaq с файлом beautifulsoup.bat.

Этот сегмент скрипта использует расширенную хранимую процедуру xp_cmdshell для запуска всех Nasdaq-компаний с файлом beautifulsoup.bat и сохраняет исходный результат в таблице #Result.

-- pull tickers and related data from nasdaq.com

-- enable the xp_cmdshell stored procedure
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE

--------------------------------------------------------------------------------------

-- create a fresh temp table (#Result) for storing unprocessed result set from
-- running xp_cmdshell for Windows batch that invokes
-- python script
if exists (
 select * from tempdb.dbo.sysobjects o
 where o.xtype in ('U')
and o.id = object_id(N'tempdb..#Result')
)
BEGIN
  DROP TABLE #Result;
END

CREATE TABLE #Result
(
  line varchar(500)
)

--------------------------------------------------------------------------------------

-- insert python script output into #Result temp table
INSERT #Result exec xp_cmdshell 'C:\python_programs\"all nasdaq companies with beautifulsoup.bat"'

--------------------------------------------------------------------------------------

-- disable the xp_cmdshell stored procedure
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
GO

Второй отрывок из файла Nasdaq для сохранения Nasdaq в файл file.sql показывает, как обрабатывать исходный вывод в таблице #Result для коллекции столбцов в другой таблице SQL Server (#pivotsource), которая включает информацию обо всех перечисленных в NASDAQ компаниях, включая их значения тикера.

-- setup to clean and extract tickers
-- from raw nasdaq.com stock listing

use tempdb

begin try
   drop sequence dbo.CountBy1_to_delete_row_number
end try
begin catch
   print 'CountBy1_to_delete_row_number'
end catch

begin try
   drop table #Result_with_to_delete_row_number
end try
begin catch
   print '#Result_with_to_delete_row_number'
end catch

begin try
   drop table #pivotsource
end try
begin catch
   print '#pivotsource'
end catch

create sequence CountBy1_to_delete_row_number
    start with 1  
    increment by 1;  

select
  next value for CountBy1_to_delete_row_number AS source_row_number
  ,line
into #Result_with_to_delete_row_number
from #Result

declare @max_source_row_number int = (select max(source_row_number) from #Result_with_to_delete_row_number)

-- pull column values into #pivotsource with names for
-- COMPANY NAME, TICKER, MARKET CAP, COUNTRY, IPO YEAR and INDUSTRY
-- for nasdaq.com stocks

select
   tickers.TICKER_NUMBER
   ,
   case
when row_number_within_ticker = 0 then 'COMPANY NAME'
 when row_number_within_ticker = 1 then 'TICKER'
 when row_number_within_ticker = 2 then 'MARKET CAP'
 when row_number_within_ticker = 4 then 'COUNTRY'
 when row_number_within_ticker = 5 then 'IPO YEAR'
 when row_number_within_ticker = 6 then 'INDUSTRY'
   end ticker_ids
   ,ticker_value
into #pivotsource
from
(
-- ticker values for ticker_number values
select
   (source_row_number-5) / 14 TICKER_NUMBER
   ,line TICKER
from #Result_with_to_delete_row_number
where
  source_row_number != @max_source_row_number
   and source_row_number not between 1 and 4
   and ((source_row_number-5) % 14) = 1
) tickers

left join

(
select
   (source_row_number-5) / 14 TICKER_NUMBER
   ,((source_row_number-5) % 14) row_number_within_ticker
   ,line ticker_value
from #Result_with_to_delete_row_number
where
  source_row_number != @max_source_row_number
   and source_row_number not between 1 and 4
   and ((source_row_number-5) % 14) in (0, 1, 2, 4, 5, 6)
) ticker_identifiers

on tickers.TICKER_NUMBER = ticker_identifiers.TICKER_NUMBER
order by tickers.TICKER_NUMBER, row_number_within_ticker

Последняя часть этого скрипта (см. ниже) - это оригинальный код, специально разработанный для этой статьи. Код выполняет три действия.

  1. Он начинается с удаления любой предыдущей версии таблицы ## nasdaq_tickers.
  2. Затем он копирует значения столбцов с значениями тикера NASDAQ из таблицы #pivotsource в таблицу ## nasdaq_tickers. В результате таблица ## nasdaq_tickers имеет все значения тикера с сайта NASDAQ во время запуска приложения.
  3. Наконец, код иллюстрирует использование расширенной хранимой процедуры xp_cmdshell и команды bcp для копирования содержимого таблицы ## nasdaq_tickers в файл nasdaq_tickers.txt в пути c: \ python_programs.

-- filter #pivotsource to extract just
-- nasdaq ticker values into ##nasdaq_tickers
if exists (
 select * from tempdb.dbo.sysobjects o
 where o.xtype in ('U')
and o.id = object_id(N'tempdb..##nasdaq_tickers')
)
BEGIN
  drop table ##nasdaq_tickers;
END

-- save ticker_value from #pivotsource in ##nasdaq_tickers
select ticker_value into ##nasdaq_tickers from #pivotsource where ticker_ids = 'TICKER'

-- enable the xp_cmdshell stored procedure
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE

-- write a text file with the ticker symbols
-- for use by read_all_nasdaq_tickers_from_2014_and_download_stock_prices_with_try_catch_any.py
EXEC master..xp_cmdshell
'bcp "select ticker_value from ##nasdaq_tickers" queryout c:\python_programs\nasdaq_tickers.txt -c -T'

-- disable the xp_cmdshell stored procedure
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
GO  

По состоянию на момент сохранения тикеров  Nasdaq в файле  file.sql для этой статьи, в исходной таблице HTML на сайте nasdaq.com было указано значение 3297 значений NASDAQ. Следующие два снимка экрана показывают первые десять значений тикера и последние девять значений тикера в файле nasdaq_tickers.txt, отображенных в текстовом редакторе Notepad ++. Номера строк не являются частью файла. Другой файл сценария Python может прочитать этот файл, чтобы получить исторические данные о ценах и объемах из Yahoo Finance для всех значений тикера NASDAQ.

Загрузка из Yahoo Finance исторических цен и объема данных для всех NASDAQ тикеров

Процесс загрузки исторических данных о ценах и объемах из Yahoo Finance в таблицу SQL Server основывается на предыдущем совете, где было показано, как загружать данные о исторических ценах за 150 центов акций, которые были выбраны вручную из списка популярных акций. Этот совет расширяет предыдущий тремя способами.

  • Во-первых, он получает как исторические данные о ценах, так и об объеме. В предыдущем совете было показано, как загружать исторические данные только о ценах.
  • Во-вторых, здесь получены данные для всех акций, торгуемых NASDAQ, для которых у Yahoo есть данные о ценах и объемах. Это представляет собой более чем двадцатикратное увеличение числа значений в тикерах, для которых загружаются данные. Фактически, в конечном наборе загруженных данных для этого совета есть более 2,6 миллиона строк данных о ценах и объёме.
  • В следующем разделе рассматриваются незначительные несоответствия для наличия символов тикера между nasdaq.com и Yahoo Finance. Предыдущий совет не сравнивал значения тикера от Yahoo Finance с фондами на фондовой бирже, такими как обмен NASDAQ.

Как и в процессе загрузки всех тикеров NASDAQ, процесс загрузки исторических данных о ценах и объемах зависит от трех файлов сценариев.

- Файл read_all_nasdaq_tickers_from_2014_and_download_stock_prices_with_try_catch_any.py содержит сценарий Python для загрузки данных о ценах и объемах из Yahoo Finance. Как уже упоминалось, этот сценарий сильно зависит от сценария из предыдущего совета. Однако здесь есть еще четыре модификации..

  • Наиболее значительным изменением для общей работы является источник значений тикера. В предыдущем совете источник состоял из 150 значений тикера, которые, как известно, были в Yahoo Finance. Требование для списка тикеров этого совета состоит в том, что оно содержит все значения тикера в указанной таблице HTML на сайте NASDAQ. Эта таблица HTML содержит информацию о компании для всех акций, включенных в NASDAQ. Новый список тикеров более чем в двадцать раз больше и составляет 3,297. Примерно один процент из этих 3 297 тикеров не был найден в Yahoo финансах.
  • Сценарий Python для этого совета был обновлен для выбора значений тикера из файла nasdaq_tickers.txt, который включает все значения тикера из таблицы HTML на веб-сайте NASDAQ с информацией обо всех компаниях, зарегистрированных в NASDAQ.
  • Частично в ответ на двадцатикратное увеличение числа тикеров, для которых были получены исторические данные, в этом совете по сравнению с предыдущим была изменена дата старта с начала 2007 года до начала 2014 года. Эта модификация позволила процессу собирать меньше данных и, следовательно, сокращать время выполнения.
  • Поскольку предыдущий совет с 150 значениями тикера включал только те же символы тикера что и в Yahoo Finance, сценарий в том совете никогда не имел дело с тикером, отсутствующим в Yahoo Finance. Однако текущий совет  имеет входной файл, в котором значения тикера не экранированы предварительно, поэтому он не исключает любые символы тикера, отсутствующие в Yahoo Finance.  Когда Yahoo Finance не вернул набор данных в ответ на историю для значения тикера, решение должно было повторить попытку столько раз, сколько необходимо, пока Yahoo Finance не будет обслуживать запрошенные данные. Сценарий Python для этого совета был изменен, чтобы проводить отличие между:
  • повтором попытки необходимого количества раз в случае, если  исторические данные недоступны после первой попытки и
  • обходом бесконечного цикла повторных попыток, если значение тикера с веб-сайта NASDAQ в Yahoo Finance  отсутствует после разумного количества попыток

- Read_all_nasdaq_tickers_from_2014_and_download_stock_prices_with_try_catch_any.bat - это код-оболочка для запуска файла read_all_nasdaq_tickers_from_2014_and_download_stock_prices_with_try_catch_any.py в командном окне DOS. Этот файл bat имеет две команды, которые выполняют обычные функции для командного файла Windows, используемого для вызова файла сценария Python.

  • Он изменяет текущий каталог на тот, в котором находится интерпретатор Python (python.exe).
  • Затем он запускает файл сценария Python (read_all_nasdaq_tickers_from_2014_and_download_stock_prices_with_try_catch_any.py) с интерпретатором Python, возвращая выход из сценария Python в командное окно DOS.

- Файл read_all_nasdaq_ticker_historical_prices_and_volumes.sql содержит сценарий T-SQL. Существует два ключевых шага к сценарию T-SQL.

  • Он вызывает расширенную хранимую процедуру xp_cmdshell для сбора необработанного вывода из файла read_all_nasdaq_tickers_from_2014_and_download_stock_prices_with_try_catch_any.bat. Исходные выходные значения сохраняются в таблице #Result.
  • Затем он обрабатывает исходный вывод в таблице #Result для заполнения новой таблицы SQL Server с именем ## Results_with_extracted_casted_values. Эта таблица содержит семь столбцов с строкой для каждого тикера NASDAQ и отдельную комбинацию дат.  Восьмой столбец содержит исходный необработанный вывод из пакетного файла, который запускает скрипт Python. Этот восьмой столбец предназначен для облегчения проверки извлеченных, отобранных значений ключевого столбца из исходного вывода в таблице #Result. Семь ключевых столбцов включают:
  • Дата
  • символ (для значения тикера)
  • цена открытия
  • высокая цена
  • низкая цена
  • цена закрытия
  • объем
  • В предыдущем совете был опубликован объем акций, проданных для каждого из 150 значений тикера в сценарии Python, но там не было показано, как извлекать и вычислять значения томов, как делает сценарий T-SQL-скрипт в этом контенте.

Ниже приведен список кодов для файла read_all_nasdaq_tickers_from_2014_and_download_stock_prices_with_try_catch_any.py. Этот файл содержит скрипт Python для чтения исторических данных о ценах и объемах из Yahoo Finance для всех значений тикера NASDAQ. Этот сценарий основывается на аналогичном сценарии Python, описанном в предыдущем совете, по сбору исторических данных для меньшего предварительно отобранного набора значений тикера. Таким образом, вы можете получить базовое понимание кода из сценария A Python для сбора исторических цен для многих тикеров из раздела Yahoo Finance в предыдущем совете. Обсуждение сценария здесь сосредоточено на изменениях кода, выполненных с учетом особых требований этого совета.

В третьем сегменте в следующем скрипте код заполняет объект списка символов Python с помощью записей из файла nasdaq_tickers.txt в папке c: \ python_programs. Как видно из первой пары скриншотов этого совета,  в этом файле содержится всего 3,297 символов тикера. Предыдущий совет, содержащий аналогичный код для чтения исторических данных из Yahoo Finance, основывался на одном из двух разных файлов в качестве источников для заполнения объекта списка символов. Один из этих файлов содержал 150 значений тикера, а в другом файле было всего 20 значений тикера.

Четвертый сегмент в следующем сценарии устанавливает т 1 января 2014 в качестве даты начала для сбора  исторические данные из Yahoo Finance. В предыдущем совете по сбору исторических данных из Yahoo Finance начальная дата была установлена ​​на 1 января 2007 года - на семь лет раньше. Несмотря на то, что предыдущий отзыв использовал исторические данные еще семи лет, окончательный набор результатов содержал менее 360 000 строк. С другой стороны, окончательный набор результатов для этого совета содержал более 2.6 миллионов строк. Это огромное несоответствие, вызванное огромным количеством символов тикера в этом  совете по сравнению с предыдущим, привело к времени выполнения равному примерно 45 минут для этого совета против менее 10 минут для предыдущего. По мере добавления дополнительных значений тикера, например, путем включения символов тикера из других обменов, кроме NASDAQ, или удлинения периода, для которого собираются исторические данные, желательно разрабатывать методы выполнения расписания в периоды непикового использования и / или запускать привлечение разных наборов исторических данных для одновременной работы.

Возможно, самым значительным изменением с точки зрения кодирования была ревизия для размещения значений тикера, которые были в файле nasdaq_tickers.txt, но не представлены в исторических данных из Yahoo Finance. В предыдущем совете отмечено, что использование значения тикера, отсутствовавшего в Yahoo Finance, приведет к тому, что исходный код в конечном сегменте для предыдущего совета войдет в бесконечный цикл. В совете описано, как избежать этого результата, исключив значение тикера, которое еще не было в исторической базе данных Yahoo Finance.

Для текущего совета было затруднительно проверить,  не пропали ли отдельные значения тикера из Yahoo Finance, потому что запуск полного набора значений кода NASDAQ занял слишком много времени, и в конечном итоге было найдено более 30 значений тикера, которые существовали на nasdaq.com, но не в Yahoo Finance с историческими данными. Эти недостающие значения тикера от Yahoo Finance с историческими данными потребовали динамического решения для игнорирования выбранных значений тикера, если в Yahoo Finance не было найдено никаких исторических данных после разумного количества попыток получить их.

Из повторных наблюдений при разработке предыдущего совета было обнаружено, что никогда не было более трех повторных попыток, необходимых для окончательного извлечения исторических данных для значения тикера, если они существовали в Yahoo Finance.  В результате в коде этого совета был введен новый счетчик для определения количества повторных попыток для текущей попытки получить исторические данные для значения тикера. Когда количество повторных попыток превысило десять для значения тикера, код переставал пытаться получить исторические данные.

  • Переменная с именем j подсчитывает количество повторных попыток для тикера
  • Если количество повторных попыток для тикера меньше или равно десяти, и текущая попытка не возвращает набор данных из Yahoo Finance, тогда управление переходит к оператору continue, чтобы запустить другую попытку сбора исторических данных для значения тикера
  • В противном случае,  если количество повторных попыток больше десяти, и текущая попытка не вернула набор данных из Yahoo Finance, то
  • Команда Print печатает значение тикера, для которого совпадение не найдено в Yahoo Finance
  • управляет потоком в утверждение pass, что приводит к
  • перепроверке индексную переменную j, сбросив до 0
  • увеличении на единицу переменной индекса символа i,

#settings to dynamically display all rows and columns
#in a single tabular display of the dataframe
import pandas as pd
pd.set_option('display.max_rows', None)
pd.set_option('expand_frame_repr', False)

#settings for importing built-in datetime and date libraries
#and external pandas_datareader libraries
import datetime
from datetime import date
import pandas_datareader.data as web

#read symbols from the nasdaq_tickers.txt file to a python list
symbol = []
with open('c:/python_programs/nasdaq_tickers.txt') as f:  
    for line in f:
        symbol.append(line.strip())
f.close

#setting for start and end dates
#you can designate a specific date
#such as 2014, 1, 1
#or a relative date such as today less 5 days

start = datetime.date(2014, 1, 1)
#start = date(date.today().year, date.today().month, date.today().day-5)

end = datetime.date.today()

#iterate over ticker symbol values
#with while for retrieving historical and
#displaying stock prices
#try code and catch any exception
#try again (continue) after catch
i=0  # for iterating through tickers
j=0  # for iterating through tries for a ticker
while i

Файл read_all_nasdaq_tickers_from_2014_and_download_stock_prices_with_try_catch_any.bat имеет только две команды, которые отображаются ниже. Первая команда изменяет текущий каталог на путь, в котором находится интерпретатор python. Вторая команда использует интерпретатор Python для вызова сценария Python для извлечения исторических данных для набора значений тикера. Этот код возвращает все исторические данные для значений тикера в командной оболочке Windows.

cd C:\Program Files\Python36python.exe "C:\python_programs\read_all_nasdaq_tickers_from_2014_and_download_stock_prices_with_try_catch_any.py"

Третий файл сценария в этой части решения имеет имя read_all_nasdaq_ticker_historical_prices_and_volumes.sql. Он содержит сценарий T-SQL, который можно запустить из SQL Server Management Studio. Этот скрипт имеет только два сегмента.

Скрипт для первого сегмента показан ниже. Если вы сравните этот первый сегмент сценария T-SQL для получения исторических данных в первом сегменте сценариев T-SQL для получения всех тикеров NASDAQ, вы заметите, что оба примера кода почти одинаковы. Цель первого сегмента в этом разделе - запустить файл read_all_nasdaq_tickers_from_2014_and_download_stock_prices_with_try_catch_any.bat и поместить исходный вывод из командного файла в таблицу #Result. Он достигает этой цели, вызывая расширенную хранимую процедуру xp_cmdshell для командного файла.

-- enable the xp_cmdshell stored procedure
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE

--------------------------------------------------------------------------------------

-- create a fresh temp table (#Result) for storing unprocessed result set from
-- running xp_cmdshell for Windows batch that invokes
-- python script
if exists (
 select * from tempdb.dbo.sysobjects o
 where o.xtype in ('U')
and o.id = object_id(N'tempdb..#Result')
)
BEGIN
  DROP TABLE #Result;
END

CREATE TABLE #Result
(
  line varchar(500)
)

--------------------------------------------------------------------------------------

-- insert python script output into #Result temp table
INSERT #Result exec xp_cmdshell 'C:\python_programs\"read_all_nasdaq_tickers_from_2014_and_download_stock_prices_with_try_catch_any.bat"'

--------------------------------------------------------------------------------------

-- disable the xp_cmdshell stored procedure
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
GO

Далее появится второй сегмент файла T-SQL. Этот блок кода изменился больше, чем предыдущий сегмент, который просто передал содержимое командной строки Windows в таблицу SQL Server (#Result). Второй сегмент демонстрирует, как анализировать и сохранять в новую таблицу (## Results_with_extracted_casted_values) извлеченные, отобранные исторические значения из необработанного вывода в строках таблицы #Result. В этом совете был извлечен новый столбец для объемных данных, а метод отбора для столбцов также был слегка изменен в сравнении с предыдущим советом. Из-за объема этих модификаций код второго сегмента описывается так, как если бы он был совершенно новым для этого совета.

  • Сценарий начинается с удаления любой предыдущей версии таблицы ## Results_with_extracted_casted_values
  • Затем серия из шести вложенных подзапросов последовательно передает свои результирующие множества друг другу и, в конечном счете, внешний запрос
  • Вложенные имена подзапросов от самого внутреннего до внешнего
  • extract_date_and_symbol
  • params_for_open
  • params_for_high
  • params_for_low
  • params_for_close
  • param_for_volume
  • Подзапрос extract_date_and_symbol
  • получает исходный код из строк таблицы #Result
  • извлекает значения даты и символа из строк таблицы #Result и выдает их соответственно в виде типов данных даты и varchar
  • добавляет исходное значение строки таблицы #Result для дальнейшей обработки с помощью последующих подзапросов
  • исключает из набора результатов #Result строки без значения начальной даты и последующего значения символа
  • Подзапрос params_for_open
  • получает свой источник из набора результатов результирующего запроса extract_date_and_symbol, который включает в себя дату, символ и исходное необработанное значение строки из таблицы #Result; обратите внимание на сегмент кода, расположенного выше: таблица #Result имеет один столбец с именем line
  • вычисляет начальные (открытые_1) и конечные (open_plus_1) значения позиции для значения цены открытия в строке
  • Параметр params_for_high
  • получает исходный код из набора результатов запроса params_for_open
  • вычисляет значение открытой цены из начальной и конечной позиции и добавляет его в свой результирующий набор вместе со значениями исходного столбца
  • вычисляет начальные (высокие_1) и конечные (high_plus_1) значения позиции для высокого значения цены в строке
  • Параметр params_for_low
  • получает свой источник из набора результатов params_for_high sub-query
  • вычисляет высокое значение цены из начальной и конечной позиции и добавляет его в свой результирующий набор вместе со значениями исходного столбца
  • вычисляет начальные (low_1) и конечные (low_plus_1) значения позиции для низкого значения цены в строке
  • Параметр params_for_close
  • вычисляет начальные (close_1) и конечные (close_plus_1) значения позиции для значения цены закрытия в строке
  • вычисляет низкое значение цены из его начальной и конечной позиции и добавляет его в свой результирующий набор вместе со значениями исходного столбца
  • получает свой источник из набора результатов params_for_low sub-query
  • Подзапрос param_for_volume
  • вычисляет начальное (объем_1) значение позиции для значения объема в строке; конечная позиция безоговорочно является окончанием значения столбца строки, поскольку том является последним значением в строке в таблице #Result
  • вычисляет значение цены закрытия из значений начальной и конечной позиции и добавляет их в свой результирующий набор вместе со значениями исходного столбца
  • получает свой источник из набора результатов params_for_close sub-query
  • Внешний запрос
  • отбрасывает четыре цены (открытия, закрытия, высокие и низкие) в виде денежных значений после преобразования их в значения float; Yahoo Finance использует значения float для представления значений исторических цен
  • вычисляет значение объема из его начальной и неявной конечной позиции перед преобразованием его в значение float, которое преобразуется в значение int; Yahoo Finance иногда представляет значения объема с десятичной точкой, хотя значения объема - это целые количества акций
  • исходное необработанное значение строки из таблицы #Result возвращается для облегчения проверки извлеченных, отобранных значений, отображаемых в ## Results_with_extracted_casted_values

-- extract and cast stock open, high, low, close prices
-- and volume for all nasdaq tickers
begin try
drop table ##Results_with_extracted_casted_values
end try
begin catch
print '##Results_with_extracted_casted_values was not deleted'
end catch

-- cast open, high, low, close as money
-- extract volume and cast as int
-- retain line value for rows to validate data
select
 date
,symbol
,cast(cast([open] as float) as money) [open]
,cast(cast(high as float) as money) high
,cast(cast(low as float) as money) low
,cast(cast([close] as float) as money) [close]
,cast(cast(substring(line,volume_1,500) as float) as int) volume
,line
into ##Results_with_extracted_casted_values
from

(
-- extract close and add to date, symbol, open, high, low
-- get parameter for extracting volume
select
 date
,symbol
,[open]
,high
,low
,substring(line, close_1, close_plus_1 - close_1) [close]
,close_plus_1
,close_plus_1
 +patindex('%[0-9]%',substring(line,close_plus_1,500))-1 [volume_1]
,line
from
(
-- extract low and add to date, symbol, open, high
-- get parameters for extracting close
select
 date
,symbol
,[open]
,high
,substring(line,low_1,low_plus_1 - low_1) low
,low_plus_1
 +
 patindex('%[0-9]%',substring(line,low_plus_1,500))
 -1
 close_1
,charindex
 (
 ' '
 ,line
 ,low_plus_1
 +
 patindex('%[0-9]%',substring(line,low_plus_1,500))
 -1
 ) close_plus_1
,low_plus_1

,line

from
(
-- extract high and add to date, symbol, open
-- get parameters for extracting low
select
 date
,symbol
,[open]
,substring(line,high_1,high_plus_1 - high_1) high
,
 high_plus_1
 +
 patindex('%[0-9]%',substring(line,high_plus_1,500))
 -1
 low_1
,
 charindex
 (
 ' '
 ,line
 ,high_plus_1
 +
 patindex('%[0-9]%',substring(line,high_plus_1,500))
 -1
 ) low_plus_1
,line
from
(
-- extract open and add to date, symbol
-- get parameters for extracting high
select
 date
,symbol
,substring(line,open_1,open_plus_1-open_1) [open]
,
 open_plus_1
 +
 patindex('%[0-9]%',substring(line,open_plus_1,500))
 -1
 high_1
,charindex
 (
 ' '
 ,line
 ,
 open_plus_1
 +
 patindex('%[0-9]%',substring(line,open_plus_1,500))
 -1
 ) high_plus_1
,line
from
(
-- get parameters for extracting open
select
date
,symbol
,charindex(' ',line, patindex('%[A-Z]%',line))
  +
 patindex('%[0-9]%',substring(line,charindex(' ',line, patindex('%[A-Z]%',line)),500))
 -1 [open_1]
,
 charindex
 (
 ' '
 ,line
 ,
 (
 charindex(' ',line, patindex('%[A-Z]%',line))
  +
 patindex('%[0-9]%',substring(line,charindex(' ',line, patindex('%[A-Z]%',line)),500))
 -1 )
 ) open_plus_1
,line

from
(
-- get date and symbol for returned data
select
cast(substring(line,1,10) as date) [date]
,cast
 (
 substring
 (
 line,patindex('%[A-Z]%',line),charindex(' ',line, patindex('%[A-Z]%',line)) - patindex('%[A-Z]%',line
 )
 )
as varchar(10)) [symbol]
,line
from #Result
where
isdate(substring(line,1,10)) = 1   -- line must start with a date
and patindex('%[A-Z]%',line) > 0   -- line must have a symbol value
and line not like '%Nan%'          -- no NaN line value
) extract_date_and_symbol
) params_for_open
) params_for_high
) params_for_low
) params_for_close
) param_for_volume

order by symbol, date

 

Чтобы помочь вам проверить вывод в своих собственных тестах кода, ниже перечислены первые десять строк из набора результатов отсортированного внешнего запроса (выберите верхний 10 * из ## Results_with_extracted_casted_values порядок по символу, дате). Первые семь столбцов содержат извлеченные значения для исходных значений выхода из сценария Python, который появляется в восьмом столбце. Хотя данные для любого значения тикера отображаются уже с первой торговой даты 2014 года, акции тикера AAAP первоначально не были выставлены на продажу до 11 ноября 2015 года, поэтому исторические данные для тикета AAAP берут счет с этой даты.



Различия между значениями данных тикера nasdaq.com и Yahoo Finance


Значения тикера для исторических данных на конец дня с веб-сайта NASDAQ могут не соответствовать точно пакету значений, указанному на веб-сайте Yahoo Finance. Управление этими двумя веб-сайтами осуществляется независимо, поэтому цикл обновлений не обязательно является для них синхронным.  Кроме того, известно, что Yahoo Finance и Google Finance не получают своих исторических цен для акций, котирующихся на NASDAQ, непосредственно с этого сайта поскольку данные обрабатывают сторонние посредники.  Некоторые значения тикера бывают перечислены на nasdaq.com до начала торгов. Как следствие, при попытке сопоставить любую партию данных между веб-сайтами с историческими ценами на конец дня можно обнаружить некоторые различия. Для того, чтобы помочь вам получить представление о типах и значительности различий между сайтом NASDAQ и сайтом Yahoo Finance по историческим ценам, этот раздел сравнивает количество значений тикера и различия между значениями тикера от nasdaq.com и Yahoo Finance.

Следующий скрипт получает все отдельные значения тикера из таблицы ## Results_with_extracted_casted_values ​​и сохраняет их в таблице ## Results_with_extracted_casted_values_distinct_symbol.

-- distinct ticker values from Yahoo Finance historical data
select distinct symbol
into ##Results_with_extracted_casted_values_distinct_symbol
from ##Results_with_extracted_casted_values

В следующем запросе используется оператор набора пересечений T-SQL для сравнения значений тикера в таблице ## nasdaq_tickers (с сайта nasdaq.com) по сравнению с таблицей ## Results_with_extracted_casted_values_distinct_symbol (с веб-сайта Yahoo Finance). Исходная строка комментария указывает, что между этими двумя источниками есть только 3263 значения тикера. Однако запросы отдельных источников указывают на то, что в таблице ## nasdaq_tickers отображается 3297 значения тикера, и всего 3266 значений в таблице ## Results_with_extracted_casted_values_distinct_symbol. Тот факт, что количество значений тикера на пересечении между двумя источниками отличается от количества значений тикера в любом источнике, означает, что источник имеет как пересекающиеся с другим источником значения тикера, так и отличающиеся.

-- 3263 ticker values are in both ##nasdaq_tickers and ##Results_with_extracted_casted_values_distinct_symbol

-- 3297
select * from ##nasdaq_tickers

intersect

-- 3266
select symbol from ##Results_with_extracted_casted_values_distinct_symbol

Запрос, отображенный ниже, использует T-SQL (кроме оператора set)  для обнаружения значений тикера в таблице ## nasdaq_tickers, а не ## Results_with_extracted_casted_values_distinct_symbol. Этот запрос подтверждает, что есть 34 значения тикера, которые находятся в таблице ## nasdaq_tickers и отсутствуют в таблице ## Results_with_extracted_casted_values_distinct_symbol. Если вы запустите запрос, вы можете проверить набор результатов, чтобы точно узнать, какие значения тикера находятся в таблице ## nasdaq_tickers, но отсутствуют в таблице ## Results_with_extracted_casted_values_distinct_symbol.

-- 34 ticker values from ##nasdaq_tickers are not in Yahoo ticker values

-- 3297
select * from ##nasdaq_tickers

except

-- 3266
select symbol from ##Results_with_extracted_casted_values_distinct_symbol



Если вы задумаетесь над этими результатами, вы можете легко решить, что необходимо исследовать и другие сравнения. Но это уже выходит за рамки этого совета.

Короче говоря, являются тикеры из каждого источника данных на 99%  или на 100% одинаковыми - действительно ли это так важно?Вероятно, ответ зависит от целей, для которых вы извлекаете исторические значения. Если ваши требования диктуют необходимость, вы можете использовать запросы подобные тем, которые показаны в этом разделе, для изучения характера различий между источниками.

Следующие шаги

  1. Установите Python, если его еще нет на используемом вами компьютере SQL Server, и настройте SQL Server, чтобы он мог безопасно запускать скрипты Python. Ознакомьтесь с настройкой для использования Python с SQL Server для этого раздела, чтобы узнать, как выполнить эти шаги.
  2. Затем загрузите  файлы для этого совета  в каталог c: \ python_programs на компьютере под управлением SQL Server.
  3. Этот совет содержит три файла сценариев для каждой из двух задач. Однако вам нужно только запустить файл сценария T-SQL для каждой задачи. Это связано с тем, что файл сценария T-SQL является причиной вызова каждого из двух других файлов для каждой задачи.
  • Сначала запустите сценарий T-SQL, чтобы сгенерировать файл всех символов тикера NASDAQ.
  • Затем запустите сценарий T-SQL, чтобы генерировать исторические данные о ценах и объеме для всех символов тикера NASDAQ (которые также находятся в Yahoo Finance).

4. Подтвердите свои результаты, сравнив набор результатов теста с выдержками, отображаемыми в этом совете. Также посетите пользовательский интерфейс Yahoo Finance (https://finance.yahoo.com/) и проверьте свои результаты по этому источнику.

5. Наконец, начните использовать исторические данные о ценах и объемах. Вам предлагается оставить комментарии для этого совета, указав конкретные последующие шаги, которые вы пробовали, или вы хотите увидеть в будущих советах.

No Comments

Add a Comment