Bitcoin Forum
September 23, 2019, 12:15:01 PM *
News: If you like a topic and you see an orange "bump" link, click it. More info.
 
   Home   Help Search Login Register More  
Pages: [1] 2 3 »  All
  Print  
Author Topic: Как подтянуть в Excel ценник монеты с Coinmarketcap (API v2)  (Read 2281 times)
LiluSG
Full Member
***
Offline Offline

Activity: 588
Merit: 196


View Profile
February 02, 2018, 10:30:54 AM
Last edit: September 05, 2018, 09:18:47 AM by LiluSG
Merited by k0er (2), RuSS512 (2), explorder (2), Frayz (2), xandry (1), klarki (1), Vadi2323 (1), TheFuzzStone (1), JohnSilver (1), rafaelyarulin (1), Ognik33 (1), sairji (1), Zedxxx (1), kefir56 (1)
 #1

Ребята, привет Smiley
Сегодня хотел с вами поделиться о том, как подтянуть в Excel ценник монеты с coinmarketcap, достаточно простыми манипуляциями и в пару кликов

Да, достаточно много уже готовых таблиц и решений, если поискать. Но в своё время, когда искал и разбирался сам, я потратил целый день на то, что бы решить эту задачу
 
Например, готовые таблицы, каких их только нет и какие только не встречал. Попадались даже с макросами, которые логи цены по временному шагу пишут и т.д. с кучей настроек, как в самолёте Smiley Но в готовой таблице нет объяснения всех нюансов, как она сделана и т.д. И это было проблемой, так как мне нужна была только часть функций, а осваивать эксель до всех его тонкостей не хотелось

Зачем мне это нужно было?
Я хотел сделать таблицу по ценам того что получил, например, с баунти и что уже есть на маркеткап, выдрать это по api и иметь возможность обновить с экселя. Не хотелось держать это с общим портфелем и смешивать всё. А общая картина по пулу монет нужна была

В конечном итоге всё превратилось в 9 столбов
- Название монеты и её тикер
- Цена на пре-ико и ико в баксах (это забивалось в ручную)
- Цена в долларе и битке по маркету
- Кол-во монет (вносилось вручную)
- Умножение кол-ва с выводом суммы в баксе и битке

Весь процесс добавления данных, который занимает 30 секунд, разложен в пошаговых картинках. В силу обновлений API на койнмаркете, я обновил визуальную инструкцию



В редакторе мы прописывает такого рода код:

Code:
let
    Источник = Json.Document(Web.Contents("https://api.coinmarketcap.com/v2/ticker/2752/?convert=BTC&limit=10")),
    data = Источник[data],
    quotes = data[quotes],
    usd = Record.RenameFields(quotes[USD], {"price", "price_usd"}),
    usd1 = Record.RenameFields(usd, {"volume_24h", "price_btc"}),
    btc = Record.RenameFields(quotes[BTC], {"price", "price_btc"}),
    result = Record.Combine({data, usd1, btc}),
    #"Преобразовано в таблицу" = Record.ToTable(result),
    #"Строки с применным фильтром" = Table.SelectRows(#"Преобразовано в таблицу", each ([Name] = "name" or [Name] = "price_btc" or [Name] = "price_usd" or [Name] = "rank" or [Name] = "symbol"))
in
    #"Строки с применным фильтром"

Где наша основная строчка это:
Code:
https://api.coinmarketcap.com/v2/ticker/512/?convert=BTC&limit=10
А прибавка:
Code:
/?convert=BTC&limit=10
Добавит нам цену BTC в импорт данных по API.
По хорошему, всё дальнейшее изменение строчек идёт по id на маркете, в нашем случае ID Stellar - 512, у другого токена будет другой номер, вот и вся разница



Как видите всё достаточно просто и занимает немного времени. Таблицу и данные можно скомпоновать и оформить как удобно, с другими наворотами и возможностями экселя, красотой и прочим. Но это уже другая история Wink Кстати, во вкладке данные есть есть значок “Обновить всё”. Это собственно та волшебная кнопка, которая синхронизирует нашу таблицу по апи с койнмаркеткап. Но маркет опять же ввёл ограничение на эту функцию и если у вас больше 40 позиций, вы получите временный бан. Пока что, я не знаю это связанно с количеством обновлений за небольшое время, или это вопрос скорости и бан получается из-за того что идёт массовый запрос 100 позиций, например.

Данная инструкция написана с использованием Excel 2016. Для более ранних версий, есть дополнение
В более ранних версиях (2013 и 2010 с SP1, не ниже) для реализации этой идеи необходимо установить надстройку Power Query. Она бесплатная и легко скачивается с сайта Microsoft. В этом случае на 5-й страничке запрос будет создаваться не на вкладке "Данные", а на вкладке "Power Query". Все остальное без изменений.      

Спасибо, буду рад если это кому-то поможет и сэкономит время Smiley
Статья так же продублирована на steemit и medium в поддержку блог-начинаниям, так что буду рад внимаю
https://steemit.com/@lilusg
https://medium.com/@SergeyL
1569240901
Hero Member
*
Offline Offline

Posts: 1569240901

View Profile Personal Message (Offline)

Ignore
1569240901
Reply with quote  #2

1569240901
Report to moderator
1569240901
Hero Member
*
Offline Offline

Posts: 1569240901

View Profile Personal Message (Offline)

Ignore
1569240901
Reply with quote  #2

1569240901
Report to moderator
Advertised sites are not endorsed by the Bitcoin Forum. They may be unsafe, untrustworthy, or illegal in your jurisdiction. Advertise here.
F1ak
Full Member
***
Offline Offline

Activity: 784
Merit: 179


trend is your friend


View Profile
February 11, 2018, 05:59:26 AM
 #2

Отлично, еще осенью делал подобную таблицу, но столкнулся с проблемой - цена монеты в ячейке отображается с индексом "$" и поэтому эту ячейку уже в формулу не добавить.
Только я пошел по пути загрузки вебданных в виде всей страницы.
Теперь могу исправить.
Спасибо Smiley
Один вопрос, данные регулярно обновляются?

Баунтист седьмого дна...
Abalor
Member
**
Offline Offline

Activity: 308
Merit: 10


View Profile
February 11, 2018, 06:09:39 AM
 #3

Спасибо за информацию, только ещё вопрос, в гугл таблицах такое можно провернуть?
JohnSilver
Full Member
***
Offline Offline

Activity: 714
Merit: 234


🇧🇬 Trader Since MMXIII


View Profile
February 11, 2018, 08:44:14 AM
Last edit: February 11, 2018, 09:37:50 PM by JohnSilver
 #4

Спасибо за информацию, только ещё вопрос, в гугл таблицах такое можно провернуть?

Сейчас попробую что получиться: https://github.com/bradjasper/ImportJSON
Также смотрю и это: https://github.com/bradjasper/CryptocurrencySpreadsheetUtils

ПС: Разобрался! Открываем табличку в Google. В файл / настройки табличку задаем USA и GMT. В инструментах / редактор скриптов – копи/пейст на скрипта и нажимаем иконку для запись. Закрываем окно редактора. Потом в табличку:

Code: (Пример для Bittrex)
=ImportJSON("https://bittrex.com/api/v1.1/public/getTicker?market=USDT-BTC","/result/Last","noHeaders")

Все понадобится засунуть в функцию VALUE(...), а то возвращает число как стринг и не годиться...
JohnSilver
Full Member
***
Offline Offline

Activity: 714
Merit: 234


🇧🇬 Trader Since MMXIII


View Profile
February 11, 2018, 06:13:07 PM
Last edit: June 03, 2018, 02:23:19 AM by JohnSilver
Merited by LiluSG (1)
 #5

ПС: Рабочий линк в подпись (внизу в зеленом), а исходники влево земной шар под черное знамя!
________

Все получилось: https://drive.google.com/open?id=1nHhaL_J8id9wcBbuEvI0YYnGpMQd0f-pYf8MItX5GW8

К скрипту добавил функцию (A1 – это у меня ячейка где отображается дата обновления):
Code:
function LastUpdated() {
  SpreadsheetApp.getActiveSpreadsheet().getRange('A1').setValue(new Date());
}

В действительности дата отображается в J34 = A1, а цвет шрифта ячейке A1 сделал одинаков с цвет фона. Перешел на такой вариант, чтобы смог добавлять/удалят строки/колонки, а то ячейка где пишется дата фиксирована и мешается в данных при переменах в таблице.

Загружаем оригинального скрипта ImportJSON.gs и сохраняем. Добавляем второй скрипт LastUpdated.gs к проекту и сохраняем. Делаем триггер на функцию LastUpdated на каждую минуту или там как хотите. Google будет ругаться что скрипт неизвестен и это опасно. Нажимаем на подробности и внизу подтверждаем что хотим использовать.

Далее в табличку код ячейке E2 (пример для Bittrex, обратите опят внимание на A1):
Code:
=VALUE(ImportJSON("https://bittrex.com/api/v2.0/pub/currencies/getBTCPrice","/result/bpi/USD/rate_float","noHeaders",A1))

ImportJSON.gs: https://drive.google.com/open?id=1VGuycpqzV_nzUuNT6_415IF0TZsq3moM
LastUpdated.gs: https://drive.google.com/open?id=1hOZaRNWGP5oB1zCxqoNIeo8QtT32Pv4U

Также можно скачать оригинал с гитхаба и сделать второй скрипт вручную (код выше)...
Оригинал скрипта здесь: https://github.com/bradjasper/CryptocurrencySpreadsheetUtils

Только не хватает авторизация к API чтобы смог загружать и текущий баланс, но за сегодня хватает...
LiluSG
Full Member
***
Offline Offline

Activity: 588
Merit: 196


View Profile
February 11, 2018, 09:21:16 PM
 #6

Один вопрос, данные регулярно обновляются?

Ну, данные он черпает с койнмаркета, если там нет сбоев, то они обновлены реал-тайм.
В самой таблице в экселе, я по кнопке обновляю (я о ней написал в конце). Хотя видел макрос, который обновляет по открытию файла. Думаю что макросами можно сделать обновление и по таймингу
Сам не стал делать, что бы не грузило мне таблицу, когда не нужно)



Спасибо, хорошая работа. И добавили полезностей и фишек в топик, кому и пригодится  Smiley
sashadoc
Sr. Member
****
Offline Offline

Activity: 476
Merit: 250



View Profile
February 11, 2018, 09:48:23 PM
 #7

спасибо, очень полезная информация, отслеживать цену монеты не заходя каждый раз на сайт очень удобно
AmigoBitOK
Newbie
*
Offline Offline

Activity: 24
Merit: 1


View Profile
February 12, 2018, 05:16:16 PM
 #8

Только начинаю вникать в гугл таблицы плотно. Подскажи как рейтинг например с ICObench ещё подтянуть?
BitcoinBoster
Member
**
Offline Offline

Activity: 87
Merit: 10


View Profile
February 12, 2018, 06:28:32 PM
 #9

спасибо автору за замечательный пост, думаю ты потратил много времени на его создание,очень полезная информация, спасибо!
LiluSG
Full Member
***
Offline Offline

Activity: 588
Merit: 196


View Profile
February 13, 2018, 09:59:13 AM
 #10

Только начинаю вникать в гугл таблицы плотно. Подскажи как рейтинг например с ICObench ещё подтянуть?


Эксель может тягать инфу прям со страницы, формируя блок-зоны, но с маркетом это не прокатило, поэтому я искал способ по апи, так как оно там есть

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

Я думаю что нужно просто смотреть в сторону парсинга сайта, формирования общей таблицы, её обновления, фильтрации и т.д. Тут очень много нюансов и это дело для тех кто в теме. Опять же по причине нюансов и большой вариативности проблем, которые могут выползти.

Ещё можно попробовать напрямую написать на сайт и спросить если у них что-то подобное и если нет, могут ли сформировать инструмент, тот же апи с общей таблицей рейтингов. Что бы делать это в один запрос, а не положить сайт или затормозить его своим парсингом Smiley


спасибо автору за замечательный пост, думаю ты потратил много времени на его создание,очень полезная информация, спасибо!

Да не за что, надеюсь пригодилось)
На самом деле не так и много.. часа 2,5-3 где-то, большая часть времени ушло на картинки, их нарезку и фотошоп листов.
Но вот что бы найти и разобраться с этими манипуляциями в экселе, я тогда убил часов 10, перерыв много форумов и таблиц))
crampus
Member
**
Offline Offline

Activity: 252
Merit: 10


View Profile
February 13, 2018, 10:47:33 AM
 #11

exсel хорошая пронрамма, можно еще спрограмировать, там не очень сложная программа создается.
JohnSilver
Full Member
***
Offline Offline

Activity: 714
Merit: 234


🇧🇬 Trader Since MMXIII


View Profile
February 13, 2018, 01:04:30 PM
 #12

Только начинаю вникать в гугл таблицы плотно. Подскажи как рейтинг например с ICObench ещё подтянуть?

Есть там у них API, но надо добавить функцию для авторизацию к скрипту...

https://icobench.com/developers
Ihar Bit
Member
**
Offline Offline

Activity: 448
Merit: 11


View Profile
February 22, 2018, 07:18:14 AM
 #13

Небольшое дополнение. Все вышесказанное справедливо по отношению к Excel 2016. В более ранних версиях (2013 и 2010 с SP1, не ниже) для реализации этой идеи необходимо установить надстройку Power Query. Она бесплатная и легко скачивается с сайта Microsoft. В этом случае на 5-й страничке запрос будет создаваться не на вкладке "Данные", а на вкладке "Power Query". Все остальное без изменений.     

Cap1tanM0rgan
Member
**
Offline Offline

Activity: 98
Merit: 10


View Profile
February 22, 2018, 08:17:16 AM
 #14

Отлично, спасибо за инфу,давно искал как правильно сделать
Voron7
Full Member
***
Offline Offline

Activity: 490
Merit: 123


View Profile
March 22, 2018, 07:17:42 PM
 #15

Написано всё хорошо, но думаю стоит упомянуть, на какой версии Excel вы проделывали данную процедуру.
LiluSG
Full Member
***
Offline Offline

Activity: 588
Merit: 196


View Profile
March 22, 2018, 08:42:45 PM
 #16

Написано всё хорошо, но думаю стоит упомянуть, на какой версии Excel вы проделывали данную процедуру.

Хорошо, сейчас добавим. Тем более был коммент по версиям и я добавлю и его замечания
toha77777
Newbie
*
Offline Offline

Activity: 75
Merit: 0


View Profile
March 22, 2018, 09:06:28 PM
 #17

Spasiba za informaciju ,poleznaja tema !
LiluSG
Full Member
***
Offline Offline

Activity: 588
Merit: 196


View Profile
April 11, 2018, 09:10:56 PM
 #18

Апну тему, может кто новый увидит и будет полезно снова Smiley
gooogler
Newbie
*
Offline Offline

Activity: 80
Merit: 0


View Profile
April 23, 2018, 09:00:05 PM
 #19

Благодарю, именно то, что искал. Нашел сначала пост на Медиуме. Респект!
gooogler
Newbie
*
Offline Offline

Activity: 80
Merit: 0


View Profile
April 23, 2018, 09:30:01 PM
 #20

Еще нашел по теме. Гайд для тех кому нужно без лишнего кода встроить данные из Coinmarketcap в Google Таблицы — https://jbuty.com/how-to-get-crypto-currencies-rates-and-more-in-google-sheet-1a57e571bc14
Pages: [1] 2 3 »  All
  Print  
 
Jump to:  

Sponsored by , a Bitcoin-accepting VPN.
Powered by MySQL Powered by PHP Powered by SMF 1.1.19 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!