Как вести учет криптовалютного портфеля в Гугл Таблицах

Введение

«Деньги любят счет» - это пословица, как никакая другая, очень точно описывает всю суть успешного распоряжения своими деньгами.

На сегодняшний день существует множество инструментов и сторонних сервисов (Blockfolio, Coinmarketcap, Cryptocompare и т.п.) для ведения учета и контроля своего инвестиционного криптопортфеля.

Также существуют инструменты для работы в Google Sheets. В таблицах есть функция GOOGLEFINANCE("BTCUSD"), которая позволяет учитывать курс биржи в паре доллара к биткоину. А в аддонах к таблицам можно найти CRYPTOFINANCE. Эта функция опирается на данные CoinMarketCap и подтягивает их по API. Данные обновляются всякий раз, как вы открываете или перезагружаете документ.

У вышеуказанных функций есть свои недостатки, например GOOGLEFINANCE имеет сильно ограниченное число криптовалютных пар. У CRYPTOFINANCE есть большинство криптовалютных пар имеющихся на различных биржах, но есть свои ограничения по количеству запросов. Поначалу я сам пользовался сервисом CRYPTOFINANCE на платной основе. Но в процессе эксплуатации понял что нужно делать что-то своё. Чтобы было максимально гибко, просто и доступно каждому.

В этой статье я покажу как в гугл таблицах простым способом и бесплатно можно получать информацию по криптовалютным парам.

Бесплатный скрипт

Для учёта инвестиций я написал свой собственный скрипт. Скрипт циклично собирает нужную информацию с криптовалютных бирж посредством API которые они предоставляют. Сейчас скрипт поддерживает 7 криптобирж:

  • Binance;
  • Gateio;
  • Bitfinex;
  • Poloniex;
  • Hotbit;
  • Bittrex;
  • Kucoin;
  • FTX;
  • Bybit;
  • Huobi;
  • OKX.

Логика и пример

Логика здесь предельно простая. Скрипт каждые 5 минут собирает информацию с криптовалютных бирж и обновляет данные в гугл таблице, которую я выложил в открытый доступ - crypto-fans.club coinstat. Таким образом любой желающий может получать актуальную информацию по интересующим его криптовалютам простым копированием данных.

Например, нас интересуют данные с биржи Binance.

Мы создадим новую вкладку в нашей таблице и с помощью формулы перенесём данные из общей таблицы в свою.

  • В гугл таблице, где будет наш криптопортфель, создадим новую вкладку под названием cryptodataBinance (название на свое усмотрение).
  • В ячейке А1 вводим формулу которая скопирует данные по бирже Binance, из вкладки общего файла, в наш файл с криптопортфелем

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PLLq6YTmFFMklDHedBb_tVR7NVjsQrSVqiUJW2TeuHQ/edit?usp=sharing"; "cryptodataBinance!A1:Z5000")

Теперь при обновлении скриптом общего файла гугл таблиц, данные будут обновляться и в нашей вкладке в гугл таблице с портфелем. Это как я уже писал выше будет происходить каждые 5 минут.

  • Теперь, допустим в нашем криптопортфеле мы хотим вывести значение торгового курса пары BTCBUSD. Для этого, в ячейку где хотите отображать значение курса btcbusd, вносим следующую формулу:

=INDEX(cryptodataBinance!$A$1:$Z, MATCH("btcusdt",cryptodataBinance!$B$1:$B,0), 3)

Данная формула ищет во вкладке cryptodataBinance, которую мы создали в нашем файле, торговую пару btcusdt по всему столбцу B. Если торговая пара в столбце найдена, то берутся данные в той же строке только из третьего столбца C, а это именно цена данной торговой пары. Очень просто. Закрепим двумя примерами, так будут выглядеть формулы для Harmony к Binance USD и Ankr к Bitcoin.

=INDEX(cryptodataBinance!$A$1:$Z, MATCH("onebusd",cryptodataBinance!$B$1:$B,0), 3)

=INDEX(cryptodataBinance!$A$1:$Z, MATCH("ankrbtc",cryptodataBinance!$B$1:$B,0), 3)

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

Пример портфеля

Вот так примерно я оформил свой портфель:

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

Теперь данную вкладку можно будет найти кликнув на кнопку в левом нижнем углу.

Заключение

Данный способ хорош тем, что он требует от пользователя минимальных навыков и знаний, нет лимитов и ограничений, а главное он абсолютно бесплатен. Спасибо за внимание, всем профита.

0
105 комментариев
Написать комментарий...
Иван Ильин

Сделал более приятное оформление и авто расчет средней цены закупа. Забирайте, кому надо!
https://docs.google.com/spreadsheets/d/12Bqy_iXz2WzVb8UyuXDBDaSuVenvDBnXkYvFQjuuim4/edit?usp=sharing

Ответить
Развернуть ветку
Mark Avreal

Как сделать тот же самый лист "портфель" но с запросом не c Binance. А с других бирж?

Ответить
Развернуть ветку
Mark Avreal

И импортировать курс с самих бирж, а не CoinMarketCap

Ответить
Развернуть ветку
Иван Ильин

Можно еще дополнить. Вставьте формулу, пример =D2/C2 , для автоматического вычисления Цены покупки(средняя цена покупка). Будет полезно при докупки на просадках для усреднения, не надо будет на калькуляторе считать))

Ответить
Развернуть ветку
Виктор Обломов
Автор

Да, поправил в шаблоне, спасибо за обратную связь

Ответить
Развернуть ветку
Виктор Обломов
Автор

создал группу в телеграм - "t.me/crypto_fans_club"; для более быстрой связи. Милости прошу

Ответить
Развернуть ветку
Serg Maljuta

Здравствуйте. Спасибо за Ваш труд, очень полезная таблица. У меня возникла проблема, не ищет по "=INDEX..............."
ошибка выскакивает "Ошибка
Синтаксическая ошибка в формуле." Помогите пожалуйста, не могу понять, копировал все как у Вас, но нет.

Ответить
Развернуть ветку
Алексей Гунбин

Здравствуйте, премного благодарен Вам за помощь. Компьютером вроде неплохо пользуюсь, но что касается программирования - темный лес для меня). Подскажите, пожалуйста, как можно аналогичным образом спарсить ATH(наивысшая цена за все время all time high). И еще не совсем понял как можно собственноручно обновлять таблицу ?

Ответить
Развернуть ветку
Виктор Обломов
Автор

обновление раз в 5 минут, сами вы не сможете обновлять

Ответить
Развернуть ветку
Alexandra Maltseva

зарегалась, чтобы написать этот комментарий)
Виктор, спасибо вам огромное! давно хотела так сделать, но разбираться в скриптах не хотелось) случайно нашла вашу статью, и вот даже спустя полтора года она очень облегчает людям жизнь)

Ответить
Развернуть ветку
Савин Евгений

добрый день. Таблица перестала обновлять данные(((

Ответить
Развернуть ветку
Sta Deni

Почему у меня не получается? "Мы создадим новую вкладку в нашей таблице и с помощью формулы перенесём данные из общей таблицы в свою."- я создаю новую таблицу у себя в Гугл бокс и копирую в нее вашу ссылку в ячейку A1 "=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1msTYPGzh33IRwAvLRIj7sbbZdWO1FxPuVAz9lcExwJg/edit?usp=sharing";, "cryptodataBinance!A1:Z5000")" пишет "ОшибкаСинтаксическая ошибка в формуле." пробовал по разному уже, запарился)

Ответить
Развернуть ветку
Виктор Обломов
Автор

Добрый день! писал в комментариях выше)
Изза последних событий, решил перенести основные файлы на яндекс диск. Не все пошло так как надо. Вобщем ссылка на файл изменилась, на следующую: https://docs.google.com/spreadsheets/d/1PLLq6YTmFFMklDHedBb_tVR7NVjsQrSVqiUJW2TeuHQ/edit?usp=sharing

соответственно формула будет такой:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PLLq6YTmFFMklDHedBb_tVR7NVjsQrSVqiUJW2TeuHQ/edit?usp=sharing";, "cryptodataBinance!A1:Z5000")

Также Ваш файл, где вы прописываете формулы сам должен быть доступен по ссылке, иначе формулы не работают. Это правило гугл таблиц.
также рабочий шаблон доступен тут:
https://docs.google.com/spreadsheets/d/1bxISMBsmbPeqzYeLLbErYlZnCrSneU_KTZCMfC0wego/edit?usp=sharing
берите формулы с него

Ответить
Развернуть ветку
8 комментариев
Сергей Корнев

Виктор, подскажите, пожалуйста, можно ли подтягивать котировку токена с coinmarketcap.com, на Бинансе есть далеко не все, нужные мне монеты.
Если, да, то как будет выглядеть формула для отображения текущей цены?
У меня никак не получается закончить со своей табличкой, осталось подтянуть текущие курсы и можно будет пользоваться...

Ответить
Развернуть ветку
Виктор Обломов
Автор

У coinmarketcap.com есть свой API, и в принципе можно тянуть данные с койнмаркеткапа, только я не вижу в этом смысла. Вы же покупаете монету на какой-то из бирж, так почему бы вам не тянуть сразу из биржи информацию? ведь койнмаркеткап тоже из бирж тянет инфу. Тот же BSW например есть на бирже Hotbit.
Вот Вам формула: =INDEX(cryptodataHotbit!$A$1:$Z, MATCH("BSW_USDT",cryptodataHotbit!$B$1:$B,0), 3)

Ответить
Развернуть ветку
2 комментария
Даниил Сотников

Виктор, я имел ввиду файл как у вас выглядит ваш портфель на примере выше) начальные данные таблицы, чтобы можно было внести туда свою крипту

Ответить
Развернуть ветку
Виктор Обломов
Автор

создал шаблон портфеля, https://docs.google.com/spreadsheets/d/1tPa-oFjd-dbp9n4z0Xw_gRHolvSwGzXByrd5dmQqd8k/edit?usp=sharing. Данные как в этой статье тянутся из вкладок (они скрыты в в этом файле).

Ответить
Развернуть ветку
2 комментария
Sta Deni

Добрый день, а можно ли как-то добавить биржу OKX?
Цена монеты на биржах разная плюс хочется к этой бирже привязаться

Ответить
Развернуть ветку
Виктор Обломов
Автор

Добрый! API биржи вроде позволяет. В планах добавить биржи Huobi, kraken, coinbase, ну и OKX добавлю.

Ответить
Развернуть ветку
2 комментария
Евгений Добрый

Есть ли возможность так же котировки брать с p2p биржи binance?

Ответить
Развернуть ветку
Виктор Обломов
Автор

api Бинанс вроде как не позволяет брать данные p2p, по крайней мере я не нашел. Можно конечно парсить данные не через API, а через браузер, но это уже кастом. Не думаю что есть массовая потребность людей, которые нуждаются чтобы данные p2p выводились в гугл таблицах. Ну я к тому, что скорее всего вы единичный случай, потому и кастом))) Вам проще наверно будет к фрилансерам обратиться. Они Вам напишут парсер. Ну или я могу, если по цене сойдемся.
Есть исходники на Гитхабе https://github.com/1412dev/binance-p2p-api
позволяют получать данные p2p, возможно вам будет полезно

Ответить
Развернуть ветку
Ольга Фридман

Евгений, добрый день. У Вас работает таблица на сегодняшний день? У меня работало всё исправно (спасибо Виктору за труд), но несколько дней уже не грузятся котировки с бирж. У Вас как?

Ответить
Развернуть ветку
2 комментария
Константин Белянин

А сможешь подсказать как создать идентичную таблицу самостоятельно?

Ответить
Развернуть ветку
Виктор Обломов
Автор

Посмотрите тут в комментариях я дал ссылку на шаблон такой таблицы. Скопируйте этот файл, и берите за основу

Ответить
Развернуть ветку
Роберт Струговщиков

Добрый день! Помогите пожалуйста понять в чем дело. При введении формулы - выдает ошибку.

Ответить
Развернуть ветку
Виктор Обломов
Автор

Название вкладки как у вас? cryptodataBinance?

Ответить
Развернуть ветку
2 комментария
Nasya727

Отлично, я очень хотела себе такой сделать))) а то приложения глюченные, профит от сделки тяжело отслеживать)

Ответить
Развернуть ветку
Виктор Обломов
Автор

спасибо, если будут вопросы, пишите

Ответить
Развернуть ветку
1 комментарий

Комментарий удален модератором

Развернуть ветку
Сергей Корнев

Виктор, спасибо за ответ!
т.к. я покупаю токены на разных биржах, а слежу за ценой на койнмаркете - решил что надо как-то в таблицу подтянуть цены именно из него.
Гугл удобно реализовал функцию googlefinance для биржи, а вот для крипты такого нет и мое незнание формул не позволяет реализовать задуманное.
Вставил вашу формулу - не работает, но в любом случае спасибо, буду гуглить, как составлять формулу и вытягивать данные с бирж!

Ответить
Развернуть ветку
Виктор Обломов
Автор

Сергей, формула будет работать если сделаете как в статье написано

Ответить
Развернуть ветку
1 комментарий
Оранжевый Сноуден

И вопрос, как вы реализовали колонку "Изменение за 24 часа в %"? Это изменение которые во вкладке "Change1d"?

Ответить
Развернуть ветку
Оранжевый Сноуден

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

Ответить
Развернуть ветку
2 комментария
Виктор Обломов
Автор

совершенно верно

Ответить
Развернуть ветку
Иван Ильин

А есть способ подтягивать такие же данные с FTX и BYBIT?

Ответить
Развернуть ветку
Виктор Обломов
Автор

API этих бирж позволяет подтягивать данные, думаю на днях добавлю

Ответить
Развернуть ветку
6 комментариев

Комментарий удален модератором

Развернуть ветку
Даниил Сотников

Добрый день! Вы могли бы поделиться файлом под портфель? Я так понял, что учет ведется на его основе и скрипт интегрируется в начальный файл?

Ответить
Развернуть ветку
Иван Ильин

Присоединяюсь. Если, что тоже этот скрипт нужен)

Ответить
Развернуть ветку
Виктор Обломов
Автор

не совсем понял про файл под портфель. Скрипт заполняет данные в файл https://docs.google.com/spreadsheets/d/1msTYPGzh33IRwAvLRIj7sbbZdWO1FxPuVAz9lcExwJg/edit#gid=818825835 . Этот файл открыт на чтение всем желающим. Чтобы уменьшить количество запросов к файлу (у вас же несколько криптовалют) в статье я пишу формулы, чтобы забирать из этого файла сразу всю вкладку с биржей которая нужна, на свою вкладку в своем файле. И потом уже со своей вкладки вы тяните данные какие нужны точечно.
добавлю, сам скрипт написан на python, он НЕ интегрирован в файл, он лежит на отдельном веб сервере, и циклически парсит данные с бирж. Скриптовые возможности гугл таблиц не позволяют реализовать такие вещи.

Ответить
Развернуть ветку
1 комментарий

Комментарий удален модератором

Развернуть ветку
Иван Ильин

Приветствую. Сегодня не работает. Данные не подтягиваются в файлах бирж Ошибка
Внутренняя ошибка при импорте диапазона.

Ответить
Развернуть ветку
Виктор Обломов
Автор

написал выше, переносил файлы на яндекс диск. Не все пошло так как надо. Вобщем ссылка на файл изменилась, на следующую: https://docs.google.com/spreadsheets/d/1bxISMBsmbPeqzYeLLbErYlZnCrSneU_KTZCMfC0wego/edit?usp=sharing
https://docs.google.com/spreadsheets/d/1PLLq6YTmFFMklDHedBb_tVR7NVjsQrSVqiUJW2TeuHQ/edit#gid=1059624245
также поправил ссылку в самой статье

Ответить
Развернуть ветку
Анатолий Снигирев

большое вам спасибо за вашу работу и труд!

Ответить
Развернуть ветку
Виктор Обломов
Автор

👍

Ответить
Развернуть ветку
Иван Ильин

Добрый день, Виктор. Сегодня данные с bybit не подтягиваются.

Ответить
Развернуть ветку
Виктор Обломов
Автор

точно? только что проверил, вроде все как часы работает

Ответить
Развернуть ветку
1 комментарий
Иван Ильин

Все норм стало.

Ответить
Развернуть ветку
Евгений Ахмадуллин

Почему-то с хотбита неверные котировки подтягивает((

Ответить
Развернуть ветку
Виктор Обломов
Автор

если можно, конкретней напишите пожалуйста, какие пары, что посмотреть. Просто проверил на примере Эфириума, все вроде достоверно подтягивает

Ответить
Развернуть ветку
3 комментария
Виктор Обломов
Автор

Добавил биржи Huobi, OKX, Hitbtc, Bingx

Ответить
Развернуть ветку
Валерий Дуберштейн

спасибо за проделанную работу Виктор 🙏 Подскажите почему на OKX change7d change30d прочерки стоят?

Ответить
Развернуть ветку
Виктор Обломов
Автор

потому что OKX была добавлена недавно, меньше чем 7 дней назад. Как будет статистика за 7 дней или за 30 дней, тогда будут данные

Ответить
Развернуть ветку
Sergiy

Виктор спасибо за труд! Можете добавить еще вкладку Сoinmarketcap.com?

Ответить
Развернуть ветку
Виктор Обломов
Автор

api Coinmarketcap вроде как позволяет брать данные, но какой смысл, если койнмаркеткап сам берет данные из API бирж, лучше уж добавлять биржи как первоисточник, разве нет?

Ответить
Развернуть ветку
Илья Горбачев

Здравствуйте, Виктор. Ваша таблица перестала обновляться, исправьте пожалуйста.

Ответить
Развернуть ветку
Ольга Фридман

Теперь понятно, что проблема не в моих настройках. Виктор, пожалуйста поправьте передачу данных!🙏К хорошему быстро привыкаешь ) Без таблицы, как без рук.

Ответить
Развернуть ветку
Илья Горбачев

Всё заработало, спасибо!

Ответить
Развернуть ветку
1 комментарий
Виктор Обломов
Автор

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

Ответить
Развернуть ветку
SKIDKOVOZ ХАЛЯВА СКИДКИ РАСПРОДАЖИ

А биржу exmo можно добавить? Или может распишешь как это делается чтобы можно было самостоятельно сделать? И я так понимаю, ссылка на файл который собирает инфу со всех бирж, если ты удалишь его, то все таблички которые создавались на базе этого файла - https://docs.google.com/spreadsheets/d/1PLLq6YTmFFMklDHedBb_tVR7NVjsQrSVqiUJW2TeuHQ/edit превратятся в тыкву? Или можно его скопировать и обращаться из своей копии? Он будет работать?

И еще вопрос, как-то можно сделать обновление по кнопке? Чтоб бумкнул по кнопке, обновились курсы и обновило экран портфеля?

Ответить
Развернуть ветку
Виктор Обломов
Автор

"И я так понимаю, ссылка на файл который собирает инфу со всех бирж, если ты удалишь его, то все таблички которые создавались на базе этого файла - https://docs.google.com/spreadsheets/d/1PLLq6YTmFFMklDHedBb_tVR7NVjsQrSVqiUJW2TeuHQ/edit превратятся в тыкву?"

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

По поводу кнопки, это не актуально. Ваш портфель подтягивает актуальную инфу с главного файла, которые обновляется раз в 5 минут, можно сделать чаще на другом сервере, но я не вижу в этом смысла.

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

Ответить
Развернуть ветку
1 комментарий
Андрей Сычев: инвестиции

Круто, а я аналогично делал, только по акциям, и без скрипта

Ответить
Развернуть ветку
Андрей Сычев: инвестиции

Виктор, а почему я не вижу скрипт в вашей таблице?

Ответить
Развернуть ветку
Виктор Обломов
Автор

Какой скрипт? Скрипт просто записывает данные в главную гугл таблицу а вы уже тащите данные из этой гугл таблицы

Ответить
Развернуть ветку
2 комментария
vitaliy isteryka

Здравствуйте. Хотел бы парсить данные с таблицы в вордпресс, как осуществить подскажите? Мне нужна определенная ячейка с ценой

Ответить
Развернуть ветку
Faruk Mekidzesh

Уже несколько дней не обновляются данные с бирж(. Год пользовался данными из этих таблиц, все работало

Ответить
Развернуть ветку
Виктор Обломов
Автор

починил, все работает

Ответить
Развернуть ветку
Jacob Hahn

Таблица перестала работать, Виктор поправьте. Благодарю за ваш труд!

Ответить
Развернуть ветку
Игнат Иосифович

Автор в своей группе тг отписался, что поправит, как найдет время

Ответить
Развернуть ветку
1 комментарий
Виктор Обломов
Автор

все работает на сегодняшний день

Ответить
Развернуть ветку
102 комментария
Раскрывать всегда