JSON курс валют НБУ і ПриватБанк у Google Sheets - в онлайн на дату без API і XML + наприкінці є ще плюшка
JSON курс валют НБУ і ПриватБанк у Google Sheets - в онлайн на дату без API і XML + наприкінці є ще плюшка
Embedded Files
На своєму Диску створюємо новий файл і починаємо, - приклад за посиланням.
Також якщо зручно є відео на YouTube.
Підхід по розбору JSON можна використовувати для будь -якого джерела даних.
JSON є гарною альтернативою XML і вимагає значно менше форматування контенту і специфічних знань по запитах XPath .
Заходимо на сайт API ПриватБанк: https://api.privatbank.ua/#p24/main
Наявні АРІ ПриватБанку -> Публічні
Нас цікавить:
Архів курсів валют ПриватБанку, НБУ
API дозволяє отримати інформацію про готівкові курси валют ПриватБанку та НБУ на обрану дату. Архів зберігає дані за останні 4 роки
Наявні АРІ ПриватБанку -> Публічні-> Документація
Копіюємо посилання: api.privatbank.ua/p24api/exchange_rates?json&date=01.12.2014
Параметри відповіді:
baseCurrency - Базова валюта
currency - Валюта угоди
saleRateNB/purchaseRateNB - Курс продажу НБУ / Курс купівлі НБУ
saleRate - Курс продажу ПриватБанку
purchaseRate - Курс купівлі ПриватБанку
Повертаємось в таблицю і вводимо функцію:
=IMPORTDATA(url; роздільник; мовний код)- Імпортує дані у форматі CSV
url - скопійований url у лапках "api.privatbank.ua/p24api/exchange_rates?json&date=01.12.2014"
роздільник-"{" обов'язково у лапках, кожен набір даних у JSON знаходиться між { }
Отримуємо формулу:
=IMPORTDATA("api.privatbank.ua/p24api/exchange_rates?json&date=01.12.2014";"{")
Маємо дані, але не придатні до читання і не компактні:
Щоб розмістити дані у стовпчик обгортаємо нашу формулу у функцію =TRANSPOSE(масив_або_діапазон) - змінює місцями рядки та стовпці масиву або діапазону клітинок:
=TRANSPOSE(IMPORTDATA("api.privatbank.ua/p24api/exchange_rates?json&date=01.12.2014";"{") )
Отримуємо рядки JSON, але дата прикладу з сайта не підходить, нам треба, щоб автоматично змінювалась дата у посиланні на СЬОГОДНІ, приступаємо.
Редагуємо посилання URL - видаляємо невірну дату, через & додаємо:
=TEXT(число;формат)- Змінює число на текст у вказаному форматі
число - СЬОГОДНІ нам дасть функція =TODAY() - Повертає поточну дату в числовому форматі.
формат - задаємо "dd.mm.yyyy"
Тому, що в посиланні формат: dd- день, mm- місяць, yyyy- рік
Отримуємо формулу і курс валют на сьогодні:
=TRANSPOSE(IMPORTDATA("https://api.privatbank.ua/p24api/exchange_rates?json&date="&TEXT(TODAY();"dd.mm.yyyy");"{"))
Переходимо до очищення даних:
=SPLIT(текст; роздільник; [розбивати_на_кожному]; [вилучити_пустий_текст])
Розбиває текст на певному символі чи рядку та
вставляє кожен фрагмент в окрему клітинку в рядку.
текст; - вказуємо клітинку текст з якої треба розділити
роздільник; - в лапках вводимо перший ":"
через & додаємо усі знаки -
роздільники: ":"&""")"&","&"}"&"]"
Самі лапки вносимо таким чином: """)" - екрануємо, щоб Таблиця зрозуміла, що по лапках треба розбити текст і що це не частина формули.
[розбивати_на_кожному]; [вилучити_пустий_текст]
нам для цього прикладу не важливі
=SPLIT(A3;":"&""")"&","&"}"&"]")
Зауважу, що для коректної роботи з цифрами треба замінити крапки на коми =SUBSTITUTE (де шукаємо;що шукаємо;на що міняємо;), на прикладі покажу у наступних статтях.
Для тих хто дочитав додаткові варіанти з альтернативних ресурсів таких як Мінфін і GoogleFinance.
Нижче приклад формул як отримати дані з сайта Мінфін - приклад за посиланням.
=ImportHTML("https://minfin.com.ua/currency/";"table";0;"en")
+
=QUERY('Мінфін'!A4:G50;"SELECT E, G WHERE A = 'Приватбанк'"; 1)
Дані з Google отримує функція GoogleFinance в Google Таблицях використовується для отримання фінансових даних про ринки акцій, валют, фондові індекси та інші фінансові параметри. Ось приклад формули та її складові:
Синтаксис функції GoogleFinance: =GOOGLEFINANCE("symbol"; "attribute"; "start_date"; "end_date"; "interval")
Де:
"symbol" - це символ фінансового інструменту (наприклад, TSLA для Tesla, AAPL для Apple і т.д.).
"attribute" - це атрибут, що визначає фінансову інформацію, яку ви хочете отримати (наприклад, "price" для ціни акції, "marketcap" для ринкової капіталізації тощо).
"start_date" і "end_date" (необов'язкові) - це дати, між якими ви хочете отримати дані.
"interval" (необов'язковий) - це інтервал часу для отримання даних (денний, щотижневий, щомісячний тощо).
Тож щоденний курс за 2023 рік :
=GOOGLEFINANCE("USDUAH";"price"; DATE(2023;1;1);DATE(2023;12;31);"DAILY"
Курс на конкретну дату:
=GOOGLEFINANCE("USDUAH";"price";I1) - у I1 - вказуємо потрібну дату.
Page updated
Google Sites
Report abuse