iamraa

7 лет назад

Работа с ценами в Python и PostgreSQL

В этой статье мы рассмотрим, как правильно работать с историей цен📈 в связке PostgreSQL🐘 и Python🐍. Разберём, как хранить цены и ускорить их получение в Python🐍.

Дополнительно приложен блокнот на IPython с исходным кодом и измерениями.

При переходе на Python я был вдохновлен удобством языка и огромным количеством готовых пакетов. Писать было легко и удобно, а работало все быстро. Но всё омрачало катастрофически медленное получение большого массива цен из базы данных (БД) в Python.

В статье показаны примеры для PostgreSQL, но материал будет полезен для любой БД, включая MySQL, при работе в связке с Python.Мы будем работать с пакетами psycopg2 и numpy.

Почему PostgreSQL и Python?

PostgreSQL строго придерживается стандарта SQL и обладает множеством удобных функций по работе с данными. Субъективно, работает значительно стабильнее в сравнении с MySQL. Множество задач можно переложить на плечи БД. Проигрыш в скорости с лихвой компенсируется преимуществами.Например, я использую:

транспонирование строк в столбцы;
генерацию недельных и месячных таймфреймов одним запросом.

Python изящен, прост и удобен для решения любых задач. Обладает большим сообществом и богатым набором пакетов на все случаи жизни. Позволяет легко подключать расширения на языке C/C++, что практически нивелирует его проблемы производительности.

Какая проблема?

Я занимаюсь анализом рынка акций и алготрейдингом. Одна из повседневных задач - это анализ большой истории цен множества активов. Например, для парного трейдинга необходимо выбрать историю цен за 600 торговых дней для 3000 активов. Что на выходе даёт нам примерно 1,8 млн строк. В каждой строке 8 полей (тикер, дата, открытие, максимум, минимум, закрытие, объем, регулирование). В итоге 14,4 млн элементов.

Для современных компьютеров - это ерунда, но не для Python. Он полностью объекто-ориентированный язык. Дата, строка или число - всё будет отдельным объектом.

На Python есть отличные пакеты, которые позволяют работать с массивами, хранящими данные единым блоком, такие как Numpy. Но между БД и numpy-массивом лежит стандартное поведение Python величиною в вечность.

Как хранить цены?

Необходимо учесть:

История цен большая и постоянно растёт.
Активы выплачивают дивиденды и проводят сплиты. Необходимо учитывать это в цене и, при необходимости, игнорировать.
Названия тикеров могут меняться или склеиваться.
На разных биржах в разное время могут встретиться одинаковые тикеры.
Цены могут быть получены из разных источников. (Данный случай я не рассматриваю.)

Исходя из перечисленного, нам необходимы две таблицы:

Таблица активов (Symbols).
Таблица истории цен (Prices).

Структура Symbols

Информация об активах. Мы можем хранить торгующиеся тикеры и популярные индексы. А можем создавать свои индексы. Дополнительно нужно хранить код биржи и факт присутствия актива в листинге. Это рабочий минимум.

Для русских бумаг может быть актуально хранение размера лота бумаги.

Пример структуры:

id - Integer, целое число
symbol - Varchar(8), строка
exchange - Varchar(8), строка
is_listed - Boolean, да/нет

Код создания таблицы доступен в блокноте.

Структура Prices

История цен. Важно хранить оригинальные цены как есть, без регулирования на выплаченные дивиденды и сплиты. Это может быть полезно при анализе прошлого. Но важно хранить и отрегулированную цену, чтобы иметь возможность пересчитать историю с учётом сплитов и дивидендов для построения визуально правильного графика.

Так как таблица истории цен будет бесконечно расти, нужно хранить в ней только необходимые и достаточные данные оптимального типа.Пример структуры для дневной истории:

symbol_id - Integer, целое число
date - DateTime (TimeStamp), дата
open - Numeric(20, 4), число с заданной точностью
high - Numeric(20, 4), число с заданной точностью
low - Numeric(20, 4), число с заданной точностью
close - Numeric(20, 4), число с заданной точностью
volume- Numeric(20, 0), число с нулевой точностью
adj - Numeric(20, 4), число с заданной точностью

Код создания таблицы доступен в блокноте.

Представление V_Prices

Представление даёт возможность перенести часть функций подготовки данных на сторону БД. Например, пересчёт цен при регулировании или сортировку.

Для нашего примера требуется только сортировка. Почему, вам станет понятно при тестах.

До решения проблемы производительности я в представлении хранил регулирование цен. Но сейчас с этим лучше справляется Numpy.

Первые проблемы Psycopg2 и решение

Psycopg2 - это популярный python-пакет для работы с базой данных PostgreSQL. И всё в нём хорошо, кроме самостоятельности. У полученных данных будет проверен тип и в соответствии с типом всё будет завернуто в правильные объекты:

Дата станет Date().
Numeric станет Decimal().

Это хорошо, так как, получив данные, мы можем сразу с ними работать. Это плохо, так как очень затратно.

Самый простой для нас, но неочевидный, способ - это отключить перевод Numeric() в Decimal(). Так как большинство данных - это вещественные числа, нам это поможет ускориться в два раза.

По умолчанию psycopg2 пожирает память при большом количестве вещественных чисел. Данное решение частично это победит.

Данный код можно запустить при подключении к БД:

# регистрируем новый тип данных, для обработки

dec2float = psycopg2.extensions.new_type(

    psycopg2.extensions.DECIMAL.values,

    'dec2float',

    lambda value, curs: float(value) if value is not None else None)

psycopg2.extensions.register_type(dec2float)

Измерения производительности в блокноте.

Как записать данные напрямую в Numpy?

Вот вопрос, который меня мучил дольше всего. Я уж было хотел писать свой драйвер на C/C++, но на помощь пришёл PostgreSQL. Нам достаточно группировать данные, чтобы получить все одной строкой. Тогда в Python мы получим всего восемь объектов. После миллионов это звучит ободряюще.

Есть проблема, с которой мы встретимся при запросе к таблице напрямую. Группированные данные сложно упорядочить. Здесь нам на помощь приходит наше представление с заложенной сортировкой по умолчанию.

Полученный набор строк можно разобрать с помощью Numpy следующими командами:

# даты

np.array(r[1].split(','), dtype='datetime64')

# цены и объем

np.fromstring(r[2], sep=',')

Получить данные для определенного актива можно так:

symbol_filter = data['symbol'] = 'AAPL'

close_prices = data['close'][symbol_filter]

От первоначальных результатов мы ускорили процесс получения данных почти в 3 раза, что видно по тестам в блокноте. И сам SQL-запрос:

SELECT

    string_agg(symbol_id::text, ',') AS symbol_list

    , string_agg(dt::text, ',') AS dt_list

    , string_agg(open::text, ',') AS open_list

    , string_agg(high::text, ',') AS high_list

    , string_agg(low::text, ',') AS low_list

    , string_agg("close"::text, ',') AS close_list

    , string_agg(volume::text, ',') AS volume_list

    , string_agg(adj::text, ',') AS adj_list

FROM v_test_prices

...

Делаем быстрое получение pandas.DataFrame

Решая проблему быстрого добавления данных в PostgreSQL пришёл к решению быстрого получения объекта DataFrame. Для этого мы объединяем все поля и группируем их в одно поле с разделителем \n. И получаем CSV-файл. Теперь всё можно разобрать одной командой:

df = pd.read_csv(io.StringIO("symbol_id,dt,open,high,low,close,volume,adj\n" + r[0]))

SQL-запрос принимает вид:

SELECT

 string_agg(

    symbol_id::text

    || ',' || dt::text

    || ',' || open::text

    || ',' || high::text

    || ',' || low::text

    || ',' || "close"::text

    || ',' || volume::text

    || ',' || adj::text

 , '\n') AS res

FROM v_test_prices

...

Что с Date()?

Объект Date() очень удобен для работы в Python. Мы же получили объект numpy.datetime64. Не страшно. Вот решение:

fast['dt'][0].astype(object)  # вернёт Date()

🎁Код в студию

Исходный код доступен на Quantrum.me

🏁Вывод

Как видно, всегда можно найти оптимальное решение наименьшими усилиями. Благодаря описанному подходу можно анализировать большие массивы цен для множества активов. Данные можно получать за один запрос. И всё это при минимальных затратах памяти.

💬В комментариях напишите, как ещё можно ускорить работу? Как можно улучшить структуру БД? Какими базами данных пользуетесь вы? Как храните регулирование цен?

Александр Румянцев aka "i.am.raa"
Автор Quantrum.me
Интересуетесь алготрейдингом на Python? Присоединяйтесь к команде. Пишите в личку или на email.

☝Готовитесь к торговле на рынке? Пройдите обучение🎓 у профессиональных трейдеров👍.