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

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)

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

Вот вопрос, который меня мучил дольше всего. Я уж было хотел писать свой драйвер на 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
...

Решая проблему быстрого добавления данных в PostgreSQL пришёл к решению быстрого получения объекта DataFrame. Для этого мы объединяем все поля и группируем их в одно поле с разделителем [code]\n[/code]. И получаем 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() очень удобен для работы в Python. Мы же получили объект numpy.datetime64. Не страшно. Вот решение:

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

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

Вывод

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

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

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