Alternative title: how to be a dinosaur and resist changeBut seriously, I'm hoping this might be useful to someone who's a bit old-school like myself. I know there's Blockfolio and all that jazz, but I'm a bit weird in that I don't like using phone apps that much (get way too addicted) - so I like to manage my portfolio and handle my crypto business on a dedicated laptop. I also manage a portfolio for my dad and my father in law too, so I needed a solution where I could track totals for a number of different people - and I couldn't find an app that has this feature out of the box. It also opens up the possibilities of making all kinds of cool custom graphs etc. if you have the know-how.
Spiel over: so you want to be able to import live crypto price data into an excel spreadsheet? It's actually pretty simple. (Also bear with the post, the images take a while to load!)
1. Open a new spreadsheet and head the the Data tab on the ribbon, then click "From Web"
2. A dialogue box will then open as follows:
a. Now you're going to need to type in the link for the coinmarket cap API. For bitcoin, it is:
https://api.coinmarketcap.com/v1/ticker/bitcoinb. For alternative coins, you are going to need to find out the long URL name from coinmarketcap - simply look up your chosen coin on CMC and look at the URL - the name you want is the entry following /currencies/, for example for ETH:
https://coinmarketcap.com/currencies/ethereumc. A step for international users - if you'd like the ticker to include a conversion into your local currency, you can append the following code to the end of the API link: ?convert=[ENTER CURRENCY CODE HERE]. For example, I'm English, so my full code would read: "
https://api.coinmarketcap.com/v1/ticker/bitcoin/?convert=GBP"
3. The Query Tracker window will then open:
a. This can be a bit fiddly, but the first thing to do is give your query a title in the Query Settings: Property bar on the right (just for your convenience), e.g. just type in "Bitcoin" or whatever
b. Click on 'Record' and the table will populate:
c. Then go to 'File'>'Close and Load' in the ribbon, and voila, you'll have your shiny new table (complete with useful additional fields like "percent_change_24h", "rank" etc.)
4. Finally you're going to want to tweak the connection settings so the table auto updates and gives you dynamic pricing data.
a) Highlight the table, then click on the "Table Tools: Design" tab - then click the down arrow underneath "Refresh" and then click "Connection Properties" in the cascade menu
b) I then personally set it to "Refresh every 1 minute" and "Refresh data when opening the file"
c) OK, and ... that's it!
You now have a dynamic price ticker in Excel, and can start charting up your portfolio, tracking % gains etc. - your imagination and Excel skills are the only limit really. Enjoy!
