Как конвертировать валюты в Google Sheets, функция GOOGLEFINANCE.
Во время разработки таблицы планирования семейного бюджета понадобилось ввести мультивалютную систему отслеживания денежных средств в транзакциях.
В google таблицах есть специальная функция для такой задачи: GOOGLEFINANCE.
Обращаясь к официальной справке, выясняется, что функция может отслеживать не только курс валюты, но и другие данные по ценным бумагам и металлам.
Синтаксис и основные положения функции GOOGLEFINANCE.
Функция GOOGLEFINANCE помогает получить текущие или архивные данные о ценных бумагах из сервиса Google Финансы.
=GOOGLEFINANCE(ticker; [attribute]; [start_date]; [and_date|num_days]; [interval]) - латинская версия =GOOGLEFINANCE(код; [атрибут]; [дата_начала]; [дата_окончания|количество_дней]; [интервал]) - русская версия
- Ticker ( код ) — обязательный аргумент. Идентификационный код для ценных бумаг, металлов и валют. Это может быть ссылка на идентификатор в любой ячейке гугл таблицы, либо статически прописанные в формулу данные. В последнем случае, они помещаются в кавычки.
- [attribute] ([атрибут]) — необязательный аргумент, по умолчанию — «price» ( цена ). Параметр, значение которого требуется получить от Google Финансов. Подробнее о свойствах и параметрах аргумента.
- [start_date] ([дата_начала]) — необязательный аргумент. Ссылка на ячейку с датой, либо статические данные даты в кавычках формата: дд.мм.гггг (либо его аналоги). Начальная дата при выборке ретроспективных данных.
- [and_date|num_days] ([дата_окончания|количество_дней]) — необязательный аргумент. Конечная дата при выборке ретроспективных данных, либо количество дней от [start_date] ([дата_начала]), для которых нужно загрузить данные.
- [interval] ([интервал]) — необязательный аргумент. Периодичность загрузки данных; может принимать значения «DAILY» (ежедневно) или «WEEKLY» (еженедельно). Либо можно задать параметры данному аргументу числами: 1 и 7 соответственно. Любые другие числа будут выдавать ошибку #NAME?.
Извлекаем текущий курс валют при помощи GOOGLEFINANCE
Для начала нам нужно определить коды искомых валют, информацию по которым мы будем вытягивать из сервиса: RUB, USD, EUR. Данные по другим кодам валют.
Теперь определяем соотношения между собой (что во что будем конвертировать): RUBUSD (рубль к доллару), RUBEUR (рубль к евро), USDRUB (доллар к рублю), EURRUB (евро к рублю).
Не забываем указать собственное соотношение с результатом — единица (1). Понадобится для конверта текущих сумм в выбранной валюте: RUBRUB, USDUSD, EUREUR.
Для определения текущего курса рубля к выбранным валютам в формуле функции указываем один единственный аргумент — Ticker ( код ).
=IF(AV2="";"";IFERROR(GOOGLEFINANCE(AV2);1))
AV2 — Ячейка с кодом соотношения валют.
Как показать курс валюты на определенную дату в Google таблицах?
Начнем с самого простого примера: показать курс 1 доллара США к рублю на определенную дату (пусть это будет 11.08.2023).
=GOOGLEFINANCE(D7;"price";C9)
- D7 — ячейка с кодом USDRUB
- «price» — атрибут, в данном случае, нужна цена, за которую торговали долларом к рублю
- C9 — ячейка с датой
При таком формате формулы из Google Finance будет подтягиваться курс с заголовками, точной датой и временем в одной ячейке, и суммой за доллар к рублю при закрытии торгов на эту дату.
Чтобы показать только сумму закрытия торгов в одной ячейке (без заголовков и другой информации — только стоимость доллара!).
=INDEX(GOOGLEFINANCE(D7;"price";C9);2;2)
- INDEX(….. ; 2 ; 2) — отображает нужную информацию из общего массива, смещаясь на 2й столбец и 2ю строку этого столбца, т.е., показывает только ту ячейку, в которой сумма 1 доллара в рублях.
- D7 — ячейка с кодом USDRUB
- «price» — атрибут, в данном случае, нужна цена, за которую торговали долларом к рублю
- C9 — ячейка с датой
При таком формате формулы из Google Finance будет подтягиваться только сама сумма 1 доллара США в рублях.
Так как операции в транзакциях были в определенные даты — текущий курс в данном случае нам не поможет (данные в отчетах, в таком случае, будут всегда плавать, привязываясь к текущему курсу, а не к курсу на дату совершения транзакции). Надо извлечь исторический курс выбранных валют на определенные даты:
=IF('Транзакции'!A2="";"";IFERROR(INDEX(GOOGLEFINANCE($B$1;"price";WORKDAY('Транзакции'!A2+1;-1));2;2);1))
- $B$1 — ячейка с кодом ( RUBUSD — Рубль к Доллару ).
- «price» — [attribute] ([атрибут]). Цена, по которой торговался рубль в выбранную дату в ячейке ‘Транзакции’!A2.
- ‘Транзакции’!A2 — ячейка с датой (на примере 01.04.2016 ).
- WORKDAY(‘Транзакции’!A2+1;-1) — конструкция [start_date] ([дата_начала]) для вычисления курса только в рабочие дни, так как если дата выпадет на выходной — то будет выдана ошибка.
- Так как в формуле не указана конечная дата, аргумент — [and_date|num_days] ([дата_окончания|количество_дней]), то функция GOOGLEFINANCE выдает список котировок на начало и конец сессии в виде табличного списка. В таком случае мы извлекаем ячейку с результатом сессии через функцию INDEX:
INDEX(GOOGLEFINANCE($B$1;«price»;WORKDAY(‘Транзакции’!A2+1;-1));2;2) - Также, в конструкцию входят IFERROR — проверка на ошибку; IF — условие: если ячейка с датой (‘Транзакции’!A2) не заполнена — то ничего выводить не нужно.
На примере извлечена информация по соотношению Российского рубля к Доллару США 01.04.2016 года — 0,01478 доллара за 1 рубль или 67,5 рубля за 1 доллар.
Данные по курсам берутся из Лондонской биржи: Currency and cryptocurrency prices provided by Morningstar
