Когда был максимальный курс доллара к евро?

Вот небольшая программа, вычисляющая это:

curl -s https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip \
| gunzip \
| sqlite3 -csv ':memory:' '.import /dev/stdin stdin' \
  "select Date from stdin order by USD asc limit 1;"

Результат: 2000-10-26. (Можете попробовать запустить её самостоятельно.)

Как это работает:

Строка с curl скачивает официальные исторические данные о курсе евро к другим валютам, публикуемые Европейским центральным банком. (Флаг -s просто удаляет шум из потока стандартной ошибки.)

Данные передаются в файле zip, который распаковывает gunzip.

sqlite3 запрашивает файл csv из архива. :memory говорит sqlite использовать файл, находящийся в памяти. После этого .import /dev/stdin stdin говорит загрузить стандартный ввод в таблицу под названием stdin. Следующая строка — это SQL-запрос.

Очистка в столбце 42

Хотя извлечь простой максимум легко, формат данных неидеален. Данные имеют «широкий» формат — столбец Date, а затем дополнительный столбец для каждой валюты. Вот заголовок csv для этого файла:

Date,USD,JPY,BGN,CYP,CZK,DKK,EEK,GBP,HUF,LTL,LVL,MTL,[и так далее]

При выполнении фильтрации и объединения работать проще, если данные хранятся в «длинном» формате, примерно так:

Date,Currency,Rate

Переход от широкого к длинному формату — это простая операция, обычно называемая melt. К сожалению, в SQL её нет.

Впрочем, это неважно, мы можем выполнить melt при помощи pandas:

curl -s https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip | \
gunzip | \
python3 -c 'import sys, pandas as pd
pd.read_csv(sys.stdin).melt("Date").to_csv(sys.stdout, index=False)'

Но есть ещё одна проблема. Обработчики файлов в ЕЦБ ошибочно ставят завершающую запятую в конце каждой строки. Из-за этого парсеры csv распознают дополнительный пустой столбец в конце. Наш sqlite-запрос этого не заметил, но эти запятые мешают выполнять melt, создавая целую кучу мусорных строк в конце:

Влияние этой лишней запятой можно устранить при помощи pandas, добавив в нашу цепочку ещё один элемент: .iloc[:, :-1], который, по сути, говорит «дай мне все строки (":") и всё остальное, кроме последнего столбца (":-1")». То есть:

curl -s https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip | \
gunzip | \
python3 -c 'import sys, pandas as pd
pd.read_csv(sys.stdin).iloc[:, :-1].melt("Date")\
.to_csv(sys.stdout, index=False)'

Неужели каждый, кто пользуется этим файлом, должен повторять всю эту возню с данными?

К сожалению, да. Как говорится, «никто не мечтал о работе уборщиком данных, но все ею занимаются».

Но если честно, данные о курсах валют ЕЦБ, вероятно, находятся в 10% лучших публикаций открытых данных. Обычно для получения подходящих табличных данных требуется гораздо более мучительный и сложный процесс.

Например, нам не нужно выполнять следующие задачи: договариваться о доступе (например, платя деньги или общаясь с поставщиком); сохранять адрес электронной почты/название компании/должность в чью-то базу лидов, проверять, не израсходовали ли мы лимит доступа; выполнять аутентификацию (часто это само по себе становится объёмным сайд-квестом), читать документацию по API или решать проблемы более серьёзные, чем простое форматирование.

То есть на фоне остальныхeurofxref-hist.zip довольно удобен.

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

Вот как это сделать:

curl -s https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip | \
gunzip | \
python3 -c 'import sys, pandas as pd
pd.read_csv(sys.stdin).iloc[:, :-1].melt("Date")\
.to_csv(sys.stdout, index=False)' | \
# this is the new bit: \
curl -n --upload-file - \
'https://csvbase.com/calpaterson/eurofxref-hist?public=yes'

Всё, что я сделал — это добавил ещё один curl, чтобы при помощи HTTP PUT поместить файл csv в csvbase. --upload-file - выполняет загрузку из стандартного ввода на заданный url (при помощи HTTP PUT). Если таблицы ещё нет в csvbase, то она создаётся. -n добавляет мои учётные данные из моего~/.netrc. Вот и всё. Ничего сложного.

Рисуем красивые графики

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

Давайте создадим график данных:

curl -s https://csvbase.com/calpaterson/eurofxref-hist | \
grep USD | \
cut --delim=, -f 2,4 | \
gnuplot -e "set datafile separator ','; set term dumb; \
plot '-' using 1:2 with lines title 'usd'"

Для более шести тысяч примеров данных в терминале на 80x25 символов получилось достаточно читаемо. Можно составить общий тренд. При этом вполне умеренное соотношение данных и чернил.

(Если вам любопытно, почему https://csvbase.com/calpaterson/eurofxref-hist в браузере возвращает веб-страницу, а curl возвращает файл csv, то прочитайте мой пост.)

gnuplot сам по себе похож на миниатюрный язык программирования. Вот что делает представленный выше фрагмент:

• set datafile separator ',' — сообщает, что это csv

• set term dumb — рисует ascii-графику!

• plot - — создаёт график данных, поступающих из стандартного входа

• using 1:2 with lines — рисует линии из столбцов 1 и 2 (дату и курс)

• title 'usd' — даёт название линии

Разумеется, можно рисовать графики и в настоящие изображения:

curl -s https://csvbase.com/calpaterson/eurofxref-hist | \
grep USD | \
cut --delim=, -f 2,4 | \
gnuplot -e "set datafile separator ','; set term svg; \
set output 'usd.svg'; set xdata time; set timefmt '%Y-%m-%d'; \
set format x '%Y-%m-%d'; set xtics rotate; \
plot '-' using 1:2 with lines title 'usd'"

Вывод в SVG лишь слегка сложнее, чем в ascii-графику. Чтобы изображение выглядело красиво, нужно помочь gnuplot понять, что это данные временных последовательностей, то есть что ось X — это время, указать формат этого времени, а затем приказать повернуть отметки на оси X, чтобы они были читаемыми. Но код довольно длинный: давайте привяжем его к функции bash, чтобы можно было использовать его многократно:

plot_timeseries_to_svg () {
    # $1 is the first param
    gnuplot -e "set datafile separator ','; set term svg; \
    set output '$1.svg'; set xdata time; set timefmt '%Y-%m-%d'; \
    set format x '%Y-%m-%d'; set xtics rotate; \
    plot '-' using 1:2 with lines title '$1'"
}

Скользящее среднее и новые инструменты

Пока всё получается здорово, но было бы неплохо добавить более сложного анализа: давайте попробуем вычислить скользящее среднее, чтобы можно было увидеть линию тренда:

curl -s https://csvbase.com/calpaterson/eurofxref-hist | \
duckdb -csv -c "select Date, avg(value) over \
(order by date rows between 100 preceding and current row) \
as rolling from read_csv_auto('/dev/stdin')
where variable = 'USD';" | \
plot_timeseries_to_svg rolling

Круто. Если у вас не установлена duckdb, то не так уж сложно адаптировать это под sqlite3 (запрос будет таким же). Я хотел показать DuckDB, потому что она во многом похожа на sqlite, но только столбчатая (а не строковая). Однако для меня её основная ценность заключается в удобной эргономике.

Вот один из примеров этого: можно загружать csv в файлы таблиц напрямую из HTTP:

-- работает с  csvbase!
CREATE TABLE eurofxref_hist AS SELECT * FROM
read_csv_auto("https://csvbase.com/calpaterson/eurofxref-hist");

Это довольно просто, а DuckDB вполне хорошо справляется с определением типов. В ней есть множество других полезных возможностей: например, она определяет размер терминала и по умолчанию ограничивает таблицы, а не заваливает терминал огромным набором результатов. У неё есть полоса прогресса для больших запросов! Она может выводить таблицы в markdown! И ещё многое другое.

Открытые данные — это ещё и открытый API

С файлом zip данных и программами, которые или установлены, или устанавливаются простой командой brew install/apt install, можно сделать очень многое. Помню, как был впечатлён, когда я работал в банке и мне впервые показал этот eurofxref-hist.zip опытный коллега. Он был так прост: самый простой протокол обмена данными между организациями, который я видел.

Кажется, что простой файл zip с csv внутри — это очень мало, но на самом деле огромное количество финансовых приложений используют этот конкретный файл zip каждый день. Я практически уверен, что запятые оставили в нём именно поэтому — если бы их удалили, то большой объём кода поломался бы.

Если доступ к открытым данным очень прост, то он выполняет и дополнительную функцию открытого API. В конце концов, в чём здесь отличие от большой доли API, которые гораздо чаще обмениваются данными, а не вызывают удалённые функции?

Поэтому я считаю, что файл zip ЕЦБ — отличный фундамент для формата обмена данными. Мне нравится его простота, и я постарался сохранить её в csvbase.

В csvbase каждая таблица имеет единственный url, соответствующий такому виду:

https://csvbase.com/<username>/<table_name>

Например:

https://csvbase.com/calpaterson/eurofxref-hist

И для каждого url есть четыре основных действия:

При выполненииGET: вы получаете csv (или веб-страницу, если находитесь в браузере).

При выполнении PUT  для нового csv: создаётся новая таблица или перезаписывается существующая.

При выполнении POST для нового csv: добавляются новые строки в существующую таблицу.

При выполненииDELETE: таблица удаляется.

Для аутентификации можно просто использовать HTTP Basic Auth.

Можно ли придумать что-то более простое?

Примечание

Выше я сказал, что в большинстве баз данных SQL нет операции melt. Из тех баз данных, которых я знаю, она есть в Snowflake и MS SQL Server. Знатоки SQL часто меня спрашивают: зачем использовать R или Pandas, если уже существует SQL? Самая главная причина заключается в том, что R и Pandas очень хорошо справляются с очисткой данных.

Ещё одна недооцениваемая особенность конвейеров bash заключается в том, что они многопроцессны. Каждая программа работает отдельно в собственном процессе. Пока curl скачивает данные из веба, grep фильтрует их, sqlite выполняет к ним запросы, и, возможно, curl снова загружает их на сервер, и так далее. Всё это делается параллельно, что, как ни странно, вполне позволяет конкурировать со сложными облачными альтернативами.

Почему евро был таким слабым в 2000 году? Эта валюта без монет и купюр была запущена в январе 1999 года. Изначально евро был своего рода внутриигровой валютой Евросоюза. Он существовал только внутри банков, поэтому для него не было ни купюр, ни монет. Всё это появилось позже. Как и вера в эту валюту — поначалу не было похоже, что маленький евро выживает, поэтому курс по отношению к доллару составлял 0,8252. Это значило, что в октябре 2000 года за доллар можно было купить 1,21 евро. Сегодня евро гораздо сильнее, за доллар можно купить меньше, чем 1 евро.