Tuesday, December 6, 2022

Track Your Cryptocurrency Portfolio Automatically! [Live Price Data]


In this video I show you how to set up a FREE automated cryptocurrency portfolio tracker using Python Scripts, the google sheets API, the Coinbase Pro API, the Gemini API, and AWS Lambda Functions and EventBridge Automations.

This is the most robust spreadsheet I’ve seen on the internet for tracking your cryptocurrency purchases, cost basis, and profit & loss. Not only because it gives you live price data, but also because of how it automatically pulls in your transaction data every time you buy or sell cryptocurrency on Coinbase Pro or Gemini. This is by far the best way I’ve seen to track profit from Coinbase Pro and Gemini.

Additionally, the sheet has the ability to be built out even further in the future to provide more in depth analytics on the sheets side and to include more exchanges going forward (like Kraken, Binance, OKCoin, etc).

♊️ Sign Up for Gemini (the only exchange I use to buy Bitcoin) and get $10 of BTC when you Trade $100:

🪙 Sign up for Coinbase + Coinbase Pro (the biggest US crypto exchange) and get $10 when you Trade $100 :

📊 Crypto Sheet Template:

☁️ Google Cloud Console: console.cloud.google.com

📓 Notion Code:

🐦 Follow me on Twitter:

📧 Sign up for Email List (bottom of blog homepage):

♊️ Symbols and minimums Gemini:

🔀 Symbols from Coinbase Pro (under layer.zip):

📅 Schedule 1 on 1 Consulting:

Intro 0:00
Free Spreadsheet 1:30
Set up Google Sheets API 2:04
Copy Code From Notion 4:52
Set Up Layer.zip 5:34
Set Up Lambda Function 6:30
Connect Gemini API 7:58
Connect Coinbase Pro API 8:58
Connect Script to Spreadsheet 10:04
How the Script Works 11:31
Testing the Script 12:53
Proof of Concept + Limitations 14:40
Automation with EventBridge 18:57
How the Spreadsheet Works 20:25
Adding New Coins to the Spreadsheet 22:36
Get Alerts When Sheet is Updated: 23:56



Source link

22 comments

  • How are you guys currently tracking your crypto purchases? Is this a better solution?

  • I did everything in the video and the script gives me the following response:

    Response
    {
    "errorMessage": "string indices must be integers",
    "errorType": "TypeError",
    "stackTrace": [
    " File "/var/task/lambda_function.py", line 103, in lambda_handlern populateCBPro(_authenticateSpreadsheet(), symbol)n",
    " File "/var/task/lambda_function.py", line 95, in populateCBPron print(f"Transaction Trade ID: {transaction['trade_id']}")n"
    ]
    }

  • does this work for binance as well?

  • Thank you so much for this and your other videos. I’ve been wanting to do this for so long but never thought I could do it. With your walkthrough I’ve finally achieved it.

  • Great system design Rhett! Thanks for putting this out here. I have the system up and running and I have been going through the spreadsheet to see how things are being done. I have only found one thing that I don't fully understand. The spreadsheet General Ledger formula for the Cost Basis of the remaining coins held of each coin type is: ((Audit Buy "Sell Side Amount" + Audit Buy "Fee") – (Audit Sell "Sell Side Amount))/ General Ledger "Total Amount"). The problem that I notice is that the values in the Audit "Sell Side Amount" column already have the Fees included. I verified this for downloaded buys on both Coinbase and Gemini against their transactions and fill reports . Unless there is something I don't understand about Cost Basis (a distinct possibility), this formula seems to yield an artificially high Cost Basis.

  • Great tool! Some added functionality that would be awesome would be crypto received (eg mining rewards) and crypto transferred from regular coinbase, as the current implimentation only sees crypto that was bought.

  • One small understanding gap – What are the "Constants" on the Prices tab? Why are these lookups in GoogleFinance, while the main list of lookups pull from TradingView. Last q: The TradingView value for ETHUSD sometimes doesn't work, and I can't figure out why – others work fine. Thanks. This tracker spreadsheet is A+, thank you, again.

  • Lambda Calrissian baby, dope video

  • How are you liking the ultra wide screen monitor? I got like 3 diff monitors was thinking of getting the one you have

  • Rhett – I sat down and got this working. This is exactly what I needed to collect and analyze all my transactions. I cannot thank you enough for sharing your expertise so generously. As a small gesture of appreciation here are my observations and questions:

    1/ First, I’ve never done anything with GCP before and your tutorial had connect-the-dots simplicity. The price import function alone is extremely useful – that could be its own video.

    2/ My transaction history includes some buys in Coinbase (regular, not Pro) which did not come in via API. I manually inserted them into rows in the right place chronologically. For each such row I made up a Transaction ID by simply adding “1” to the prior row’s Transaction ID. Didn’t seem to break anything, let me know if there are any gotchas with this approach. It’s extremely valuable to have a lifetime history in one place.

    3/ General Ledger tab / column F = Cost Basis: I can confirm that the Cost Basis result is accurate based on the manual spreadsheets I had been using. However, I don’t quite understand the formula you’ve used. Not sure what sumifs() does.

    4/ So that json file contains everything AWS needs to insert data into the Google Sheet via API using the service account email. This is so useful and powerful, I could see a lot more tutorials on this alone.

    5/ Some people will want O365/Excel, or even a spreadsheet within AWS Workspaces (if there is such a thing, just to keep on one cloud). Ideas for future.

    6/ Just checking – is this completely on the free tier of GCP?

    7/ Is the commission fee field (Audit File/column I) being used anywhere else in the workbook, or is it just handy to have for analysis?

    8/ If I don’t use Coinbase Pro anymore, can I just comment out lines 102, 103 of the lamda script, or more? No sense in routinely making API calls, for no new data.

    Again, this is brilliant. I hope your talent never gets gobbled up by some big bank. It is rare for anyone to admit this, but I am underwater with crypto right now. With the tools you’ve provided I know precisely how much better off I am a result of DCA, than had I accumulated all at once.

  • @Rhett Reisman great video. I used BlockFi a lot to buy/sell and then transfer to Celsius/Hodlnaut for the rewards. Any chance of integrating them in this script and also capture the rewards? Thanks

  • Is there a way to set a start date for the spreadsheet so it does not import any transactions prior to it?

  • Very creative productivity tool Rhett, thanks for sharing. WRT to tracking P/L, the spreadsheet needs to factor in which accounting method to use in order to calculate the correct P/L to use on a tax return. For example, the IRS assumes FIFO unless you choose an alternative method (HIFO, LIFO, Avg Cos).

  • Just wanted to comment to thank Rhett for his help. This takes a little bit of time initially, but is well worth the effort. Automation is addicting and a no brainer in my opinion.

  • Do you have one for binance? I don’t see why anyone would want to use Coinbase over binance with higher fees

  • Thanks for this – Keep receiving the error "list index out of range" when it gets to AVAX-USD. Any ideas?

  • Watched this once, looks like "Best of Rhett". One thing I'm not fully understanding – What prevents the script from pulling down duplicate entries, like if you start/stop and restart the script? I'm reserving a Saturday morning to get this working. Rhett, you are giving the Bitcoin community, for free, the kind of tools that wealth managers and hedge funds pay millions to develop. Keep rockin', and please pace yourself so you don't burn out.

  • you sir are going places

  • Love the detail! I still automatically track my Gemini purchases from the video you did on that last year. But ended up creating a manual spreadsheet this year so I can include Strike purchases. And I just made my first Bisq purchase over the weekend, so plan to use that more often too!! Nice flex on the Eth average lol…

  • Such a sadness I am not on those … :(((

  • Such an amazing thing ! Bravo!

Leave a Reply

Your email address will not be published.