Excel Portfolio - How To Get Live Prices & Stats
![](https://steemitimages.com/DQmNfLDsTLoBiDzkcyN5eeSbom5HWif25dkAAa22yDMgwsx/image.png)
Purpose
If you're a masochist like me, you like to use Excel to manage your portfolio. The amount of customization and control is simply unparalleled. I can see nice graphs and visual aids that show me which of my altcoin investments are growing most, track my fiat conversions, and so much more, but typing in all those prices, market caps, and stats is a pain. Here I'm going to show you how to pull live data from two different websites so that you too can make the Excel spreadsheet of your dreams.
Ticker Options
I'm going to go over two different sites that provide formatted data "tickers" that are meant for exactly this purpose.
![](https://steemitimages.com/640x0/https://steemitimages.com/DQmTwfcyjkX1McA5sCv565iNVCcjFmwtYuEkSwoABRSvsi1/image.png)
Using Coinmarketcap.com API
You've probably used coinmarketcap before, but maybe you didn't know that they have a very comprehensive API you can use for tracking live stats on all their coins.
https://api.coinmarketcap.com/v1/ticker/
Below are some examples of the type of optional parameters you can use to limit the amount of top coins you get and convert to a different fiat currency.
- All coins, all stats: https://api.coinmarketcap.com/v1/ticker/
- First 5 coins, all stats: https://api.coinmarketcap.com/v1/ticker/?limit=5
- First 10 coins, conversion to CNY: https://api.coinmarketcap.com/v1/ticker/?convert=CNY&limit=10
Unfortunately, pulling multiple coins means they are sorted by coin rank based on market cap. That means the order of coins changes, and we can't have that in our Excel file if we want to use simple cell references. The way around this is to add specific coins one at a time. To see a specific coin, use:
- Ethereum: https://api.coinmarketcap.com/v1/ticker/ethereum/
- Steem: https://api.coinmarketcap.com/v1/ticker/steem/
Note that we can use the "?convert=EUR" or other fiat conversion options on these:
For global stats, we use:
![](https://steemitimages.com/DQmYrNEZ9vVu2UGWMcZuKVNxfhiCbxVG8YzCETZXP7o5jS2/image.png)
Using Coincap.io API
Coincap.io is relatively new the game, but they're quickly growing to be the most popular.
Coincap does things a little differently. To see a list of all the coins, we use:
To see if the coin you want info on is supported, do a ctrl-f search for it on:
One thing we can do with Coincap is see the history of the coin. To see the history over 1 day for Bitcoin, we would use:
We can do 1, 7, 30, 90, 180, and 365 days, or just use http://www.coincap.io/history/BTC to see all history.
For individual coin data *:
*Individual coin data doesn't seem to be working, see https://github.com/CoinCapDev/CoinCap.io/issues/19 for more info.
Getting Data Into Excel
To access the API from Excel, we go to the Data tab and click the "Get Data" option. Go down to "From Other Sources" and over to "From Web".
![](https://steemitimages.com/640x0/https://steemitimages.com/DQmVYDYQCWsXyT1kz8ULXM1ymXA523pmi1dFXPQwk1DvjCT/image.png)
As I mentioned before, we are going to want to import data one coin at a time so that the relative cell index doesn't change when the coin rank based on market cap changes. There are other, more complicated, ways around this, but this is them method I use. Also, since the Coincap site is having issues with individual coins, I'm going to use Coinmarketcap.com.
Let's get some data for Ethereum. If you want a certain fiat conversion, you need to use one of the ticker URLs that had /?convert=EUR at the end:
![](https://steemitimages.com/DQmWnDbBGRVdoQhxaNsaS4Kka4TUXMtmLxu6EFwYkCLFzpp/image.png)
One of the things I like to do is rename the Query.
![](https://steemitimages.com/640x0/https://steemitimages.com/DQmdpyJ3EMESmCSZiBmPc7rrPcR18hipPZduk1mESQ8Gr69/image.png)
Now we need to hit the "To Table" convert option:
![](https://steemitimages.com/DQmdkCKZPCTTCbg7A33kPPVjQtFEwkgrge2uqvvrCt4VDvp/image.png)
Leaving these options at their defaults is good.
![](https://steemitimages.com/640x0/https://steemitimages.com/DQmdKFXZEAPs2Z7FydYsC8Nx7RMVetyWBRZo7adcWscuVYw/image.png)
I highly recommend renaming the entry to the name of the coin, underlined in red. Then we need to click the button with two outward arrows, squared in blue:
![](https://steemitimages.com/DQmdKbLEMJWiZSa5uAzQCEmB6nKiHasBvHzKbUUeeAogDD4/image.png)
Here we can check off which of the stats we actually want in our sheet. I like to create a separate page in my Excel file where I put tickers for all the coins I'm tracking, so import everything here since it's not going to get in the way.
![](https://steemitimages.com/640x0/https://steemitimages.com/DQmRn4wGghbXknnwJV2t9Yp9fSbDJFbqcvHBFybWuTM9NhX/image.png)
Now just hit "Close & Load" and your live data query will be put into a new sheet.
![](https://steemitimages.com/DQmT8pJXYGLmQ5Drrha8KoEjUBCmLyB1Hb4A8XxgZDuk93W/image.png)
From here, I go to the Design Tab and select a more aesthetically pleasing style.
![](https://steemitimages.com/640x0/https://steemitimages.com/DQmS8XHWPiokpNtRR8u6KXmETxRSXrXpV6gwXaZxVP5QcpJ/image.png)
Once you're done moving your data where you want it and styling it out, we need to click in the query field and hit ctrl-a to select the entire thing. On the Design Tab, select the Properties option.
![](https://steemitimages.com/DQmVYDDAKuBk34QZwMLtYcePqqypt7Qu9py4u2jrAJxpMND/image.png)
Click the "Query Properties" button
![](https://steemitimages.com/640x0/https://steemitimages.com/DQmYzKA98i9PYqNJ4Du7XcYiJd9xiU3j8H2JycxRwkdQGJ7/image.png)
Here you can check off "Refresh every" and "Refresh data when opening the file" options. The fastest you can refresh your data is every 1 minute.
![](https://steemitimages.com/DQmcUN5dyXG6Mxc6gBrCVFvkGpMuFRz44EgmyJ45DKjMwHS/image.png)
Now hit OK a couple times and you're all set!
Just repeat this process for each coin you want to track, I know - it can take a while, but then they sky is the limit for what kind of analytics you create to help you keep your portfolio going.
Wow, that's huge, thanks!
I can help you with formulas that pull the right values regardless of the order and reorder of stats. I spent days creating what I think is a great tool. I started with the idea and directions utilized in your post. Thank you!
I might add. I created a ticker page. All data is pulled from one place.
Matter of fact, I'll create a detailed post after the new year.
Yeah, mixing OFFSET and MATCH functions will do this pretty well. It was just more than I wanted to get into with this post originally.
So much information in one place! This was exactly what I was looking for. Thanks for sharing!
Good peice my friend! Is there a way to get 30-60-90 day price change %, 6month Hi & Low, from an API- on any coin ?
This is amazing! Thanks for the great post!
Does anyone know how to omit the headers from the import?
Click inside your query, then hit ctrl+a to select all of it. Go to the Design tab, and uncheck Header Row:
Amazing stuff, truly. I can appreciate the "Excel magic" you've created here. Thanks for the info.
@tomshwom, thanks for this article! I just joined steemit and this is my first comment. My whole investment method is built around a customized excel spreadsheet and the thinkorswim RTD functions, but they were no help when I decided to dip my toe in cryptos. You da man in my book, dude!
@tomshwom, when I started to implement your tips my screens did not match your screenshots. After a bit of fumbling I figured out that I did not have the Excel Power Query addin. Did that and everything works like a charm. I'm using the CoinMarketCap API for now but I would like to find one that has the daily OHLC info because that is not in the CMC data and I'm too lazy to write something to calculate it. You rock!
With Excel 2010 I had none of the options shown above until I download and install the Power Query Add-In for Excel 2010.
For Excel 2016, 2013, 2010, 2007 see the following...
Connect to a web page (Power Query) - Office Support
https://support.office.com/en-us/article/connect-to-a-web-page-power-query-b2725d67-c9e8-43e6-a590-c0a175bd64d8
Thanks for the How To details!
Dude..... Amazing post yet again. You're an asset to this community. Keep kicking ass.