keeping track of your investments / how to get coinmarketcap data into Excel / step by step guide
Hi Steemians!
Judging from the content of many posts here, probably a lot of you are crypto investors. So am I and I have always found it a bit hard to keep track of where the portfolio stands, as I have multiple wallets and hold different currencies. To solve that little problem I have created an Excel sheet about my holdings and feed in data through the coinmarketcap API, which makes it very easy to get an update within seconds, even without expert excel or coding skills.
All I need to do is open the Excel file, click on “refresh data” and voilà, I know whether I am up or down. Keeping track of your orders also makes a lot of sense, especially in Germany, because after holding the tokens for a year, all gains are tax free!
I’ll show you how to easily do this yourself with a step by step guide, OR upvote and comment with your email address and I will forward the file to you :-)
Step by Step:
We are first going to get the coinmarketcap data into our Excel sheet and then add the currencies we hold and their amounts.
Open Excel and go to “file” (top left corner) and “account” to view details about your Excel version: you want to know if you are using a 32 or 64 bit version (note that I am German and use a German version):
Go to https://www.microsoft.com/en-us/download/details.aspx?id=39379 and download the matching version of „Microsoft Power Query for Excel”. This is an Excel add-in to easily feed in external data.
Close Excel and install Power Query.
Restart Excel and you should see the Power Query tab in the ribbon.
Click on that Power Query tab and then click on “From Web” on the left-hand side to open the box to put in a URL.
Put in the URL of the Coinmarketcap API: https://api.coinmarketcap.com/v1/ticker/?convert=EUR
My local currency is EUR, so add the ?convert=EUR parameter. If you use USD only, you can leave it out, or if you use Rubel for instance, replace the EUR by RUB, etc.Click OK and a new window pops up. You see a long list of records. Just right-click on the word “List” and click on “To Table”. A new box opens, no changes needed, and we just click on OK.
You see a similar list of records. Left-click on those double-arrows to select which data you want from the Coinmarketcap API. You can leave everything checked, or uncheck the data you don’t want or need. What you definitely need is the Symbol and the price_usd (and/or price_eur, depending on whether you want to have it in a second currency).
For the sake of this exercise I just use Symbol, price_usd and price_eur.
Click OK.You now see a table about the chosen columns:
Click on “Close & Load” on the top left corner to get this data into a new worksheet in your excel spreadsheet:
Congratulations! You have now fed in data from an external API – you little Geek you! :))
Now, if you’re American and use only USD, and your Excel is using a decimal point and not a comma (in Europe, we use the comma), you can skip the next couple of steps and continue with step 16. If your Excel is using a comma for decimals, follow the next steps.
As you can see, the data returned from coinbase has prices with a decimal point. We want a comma. Luckily excel has a nice formula for that: SUBSITUTE (or WECHSELN in German).
Go to the worksheet with the coinbase data in it. Add another column for each currency you requested. I requested USD and EUR, so I add two new columns.
Enter the formula like in the screenshot, in English this would be =SUBSTITUTE(B2;”.”;”,”)*1 - the *1 is used to tell Excel that it is a number and not a word.
Do the same for the other currency, make sure to use C2 and not B2. With proper formatting the formula should already be used for all lines. If not, highlight the two and navigate your mouse to the bottom right corner until the white cross becomes a smaller black cross. Double click to automatically copy the formula to all lines below.
To bring the data together and only display the data for the currencies you are holding, you now create a simple overview of your tokens on another worksheet.
It can look like this. Make sure in Column A you use the official Symbol name, and enter the amount of the tokens you hold.
To add the prices and the totals (amount multiplied by price), we use formula.
To get the current prices from the data feed, we use a VLOOKUP (SVERWEIS in German).
VLOOKUP is looking for an expression (in this case “btc”) in a defined table/matrix and returning an allocated value in a named column. So we look for “btc” in the table we created using the coinmarketcap data, and want to show the corresponding USD price.
Formula explained:
=SVERWEIS(A2;Tabelle2!A:E;4;0)
We look for the value in A2 (=btc), and we look for it in Table 2 column A to E.
The USD Price is in this table twice, once with decimal point and once with decimal comma. I want the value with a comma because I use a European / German version of Excel. This is in Column D, which is the 4th column counting from column A (A is 1, D is 4, etc).
We don’t need anything else, so we end the formula with ;0 and close the parenthesis.
To get the EUR price, repeat the formula, but look for the value in column E = 5: =SVERWEIS(A2;Tabelle2!A:E;5;0)
To calculate the total, just multiply the amount of your tokens held with the price.
To refresh the data from Coinmarketcap, go to the "Data" tab and click "refresh all". It then takes only a few seconds to get fresh data.
All done :)
There are other APIs provided by all exchanges like Poloniex or Bittrex, from where you could retrieve all your orders and holdings too. It pretty much works the same way as what we just did. If you’re interested, just let me know, I can do a manual on that as well.
Thanks for reading, if you like it or have questions (sometimes the VLOOKUP can be tricky :) ), resteem, upvote, comment,…
Enjoy the ride!
Ruben