Crypto ledger in Google sheets with auto updating prices from CoinBase

Simple google sheet to keep track of your profits and coins across exchanges and wallets. Auto update prices from CoinMarketCap, giving you a quick overview to your entire portfolio and how you have progressed across all your coins.

Source: Unsplashed

Most people in crypto have a mixture of online exchanges and wallets. Just how do you keep track of all of these and separate your portfolios? After trying a few options, Google sheets came out the winner for me.

Keeping in mind that below is for those who do not do day trading and don’t hold more than 20 coins.

To start, first copy the template in google drive to make your own sheet.

This article will be a 5 step process:

1. Get a coinmarketcap api key
2. Install API connector and setup coinmarketcap
3. Setup your targets for the quarter
4. Deposit into your exchange or wallet
5. Buy coins with your USDT
6. Sell coins with your USDT

Step 1: Link coinbase to the sheet for auto update of prices

Your first step is to get an API key from coinbase. Don’t worry, its free. 😀

1. Head over to CoinMarketCap and sign up for the free plan.
2. Now go to the settings page and create your API key. You can click on copy key and keep it for later.

Step 2: Add-on extension for API Connector

In sheets, go to Extension and click on ‘Get add-ons’

Now search for “API Connector” and add the extension.

Next, you can manage connections:

Now, click on requests and and click on ‘Add New’

So, now you can add the API connector as follows:

1. Add the API URL and add any coins you are interested in.

https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=BTC,ETH,FTM,VET,THETA,ZIL,COTI,AR,SOL,MATIC,GRT,DOT,1INCH,ADA,LUNA,AVAX,TLM,RNDR,MANA,SRM,RIN,SBR,FTM,MOVR,LINK,AKT,ALGO

2. Add a header for the API key and paste in your API key from step 1 into the Value field on the right.

X-CMC_PRO_API_KEY

3. Add a header called Accept with a value to accept JSON

Accept         application/json

4. Set the Destinatin sheet to CoinMarketCap

5. Set the cell to ‘A1’

6. Give it a name of COINMARKETCAP

7. Now click on output options and set JMESPath to

data.values(@)

Now, you can hit the ‘Run’ button above, and it will populate the CoinMarketCap sheet as below.

NB. For this to work, we need the price to be in AW. If the price is not in AW, then follow the instructions on the sheet !Targets:E6.

Step 3: Setup your quarter targets

Head over to the targets tab and enter your targets for the crypto for the quarter, so you can track your progress as you go.

Step 4: depositing money into your exchange

As you transfer money into your exchange or wallet, either via credit card or deposit, you can enter it into the ledger as below using the label “DEPOSIT”.

The result is you will see your investment field as well as in the exchange pie chart.

Step 5: Buy coins

Once you have purchased coins on your exchange, you can enter them in your ledger manually as follows.

1. First, enter your purchase in the ledger below as in
2. Second, add the coin under column A
3. Third, note your USDT is lower, while all other cell’s from B4 to F4 have been calculated for you.

NB. Only change cell’s in grey. Any other color means they are a calculated field

Step 6: Sell coins

You can now sell coins just by entering a negative quantity as below, with the selling price. You will note that the spreadsheet will keep track of the profitability of the coins as you buy and sell amongst the exchanges and over time.

Conclusion

Keeping track of your profits over time is not hard in google sheets. Just be sure to keep your ledger up to date 🤪

Ahh, and before I forget, whenever you are looking at the sheet, use ‘refresh all now’ below to update the prices from CoinMarketCap.