Topic: Make Your Own Custom Excel Coin Tracker using Coinmarket Cap APIs (Read 318 times)
thanks very much, will try it out today
March 07, 2018, 08:57:50 AM
Dang, that really something nifty you created there, I'm impressed.
Now I can check coin prices in stealth when I'm working in Excel, pretty handy actually.
Thanks for going through the trouble of figuring this out!
Now I can check coin prices in stealth when I'm working in Excel, pretty handy actually.
Thanks for going through the trouble of figuring this out!
Haha, my pleasure
Happy stealth procrastinating! 
March 02, 2018, 09:40:14 AM
Dang, that really something nifty you created there, I'm impressed.
Now I can check coin prices in stealth when I'm working in Excel, pretty handy actually.
Thanks for going through the trouble of figuring this out!
Now I can check coin prices in stealth when I'm working in Excel, pretty handy actually.
Thanks for going through the trouble of figuring this out!
February 15, 2018, 03:05:01 PM
Haha - dinosaurs unite! (To be fair, pretty much anyone over 25 qualifies as a dinosaur in crypto world: so there's a lot of us)
Many thanks MagicSmoker, I'm glad it was helpful!
Many thanks MagicSmoker, I'm glad it was helpful!
February 15, 2018, 01:49:11 PM
GenX dinosaur checking in; I did have a spendable merit so I gave it to you. Really excellent work.
February 15, 2018, 12:12:44 PM
Thanks
Thanks Neatworld - no problem! I know it's not the most straightforward way of doing things compared to a phone App that does it all automatically... but you get an unparallelled level of control and customisation, I think. Or maybe I'm just making excuses. I love this stuff!
Thank you for this fantastic info. I too am a bit of a dinosaur (I love customising Excel spreadsheets!) so this was a fun read. I would have loved to give you some merit for it but I have none left. So the best I can do is comment so I can bump up the visibility of your thread.
All the best to you!
All the best to you!
Thanks Neatworld - no problem! I know it's not the most straightforward way of doing things compared to a phone App that does it all automatically... but you get an unparallelled level of control and customisation, I think. Or maybe I'm just making excuses. I love this stuff!
February 15, 2018, 08:23:02 AM
Thank you for this fantastic info. I too am a bit of a dinosaur (I love customising Excel spreadsheets!) so this was a fun read. I would have loved to give you some merit for it but I have none left. So the best I can do is comment so I can bump up the visibility of your thread.
All the best to you!
All the best to you!
February 14, 2018, 03:10:04 PM
No problem at all - just glad it was useful!
February 14, 2018, 02:07:04 PM
thank you very much for this manual! you did a good work. unfortunatelly I actually have no merits to spend. but I will keep you in mind.
February 14, 2018, 01:55:40 PM
Alternative title: how to be a dinosaur and resist change
But 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/bitcoin
b. 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/ethereum
c. 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!
But 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/bitcoin
b. 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/ethereum
c. 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!
Jump to: