14 декабря 2015 г.

Импорт курса валют в Google Таблицы. Пошаговое руководство на примере ПриватБанк (Украина)

Курс валют в Google Таблицы

Торгуя импортными товарами в сети Интернет удобно вести бухгалтерию используя онлайновый инструмент от компании Google, аналог MS Excel — Google Таблицы. По скольку импортный товар приобретается за валюту, то часто возникает необходимость учитывать текущий курс валюты для использования его в расчетах ценообразования.

Предлагаю рассмотреть способ вставить текущий курс валюты в Google Таблицы на примере ПриватБанка. Данный способ можно адаптировать для любого другого банка или сайта. Кому лень читать может смело идти в конец статьи за готовой формулой.

И так, начнем с того, что найдем необходимую нам страницу на которой отображаются курсы валют. В случае с ПриватБанком это главная страница (https://privatbank.ua/) внизу которой отображается таблица с текущими курсами валют:

 

Искомый курс находится в таблице «курс карт и для пополнения вкладов», которая вызывается из выпадающего меню:


Нас интересует курс продажи по паре USD/UAH: 25.1256

Для того, что бы вставить в ячейку Google Таблицы этот курс мы воспользуемся встроенной функцией ImportHTML которая импортирует данные из таблицы или списка на веб-странице.
Функции необходимо передать адрес страницы, указать тип данных (таблица в нашем случае) и указать порядковый номер таблицы на странице. Формула в ячейке будет выглядеть так:
=ImportHTML("https://privatbank.ua/";"table";3)
Вывод данной функции будет следующим:


Порядковый номер таблицы подбирается опытным путем. В нашем случае это «3». В таком виде использовать данные не очень удобно, так как нам нужно только одно значение из таблицы, по этому мы применим еще одну функцию — INDEX, которая извлекает данные из ячейки на пересечении определенной строки и столбца. Нам необходимо получить значение из ячейки на пересечении 8-й строки и 3-го столбца:


В формуле это будет выглядеть следующим образом:
=(INDEX(ImportHTML("https://privatbank.ua/";"table";3);8;3))
И в результате мы получим только значение этой ячейки:


Но сразу использовать значение не получится так как оно представляет собой текст, а не число:


Происходит это потому, что в ячейке используется точка в качестве разделителя, тогда как Google Таблицы используют в качестве разделителя запятую. По этому для того, что бы привести значение к нужному виду мы найдем точку и заменим её на запятую с помощью функции SUBSTITUTE:
=SUBSTITUTE(INDEX(ImportHTML("https://privatbank.ua/";"table";3);8;3);".";",";)
Результат замены:


В принципе, уже в таком виде значением можно пользоваться в своих формулах. Но если мы захотим применить к ячейке какое-либо числовое форматирование то оно не сработает, так как необходимо чтобы ячейка содержала данные явно числового типа. Привести содержимое к числовому типу можно функцией VALUE:
=VALUE(SUBSTITUTE(INDEX(ImportHTML("https://privatbank.ua/";"table";3);8;3);".";",";))
Теперь мы можем использовать для форматирования значения в ячейке числовые форматы. Например, формат числа с двумя знаками после запятой «0.00»: