How to value your Stocks & Crypto Portfolio on Google Sheets
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
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.
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
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])
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
Example BINANCE BTC Cold Wallet Balance:
=CRYPTOBALANCE(“BTC”,“34xp4vRoCGJym3xR7yCVPFHoCNxv4Twseo”, $A$1)
TRY IT OUT: LIVE SAMPLE SHEET , go to sheet name PORTFOLIO EXAMPLE
You can check all cryptocurrency available
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.
BALANCE FROM ERC20 CONTRACT ADDRESS
Example Uniswap Balance:
Contract address: 0x1f9840a85d5af5bf1d1762f925bdaddc4201f984
Holder address: 0xe3953d9d317b834592ab58ab2c7a6ad22b54075d
=CRYPTOBALANCE(“0x1f9840a85d5af5bf1d1762f925bdaddc4201f984”,“0xe3953d9d317b834592ab58ab2c7a6ad22b54075d”)
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”)
OTHER SPECIAL CASES
In other to access balances on matic, avax, fantom, moonriver smart chains, you can use the following rules:
............................ Special Cases ................ =CRYPTOBALANCE("ERC20 contract address","ERC20 holder address") =CRYPTOBALANCE("b"+ "BEP20 contract address","holder address") =CRYPTOBALANCE("m"+ "MATIC contract address", "holder address") =CRYPTOBALANCE("a"+ "AVAX contract address", "holder address") =CRYPTOBALANCE("f"+ "FANTOM contract address", "holder address") =CRYPTOBALANCE("movr"+ "MOONRIVER contract address", "holder address")
=CRYPTOBALANCE("TEZOS contract address","holder address") =CRYPTOBALANCE("SOLANA contract address","holder address")
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
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:
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.
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:
MAKERDAO — COMPOUND — DYDX — NUO— FULCRUM — AAVE 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”)
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.
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.
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
- Crypto Telegram Signals | Crypto Trading Bot
- Copy Trading | Crypto Tax Software
- Grid Trading | Crypto Hardware Wallet
- Crypto Exchange | Crypto Apps in India
- Best Crypto APIs for Developers
- Best Crypto Lending Platforms
- An ultimate guide to Leveraged Token
- Coinbase Bots | AscendEX Review | OKEx Trading Bots
- How to buy Bitcoin in India? | WazirX Review
- Crypto Trading bots | Probit Review
- CryptoHopper Alternatives | HitBTC Review
- CBET Review | KuCoin vs Coinbase
