Update 10.09.2017: Added support for Bitcoin and Euro wallet, Added display of current prices from Coinbase
Most media and some experts see cryptocurrency struggling. Some say bitcoin and blockchain are overhyped or even locate crypto-exchanges in the trough of disillusionment. As s visionary and strategist, you know this may just be the right time to invest: As we’ve cycled about two-thirds of Gartner Hype Cycle, the plateau of productivity is almost graspable, limiting volatility.
- I’ve developed a handy but simple Google Spreadsheet-Script to retrieve your current Ether balance from your Coinbase.com wallet.
- This is the first step to a truly open-source Personal Finance Management solution for an independent savings strategy.
- I’ll explain why this is a rule-breaking MVP and why Ether, via Coinbase, is a perfect way to validate this idea.
Back in 2013 I was buying Bitcoin at Mt.Gox for about 70 €/BTC. Shortly after BTC/EUR exchange rates hit 700 €/BTC, Mt.Gox went insolvent in February 2014. Loosing 60 % of my investment (and 950 % of my potential payout and still waiting for compensation), I turned my back on cryptocurrencies for a while. In the last 12 months, I gave Ether a shot; with reasonable returns. Ether, technically similar to Bitcoin, isn’t just a currency based on Blockchain, but the fuel for a new kind of digital ecosystem. So-called Smart Contracts enable individuals and businesses to set-up any kind of contract, authenticate and get it certified by a crowd of Blockchain witnesses (nodes). Examples include loan and rental agreements or corporate bonds. With Blockchain’s built-in peer supervision, central authorities and even notaries may become redundant. This yields a huge opportunity for developers, startup-companies and investors.
First things first: In order to participate in this venture, you need Ether. There are two options on how to get it: (1) buy Ether coins at an exchange using EUR or USD, or (2) harvest Ether coins on your own. Option 2 is commonly known as “mining” and some high-level instructions how to do so are covered here. Your mining efforts will only yield a positive ROI if you have access to cheap electricity, good knowledge about computer hardware and GPUs, as well as some patience. As the efficiency of mining is greatly influenced by some tech-skills, you may want to opt-in to buying Ether at an exchange like Coinbase.
Part I: Register for your Coinbase account and make your first transaction
If you already got an account, skip this part and move on to part II.
- Go to Coinbase.com and create a new account. If you register by using the following link, you will get $10 (€8) of free bitcoin when you buy or sell at least $100 (€85) of digital currency. I will be gifted $10 (€8) from Coinbase, too (referral program): https://www.coinbase.com/join/597231ac1b11c300889294f2
If you don’t already use a password manager to generate secure passwords and store your credentials, start now by using KeePass (on your computer) or LastPass (web-based).
- Fill in your details, follow the steps and validate your account.
- Buy some Ether, e.g. worth 100 EUR.
Using a credit card for payment is the easiest and fastest way, but with high fees. You may want to use SEPA instead, but it takes a couple of business days to validate once.
- Quickly after you placed your buy order your Ether will appear in your ETH wallet.
If you noticed that the digits of screenshot 3 and 4 differ slightly, this is just because I took screenshot 3 after I already bought ETH. Spread and fees are transparent before you buy.
Great! You’ve just made your first transaction and bought Ether. Let’s now move on to part II.
Part II: Get the Google Spreadsheet-Script
I’ve developed a handy Google Spreadsheet-Script to retrieve your current Ether balance from your Coinbase wallet. In order to check your balance, you don’t have to log-in at Coinbase each time. Just open your spreadsheet. This script is very simple, though. It’s just the beginning. You could extend it or use it in another spreadsheet if you are already tracking your personal finances this way.
Depending on your tech-skills and if you like to extend or fork my script, there are two options on how to get it. In any case, you should have a Google Account, of course.
Option “User”: Clone my Google Sheet to your account
If you want to use the script right away without digging too deep into technical details, follow these instructions:
- Open my spreadsheet template https://docs.google.com/spreadsheets/d/1P_hZwC8hBe_uL0BkBdGnauvs32JGC4Atq1L39nYB3AQ/edit?usp=sharing
- Copy it to your Google Drive and name it “My Personal Finance Manager” (or any other name).
- In your copy of the spreadsheet go to tab “Dashboard” and it should look like this:
- As you can see on the “Settings” tab, the Coinbase API credentials are still missing.
We are going to create them in part III.
Option “Coder”: Get the source code from Github
- Check Code.gs in my GitHub repository https://github.com/psysm/Coinbase-API-Retrieve-Ether-wallet-balance
- Create a new Google Script using Code.gs
Have a look at Coinbase’s API reference.
Part III: Enable Coinbase API and connect your spreadsheet
You’ll need API credentials with minimum permissions, just enough to read your balance.
- While logged-in to Coinbase.com, go to https://www.coinbase.com/settings/api and create a new API key.
- Only enable the API key for your ETH (Ether) wallet with a read permission (“wallet:accounts:read”).
- Leave the rest blank and hit “Create”
- Bravo! You’ve just created your minimum API credentials. You need them in your spreadsheet.
- Copy your API Key and API Secret to your spreadsheet.
- And voilà, here is your current balance from your Ether wallet.
The list of things this script CAN’T do is long:
- It only reads your current balance for your Ether wallet. Not Bitcoin, not Litecoin and not your native currency wallet (EUR/USD).
- No historical data is stored, it just shows the current value.
- If anything goes wrong at Coinbase, this stupid script just doesn’t work. There is no error handling.
- It doesn’t show how many seconds have passed, since the last retrieval from the API.
- … and much more!
Feedback and fork
Fork me on Github and send me any feedback or suggestions you may have.
The Big Picture: Open-Source Personal Finance Management
Keeping track of your personal finances has never been easier. There are installable software products like Quicken or FinanzManager (DE); some of them are open-source like GnuCash, then there are web-based services like Mint.com or moneymeets (DE), multi-banking services like the one from Consorsbank (DE) and even hosted multi-banking APIs like figo.io.
So, what’s all the fuss about?
Instead of using these versatile and sophisticated products and services, most people are just using Excel to keep track of their personal finances. Why? It’s the most flexible solution and you are in full control of your data.
You may have noticed that my use case above uses Google services, so technically you are not in full control of your data, but you still own it. Be honest, what would be alternatives? Having an Excel spreadsheet stored on your computer at home, that you can’t access while on-the-go? Or setting-up your own server and digging deep into software development? Eventually, in legal terms, there is a profound difference between using an all-purpose cloud service in contrast to accept the terms of a Fintech company. In the first case, you still own your data.
There are definitely better solutions. But in this early MVP stage, it’s about validating an idea as quickly as possible.
I may not have found the right words to describe my idea yet, but I hope you’ll get the point:
How could we combine the advantages of professional products mentioned above, with the flexibility of Excel, data ownership and a community of (open-source) developers, in order to build the most flexible and open, but also powerful, personal finance management platform?
Why did I start with Ether and Coinbase?
All commercial services mentioned above start from a specific point of view: With your bank account or your credit card. But the future may look profoundly different. Our kids’ first account may not be one from a bank but a Bitcoin or Ether wallet. They may even never will have a traditional bank account in their life. Anticipating that the ongoing disruption in the financial industry will intensify, I think it would be foolish to start this venture with a traditional banking mindset. So, I propose skipping digitalization of the banking sector and starting right off with the future.
Aside this visionary explanation, there is a strategic one: If this idea should grow, we will need people with the right mindset – people familiar with principles of open-source, crowd and democracy.
And a technical reason: Implementing Coinbase API was easier than implementing FinTS and works globally.
Legal Disclaimer: The information provided is for informational purposes only. It should not be considered legal or financial advice. You should consult with an attorney or other professional to determine what may be best for your individual needs. Use this script on your own risk.
8 Replies to “Cryptocurrency: Personal Finance Management with Coinbase API and Open-Source Google Spreadsheet and Script”
Many thanks for your article – managed to implement it for my account. Do you think it’s possible to use the same script to get the current BTC and ETH value data from coinbase?
Thank you for your feedback. I’ve just added support for Bitcoin and Euro wallets. I’ll look into retrieval of current BTC and ETH exchange rate right now, and will keep you posted.
>Do you think it’s possible to use the same script to get the current BTC and ETH value data from coinbase?
Added support for this as well. See https://www.koepernick.de/cryptocurrency-personal-finance-management-coinbase-api-open-source-google-spreadsheet-script-update-20170910/ for details
Hello, I’m trying to implement your code to fetch balance data from GDAX account, but I always get an error regarding “invalid signature”.
I forked you on github, added you as collaborator to my fork if you have time to have a look I would appreciate. Thank you
>fetch balance data from GDAX account, but I always get an error regarding “invalid signature”
yeah… I’ve tried GDAX API as well, but postponed this implementation initially. First, GDAX authentication works slightly different (see https://docs.gdax.com/?php#authentication), in contrast to Coinbase API (same company though). Second, compared to PHP or NodeJS, things are complicated by that fact that Google Script uses byte for base64decode and has a slightly different implementation when it comes to computeHmacSha256Signature(). See https://stackoverflow.com/questions/46105421/google-apps-script-equivalent-of-phps-hash-hmac-with-raw-binary-output for details. I have a running PHP script and I’m working on the Google Script right now…
have a look at https://github.com/psysm/Coinbase-API-Retrieve-Ether-wallet-balance/commit/d7af090abf1b551d43a84d6a9eb56c717b23ecf6#diff-b2598947b32ada69922f17eb96952e70
Does this work for you as well? Would you like to merge this to your github repo and add functions to get LTC, BTC and convert cryptocurrency balances to native currency (using getCoinbasePrice(currencyPair, buySell) )?
Comments are closed.