Виктор Обломов

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

Введение

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

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

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

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

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

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

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

  • Binance;
  • Gateio;
  • Bitfinex;
  • Poloniex;
  • Hotbit;
  • Bittrex;
  • Kucoin.

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

Логика здесь предельно простая. Скрипт каждые 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
67 комментариев
Написать комментарий...
Иван Ильин

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

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

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

Ответить
Развернуть ветку
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 комментария
Nasya727

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

А есть способ подтягивать такие же данные с 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
также поправил ссылку в самой статье

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

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

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

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

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

👍

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ответить
Развернуть ветку
Читать все 67 комментариев
null