Get your real-time Trade Balance from Kraken in Google Sheets
This tool will be useful and fast for those who perform end of month accounting or use Google Sheets to value their cryptocurrency portfolio.
Kraken.com has already written the Google Sheet code for us. All we need to do is follow instruction from Kraken Google Script — REST API Private Endpoints.
NB before we go further: NEVER SHARE YOUR SHEETS WITH YOUR PRIVATE INFORMATION!
What you’ll need :
- Generate Kraken private API keys (3min)
- Open Google Sheet
- Add code — Ctrl C/Ctrl V — Authorize — Save (3min)
How it works:
From Kraken’s website: How to generate an API key pair?
API keys are one of the components of API authentication, they are the API equivalent of a username and password.
API keys are required to call any of the private API methods, namely the account management, trading, and funding methods. API keys are not required to use the public API methods (the market data methods) as the public methods do not need access to a Kraken account.
Creating An API Key
- Sign in to your Kraken account.
- Click on your name in the upper-right corner of the page.
- Click on “Security” and then “API”.
- Click on the “Add key” button.
Configuring An API Key
API keys have several configuration options that must be setup before an API key can be used, notably the Key Description and Key Permissions.
Key Description
The Key Description is essentially the name of the API key, and can be almost anything that you prefer, as long as the description is unique (different from the descriptions of any other API keys on the same account).
Key Permissions are very important! The less access permissions, the better the security.
For the purpose of retrieving your total balance, you will just give permission to:
- Query Funds, which is required for the account management methods that query the account balance information, such as Balance and TradeBalance.
Using an API Key
API keys consist of a public/private key pair, both of which must be provided to the API client software. The key pair can then be copied and pasted as plain text directly into the API client code.
Open Google Sheet
- Go to sheets.google.com
- Google Drive — Click New
- Google Sheets and create from scratch or from a template.
Add the code that Kraken created for Google Sheets
- Open the script editor via the Tools -> Script editor menu.
2. Delete the default code that is shown (Select All then Delete/Backspace, for example).
3. Copy/Paste the Google Script API code (link) into the script editor.
4. Update the example API key to use an API key from your Kraken account.
5. Save the Google Script Code via the File -> Save menu or the
6. Authorize Google Services -> Apps Script requires user authorization to access private data from built-in Google services or advanced Google services.
Use Kraken’s built-in formulas
=KAPI_Private(“TEST”, “TradeBalance”, “asset=USD”)
The KAPI_Private() function returns the original JSON response from the API, such as the following for the above Balance endpoint example:
- {“error”:[],”result”:{“eb”:”20.7858",”tb”:”19.4652",”m”:”0.0000",”n”:”0.0000",”c”:”0.0000",”v”:”0.0000",”e”:”19.4652",”mf”:”17.4652"}}
eb: Evaluated Balance in real-time, tb: Last Trade Balance, e:Equity, mf:Free Margin.
Try it out, but instead of putting “TEST”, insert your description key name.
In order to get your total real-time balance, you need to retrieve the eb value from the JSON. You will need to add this lines of code in the KAPI_Private function as follows:
if (endpoint == “TradeBalance”){api_data=JSON.parse(api_data).result.eb}
Insert the line after line 19.
This line will parse the JSON format, into the evaluated balance (”result”:{“eb”:”20.7858"}) and you will get valuation the sheets as shown on the following example cell O2.
=KAPI_Private(“……”, “TradeBalance”, “asset=USD”)
Voilà! Now you should get your real-time Kraken balance. If you refresh the cell, you will get the latest amount. Cells usually refresh once every hour or so in Google Sheet.
More Tools
If you are interested in more cryptocurrency portfolio valuation tools, you might be interested by the following medium.
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.
The opinions expressed are for general informational purposes only and are not intended to provide specific advice or recommendations for any individual or on any specific security or investment product. It is only intended to provide education about the financial industry.
Thank you so much for investing your time in reading this article.
Take care of yourself & your family in these challenging times! Happy New Year 2021!
Join Coinmonks Telegram group and learn about crypto trading and investing
Also, Read
- Learn Ethereum and Web3 development
- The Best Crypto Trading Bot
- 3Commas Review
- Pionex Review
- AAX Exchange Review | Referral Code, Trading Fee, Pros and Cons
- Deribit Review | Options, Fees, APIs and Testnet
- FTX Crypto Exchange Review
- NGRAVE ZERO review
- Bybit Exchange Review
- 3Commas vs Cryptohopper
- The Best Bitcoin Hardware wallet
- Crypto Copy Trading Platforms
- ledger nano s vs x
- Vauld Review
- The Best Crypto Tax Software
- Best Crypto Trading Platforms
- Best Crypto Lending Platforms
- Ledger Nano S vs Trezor one vs Trezor T vs Ledger Nano X
- BlockFi vs Celsius vs Hodlnaut
- Bitsgap review — A Crypto Trading Bot That Makes Easy Money
- Quadency Review- A Crypto Trading Bot Made For Professionals
- CoinTracking Review
- YouHodler Review
- Ellipal Titan Review
- SecuX Stone Review
- BlockFi Review | Earn up to 8.6% interests on your Crypto
- Coinrule review
- Best Blockchain Analysis Tools
- Crypto arbitrage guide: How to make money as a beginner
- Best Crypto Charting Tool
- What are the best books to learn about Bitcoin?
