How to value your Stocks & Crypto Portfolio on Google Sheets

Eloise
Eloise
Oct 20, 2020 · 10 min read

Simple Google Sheet Tools to value your crypto and stock assets. Duplicate the sheet and make it your own.

TRY IT OUT: LIVE SAMPLE SHEET

Image : Portfolio Valuation Example using Google Sheets, Duplicate and Make it your own

At first, when I created CRYPTOTOOLS, I wanted to have a private flexible way to analyze whale moves on the blockchain, value trades I had made, find arbitrage opportunities and compute statistics in Google Sheets. Then friends got interested, so I made it more efficient and made the code public for everyone. I hope you can benefit from my work. And for developers who wish to directly connect to my API, let me know. I will do the necessary to create an API documentation.

The objective of this article is to show how anyone can automatically retrieve stock & cryptocurrency prices, crypto balances from public blockchain addresses, lending and farming rates from different lending platforms, and stacking and rewards from Proof of Stake blockchains.

These are custom built-in functions that I have created on my own. You can use and replicate the code in any Google Sheets. Duplicate the following Open Sourced Google Sheet and make it your own. Or go to the Github repo, and upload the following codes CryptoTools + GeckoPrices into your script editor in Google Sheets. At the end of this Medium, I’ve referenced a “How to upload javascript code in Google Sheets”.

I will be describing each of the functions, as well as their purpose and how to use them. To make it easier for users to get acquainted with formulas, I created a Google Sheet Template for testing:

CrypoTools Google Template for hands on learning.

For privacy and if you want to make it your own, click on the blue copy link, it will create a duplicate of the sheet and save it to your Google Drive account.

How to value your cryptocurrencies?

Get CoinGecko Cryptocurrency Prices

Coingecko is a real-time cryptocurrency price provider like CoinMarketCap.

https://www.coingecko.com/en

The GECKOPRICE custom built-in function imports CoinGecko’s cryptocurrency prices into Google spreadsheets. The code is completely Open Source and connects directly to the API provided by CoinGecko. For those of you interested in getting the VBA code for Excel, here you go.

The price feed is received in a cell. By default, the data gets transformed into a float number so it looks more like a normal price feed.

GECKOPRICE Function

Syntax: GECKOPRICE(ticker, currency,[optional refresh attribute])

Example: GECKOPRICE(“BTC”, “USD”)

1st parameter: the cryptocurrency ticker you want the price from, example BTC. 2nd parameter: the fiat currency ex: USD, EUR, GPB etc… 3rd parameter an optional fixed cell for automatic refresh of the data, this parameter

CoinGecko Google Sheet Formula GECKOPRICE
Left Panel Getting Ethereum’s price in Euros-> =GECKOPRICE(“ETH”,”EUR”) — Right Panel Bitcoin’s price in Dollars -> =GECKOPRICE(“BTC”,”USD”)

TRY IT OUT: LIVE SAMPLE SHEET , go to sheet name PORTFOLIO EXAMPLE

For more information on all of CoinGecko’s formula, please refer to the following medium.

How to value STOCK prices with the GOOGLEFINANCE function ?

The GOOGLEFINANCE function allows you to import real-time financial and currency market data straight into Google Sheets. As well as enabling you to track current stocks and shares information, it can also be used to retrieve historical securities data.

This function imports data from the Google Finance web application, which provides daily stock prices, news from the currency and financial markets, and other information on market trends. Google Finance can be accessed from the Google menu like all other Google applications, or simply by searching for a stock on Google, which will bring up the Google Finance information relating to that stock.

Syntax: GOOGLEFINANCE(ticker, [attribute])

https://finance.yahoo.com/quote/AAPL/

In the left example, I show how to get Apple’s stock price (“AAPL” :ticker).

=GOOGLEFINANCE(“AAPL”)

How to retrieve balances from cryptocurrency blockchain addresses ?

CRYPTOBALANCE function

The CRYPTOBALANCE function will help you get in real time anyone’s wallet balance from a public blockchain address. It currently holds access to 100+ blockchains and 1000+ cryptocurrencies.

Syntax: CRYPTOBALANCE(ticker, address, [optional refresh attribute])

1st parameter: the cryptocurrency ticker you want the price from, example BTC. 2nd parameter: the public blockchain address where the crypto is stored. 3rd parameter an optional fixed cell for automatic refresh of the data, this parameter

Verify the balance from CRYPTOBALANCE with what you get on Blockchain.com

Example BINANCE BTC Cold Wallet Balance:

=CRYPTOBALANCE(“BTC”,“34xp4vRoCGJym3xR7yCVPFHoCNxv4Twseo”, $A$1)

=CRYPTOBALANCE(“BTC”,“34xp4vRoCGJym3xR7yCVPFHoCNxv4Twseo”, $A$1) (Old screenshot from Aug 2019)

TRY IT OUT: LIVE SAMPLE SHEET , go to sheet name PORTFOLIO EXAMPLE

You can check all cryptocurrency available

> ERC20-BEP20

> Other Blockchains

> Staking Coins

> Reward Coins

If you want to have access to a new cryptocurrency, don’t hesitate to let me know at https://t.me/TheCryptoCurious. If you want to get more details about CRYPTOBALANCE, you will find here the first post I have published on the function.

UPDATE JAN 2021: BALANCE FROM ERC20 CONTRACT ADDRESS

Example Uniswap Balance:

Contract address: 0x1f9840a85d5af5bf1d1762f925bdaddc4201f984
Holder address: 0xe3953d9d317b834592ab58ab2c7a6ad22b54075d

=CRYPTOBALANCE(“0x1f9840a85d5af5bf1d1762f925bdaddc4201f984”,“0xe3953d9d317b834592ab58ab2c7a6ad22b54075d”)

UPDATE JAN 2021: BALANCE FROM BEP20 CONTRACT ADDRESS

BEP-20 is a token standard on Binance Smart Chain that extends ERC-20

Example Pancakeswap Balance:

Contract address: 0x0e09fabb73bd3ade0a17ecc321fd13a19e81ce82
Holder address: 0x000000000000000000000000000000000000dead

For BEP20 contract addresses to work, you need to put a b in front like as follows. It will indicate to the algo that you are searching for balance on binance smart chain.

=CRYPTOBALANCE(“b0x0e09fabb73bd3ade0a17ecc321fd13a19e81ce82”,“0x000000000000000000000000000000000000dead”)

UPDATE JAN 2021: CRYPTOSUMETH function

I created the CRYPTOSUMETH function to get directly the total USD amount on any ERC20 address. When you hold many different coins on the same address, it is easier to use this function than looking one coin at a time.

Syntax: CRYPTOSUMETH(address, [optional refresh attribute])

1st parameter: the public blockchain address where the crypto is stored. 2nd parameter an optional fixed cell for automatic refresh of the data, this parameter

Total USD amount https://ethplorer.io/address/0xbaa12224fc95be819df67f63d3f981542e275cb2
Total ERC20 USD Amount + ETH USD Amount = $57'690,13

If you had used the CRYPTOBALANCE function, you would have to add all 72 cryptocurrencies (MKR, SNT, TKN, OMG etc..) from the address in order to get the whole USD balance amount. It would have been off-puting and quite long without this method. I’ve taken all these screenshots within a few hours difference, this is why you will notice the USD amount differences on CRYPTOSUMETH function below:

=CRYPTOSUMETH(“0xbaa12224fc95be819df67f63d3f981542e275cb2”)

For more real-time examples go to sheet name PORTFOLIO EXAMPLE.

How to get lending rates and farming yields from lending platforms?

UPDATE JAN 2021: CRYPTOFARMING “deprecated”

The CRYPTOLENDING & CRYPTOFARMING functions will be useful to you if you want to arbitrage lending platforms. Or if you need to keep a tab or perform some accounting on the cryptocurrency amounts you have earned from lending/borrowing/farming cryptocurrencies.

Image : Import directly in your Google Sheets yield farming rates and DeFi lending rates

CRYPTOLENDING Function

I created the CRYPTOLENDING function to return cryptocurrency lending and borrowing rates from different lending platforms. Here follows a list of some of the platforms and coins you can retrieve rates:

MAKERDAOCOMPOUND DYDX — NUOFULCRUMAAVE etc…ETH — DAI — USDC— WBTC — USDT— WBTC — SNX — LINK— REP etc…

You will be able to access the borrowing and lending APR (Annual Percentage Rate) through the following formula:

Syntax: CRYPTOLENDING(platform, ticker, side,[optional refresh attribute])

I open sourced the code for anyone that wishes to get the rates. The APR Lending/Borrowing rates are directly taken from CoinMarketCap & DefiPulse.

Below is a representation of rates from DefiPulse. If you want to get the lending rate on Compound for USDC, the formula is

CRYPTOLENDING(“COMPOUND”, “USDC”,”APR_LEND”)

LENDING RATE EXAMPLE SHEET

If you want to get the borrowing rate on C.R.E.A.M Finance for USDC, the formula is CRYPTOLENDING(“CREAM”, “USDC”,”APR_BORROW”). For more information on this function, please refer to the following Medium article or go to the LENDING RATE EXAMPLE sheet.

CRYPTOFARMING Function

Yield farming, also referred to as liquidity mining, is a way to generate rewards with cryptocurrency holdings. In simple terms, it means locking up cryptocurrencies and getting rewards.

Yield farming platforms

Syntax: CRYPTOFARMING(project name, ticker, period,[optional refresh attribute])

The figure above shows some of the project name and tickers available. For a full list go to https://coinmarketcap.com/yield-farming/ or contact me. There are 4 different periods possible, daily “D”, weekly “W”, monthly “M” and yearly “Y”.

How to retrieve the weekly farming yield for DAI-ETH on Sushi?

Solution : CRYPTOFARMING(“SUSHI”, “DAI-ETH”,”W”)

Staking & Rewards from Proof of Stake Blockchains

Staking cryptocurrency means that you are holding cryptocurrency to verify transactions and support the network. In exchange for holding the crypto and strengthen the network, you will receive a reward. You can also call it an interest. With staking you can generate a passive income by holding coins.

CRYPTOSTAKING & CRYPTOREWARDS are 2 functions I’ve created in order to get the amounts staked as well as the rewards received in return for staking. These 2 functions are shown on the sheet named STAKING — REWARDS — TEZOS EXAMPLE.

Tezos example of the use of the Staking & Reward function on Google Sheets

For more information on those 2 functions, please refer to the following Medium article.

Future Developments Excel

For increased privacy and for people used to work with excel, I have created a beta version on Excel. As of now it only works on Microsoft. If you are familiar with VBA you can check for yourself and download the code from Github here:

These code version are very preliminary. If you need help and personalization or want to receive directly the excel file, don’t hesitate to contact me on telegram chat or leave a message in the comments.

Github Open-Source Setup

Please refer to the following Medium article if you wish to get more information on how to integrate directly the javascript code into your Google Sheets.

Conclusion

You should now be able to :

a. Value cryptocurrency and stock prices

b. Retrieve balances from cryptocurrency blockchain addresses

c. Get lending rates and farming yields from lending platforms

d. Get staking and rewards from proof of stake blockchains

Simple Google Sheet Tricks to value your assets without relying on 3rd party apps. Duplicate the sheet and make it your own.

ACCESS LIVE SAMPLE SHEET

This is ongoing work. If you find errors, please do not hesitate to let me know. Feedback is very welcomed. A telegram chat is also available for support. If this project adds any value to you and/or are looking for personalized coding on your Google Sheets, don’t hesitate to leave a message. Thank you for reading.

Also, Read

Coinmonks

Coinmonks is a non-profit Crypto educational publication.