Rivalry2

HTML Diff

0 added 0 removed

Original 2026-01-01

Modified 2026-02-26

1 В этом уроке мы рассмотрим свечной график или Candlestick chart. Он широко используется для изображения котировок валют. Сегодня мы агрегируем данные валютной пары за 19 лет и построим график в Google Sheets.

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

3 <h2>Candlestick chart</h2>

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

5 На этом рисунке мы видим линейный график котировок валют австралийского доллара относительно американского доллара.

6 Но такой график для отображения котировок неудобен и неинформативен. Мы видим только изменение текущей цены со временем. Торги на рынке происходят в каком-то окне: есть цена открытия и цена закрытия. Цена открытия - это цена, с которой начинаются торги, а по цене закрытия они заканчиваются. Поэтому таблицы с котировками обычно выглядят более сложно:

7 currency_exchange

8 В этой таблице представлены котировки валюты с шагом в один месяц. Она содержит такие столбцы:

9 <ol><li>date - дата</li>

10 <li>low - минимальное значение цены за этот месяц</li>

11 <li>open - цена открытия. Это первая цена за месяц</li>

12 <li>high - максимальная цена за месяц</li>

13 <li>close - цена закрытия. Это последняя цена за месяц</li>

14 </ol>Из этой таблицы мы понимаем, как торги начинались и заканчивались. Также знаем минимальное и максимальное значение котировок. Для визуального представления таких таблиц используютсвечной графикилиCandlestick chart. Он удобнее линейного графика, потому что позволяет визуализировать все параметры: цену открытия, закрытия, минимальную и максимальную цены.

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

16 На свечном графике каждый промежуток времени мы изображаем в виде "свечи". У свечи есть тело и фитиль. Нижний и верхний фитили на картинке обозначены как Lower Wick и Upper Wick. Тело свечи отмечено как Real Body. Open и Close - это цены открытия и закрытия.

17 Тело - это прямоугольник, у которого одна из границ - цена открытия, а вторая - закрытия. Цена открытия бывает меньше или больше цены закрытия - это зависит от трендов на рынке.

18 Фитиль рисуется узкой вертикальной линией. Верхняя граница фитиля - это максимальная цена за период торгов, а нижняя - минимальная. Если фитиль длинный, а тело нет - это говорит о том, что рынок очень волатильный, то есть быстро меняется.

19 По таблице CurrencyExchange мы можем построить свечной график котировок. Он будет выглядеть так:

20 Здесь мы видим свечной график котировок валют. Он содержит в себе не информацию о текущей стоимости валюты, а цену открытия, закрытия, минимальную и максимальную во временном окне, когда идут торги.

21 Чтобы получить из таблицы, в которой есть только текущая стоимость, таблицу с ценами открытия, закрытия, минимальной и максимальной, мы агрегируем данные. По агрегированной таблице мы уже построим свечной график.

22 <h2>Агрегация курсов валют</h2>

23 Мы будем агрегировать таблицу курса валют помесячно. Для агрегации мы проделаем такие шаги:

24 <ol><li>Создадим вспомогательную таблицу с первыми и последними значениями цены за месяц</li>

25 <li>Создадим вспомогательную таблицу для цен открытия и закрытия</li>

26 <li>Создадим вспомогательные таблицы для минимальных и максимальных значений за месяц</li>

27 <li>Объединим эти таблицы с помощью SQL-функции join и проведем итоговую агрегацию</li>

28 </ol><h3>Первые и последние значения цены за месяц</h3>

29 Откроем базу<a>australian_currency</a>.

30 В базе данных есть таблица exchange с двумя полями:

31 <ul><li>date - дата, в которую валюта имела определенную стоимость</li>

32 <li>currency - значение котировки валюты</li>

33 </ul>Посмотрим на саму таблицу:

34 Currency

35 В этой таблице 5019 строк. Мы видим, что в таблице есть данные за 2000-2019 года.

36 Нам нужно создать новую таблицу с минимумом, максимумом, первой ценой и последней ценой за определенный период времени. Мы будем агрегировать данные помесячно.

37 Чтобы получить первую и последнюю цены за месяц, мы создадим вспомогательную таблицу monthly_dates. В нее мы агрегируем первую и последнюю дату за месяц, само значение месяца и уникальный ключ, по которому мы будем джойнить таблицы:

38 Мы создали таблицу с полями:

39 <ul><li>year_month - уникальный строковый ключ с годом и месяцем</li>

40 <li>month - значение месяца</li>

41 <li>min_date - первая дата за месяц</li>

42 <li>max_date - последняя дата за месяц</li>

43 </ul>Агрегируем данные и запишем их в новую таблицу:

44 Мы записали в таблицу ключ year_month, поле month, минимальное значение даты за месяц min_date и максимальное значение даты max_date.

45 Функция concat применяется, чтобы объединить несколько строковых значений. date_trunc позволяет "схлопнуть" даты временного промежутка в один. А с помощью значения month в функции date_trunc мы использовали агрегацию в месяц.

46 Сортировка по дате нужна, чтобы избежать перемешивания дат. Уникальный ключ, в котором есть год и месяц, нам понадобится в дальнейшем для джойна таблиц.

47 <h3>Цены открытия и закрытия</h3>

48 Теперь мы создадим таблицу open, чтобы сохранить в нее первые значения котировок в месяце. В ней тоже будет уникальный ключ year_month, а также первая дата за месяц и значение open цены валюты в эту дату:

49 Теперь сджойним таблицу monthly_dates с таблицей open по минимальной дате. Мы не будем использовать агрегацию, а только возьмем значения котировок в минимальные даты каждого месяца:

50 По аналогии с таблицей open создадим таблицу close, в которой будет последнее значение валюты за месяц. Мы будем джойнить ее с таблицей monthly_dates по максимальной дате месяца:

51 <h3>Минимальные и максимальные цены за месяц</h3>

52 Теперь мы создадим таблицу min_max_currency. В ней будет значение месяца, потому что мы агрегируем минимальное и максимальное значения валюты за месяц, и сами минимальное и максимальное значения котировок:

53 Агрегируем таблицу exchange по минимальным и максимальным помесячным значениям валют и запишем в min_max_currency. Для помесячной агрегации мы используем функцию date_trunc:

54 <h3>Витрина данных и свечной график</h3>

55 Теперь осталось создать таблицу с витриной, в которой будут уникальный ключ, цена открытия и закрытия и минимальная и максимальная цены:

56 Для создания витрины мы сджойним таблицы monthly_dates, open, close и min_max_currency:

57 Мы создали витрину данных, по которой будем строить свечной график. Мы сделали джойн таблиц monthly_dates, open и close по ключу year_month и полученной таблицы с min_max_currency по значению месяца month.

58 Итоговая таблица содержит 240 строк и выглядит так:

59 data_mart

60 В ней есть поля:

61 <ul><li>year_month - уникальный строковый ключ</li>

62 <li>month - месяц, по которому производилась агрегация</li>

63 <li>min_currency - минимальное значение валюты за месяц</li>

64 <li>open - первое значение валюты за месяц</li>

65 <li>max_currency - максимальное значение валюты за месяц</li>

66 <li>close - последнее значение валюты за месяц</li>

67 </ul>Поля идут в таком порядке, потому что так по ним удобнее строить Candlestick Chart в Google Sheets.

68 Построим график по следующим шагам:

69 <ol><li>Скопируем витрину в Google Sheets</li>

70 <li>Выделим все столбцы, кроме year_month</li>

71 <li>Для столбца month выберем "Формат" -> "Числа" -> "Обычный текст"</li>

72 <li>В столбцах с котировками заменим все точки на запятые</li>

73 <li>Вставим диаграмму и типом диаграммы выберем "График "японские свечи""</li>

74 </ol>В итоге мы получим график, который видели выше:

75 Мы построили свечной график по помесячным котировкам. Видим, что он состоит из свечей. Но на нем слишком много данных и мы не можем посмотреть на фитили. Поэтому возьмем только данные за 2019 год и запишем их в таблицу data_mart_new_data:

76 Здесь мы отобрали все даты, которые больше или равны 1 января 2019 года.

77 Теперь построим свечной график по этим данным. В дополнительных настройках вертикальной оси установим минимальное значение 1.2, чтобы рассмотреть график на большем масштабе:

78 Мы построили свечной график котировок валют за 2018-2019 год. На этом графике отчетливо видно, что свечной график состоит из тела свечи и фитилей. Границы тела определяются ценой открытия и закрытия, а фитили - минимальным и максимальным значениями за месяц.

79 <h2>Выводы</h2>

80 В этом уроке мы научились агрегировать данные для свечного графика. Мы взяли котировки валютной пары австралийского доллара и доллара США, сделали помесячную агрегацию валют и построили свечной график в Google Sheets. Агрегация валют позволит работать в финансовой сфере и строить отчеты по валютным парам.