Bitcoin Forum
January 24, 2019, 09:46:18 AM *
News: Latest Bitcoin Core release: 0.17.1 [Torrent]
 
   Home   Help Search Login Register More  
Pages: [1]
  Print  
Author Topic: Make Your Own Custom Excel Coin Tracker using Coinmarket Cap APIs  (Read 185 times)
Castlereagh
Member
**
Offline Offline

Activity: 154
Merit: 21


View Profile
February 14, 2018, 06:55:40 PM
Last edit: February 15, 2018, 05:10:40 PM by Castlereagh
Merited by Wusolini (5), eternalgloom (3), OgNasty (1), LoyceV (1), mpufatzis (1), MagicSmoker (1), Coolcryptovator (1)
 #1

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!

1548323178
Hero Member
*
Offline Offline

Posts: 1548323178

View Profile Personal Message (Offline)

Ignore
1548323178
Reply with quote  #2

1548323178
Report to moderator
1548323178
Hero Member
*
Offline Offline

Posts: 1548323178

View Profile Personal Message (Offline)

Ignore
1548323178
Reply with quote  #2

1548323178
Report to moderator
1548323178
Hero Member
*
Offline Offline

Posts: 1548323178

View Profile Personal Message (Offline)

Ignore
1548323178
Reply with quote  #2

1548323178
Report to moderator
Advertised sites are not endorsed by the Bitcoin Forum. They may be unsafe, untrustworthy, or illegal in your jurisdiction. Advertise here.
1548323178
Hero Member
*
Offline Offline

Posts: 1548323178

View Profile Personal Message (Offline)

Ignore
1548323178
Reply with quote  #2

1548323178
Report to moderator
ingbu
Newbie
*
Offline Offline

Activity: 18
Merit: 0


View Profile
February 14, 2018, 07:07:04 PM
 #2

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.
Castlereagh
Member
**
Offline Offline

Activity: 154
Merit: 21


View Profile
February 14, 2018, 08:10:04 PM
 #3

No problem at all - just glad it was useful!

neatworld
Member
**
Offline Offline

Activity: 260
Merit: 12


View Profile
February 15, 2018, 01:23:02 PM
 #4

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! Grin
Castlereagh
Member
**
Offline Offline

Activity: 154
Merit: 21


View Profile
February 15, 2018, 05:12:44 PM
 #5

Thanks
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! Grin

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! Grin

MagicSmoker
Full Member
***
Offline Offline

Activity: 364
Merit: 172



View Profile
February 15, 2018, 06:49:11 PM
 #6

GenX dinosaur checking in; I did have a spendable merit so I gave it to you. Really excellent work.
Castlereagh
Member
**
Offline Offline

Activity: 154
Merit: 21


View Profile
February 15, 2018, 08:05:01 PM
 #7

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!

eternalgloom
Legendary
*
Offline Offline

Activity: 1470
Merit: 1140


Crypto-Games.net: Multiple coins, multiple games


View Profile WWW
March 02, 2018, 02:40:14 PM
 #8

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!


▄▄▄████████▄▄▄
▄██████████████████▄
▄██████████████████████▄
██████████████████████████
████████████████████████████
██████████████████████████████
██████████████████████████████
██████████████████████████████
██████████████████████████████
██████████████████████████████
████████████████████████████
██████████████████████████
▀██████████████████████▀
▀██████████████████▀
▀▀▀████████▀▀▀
   ███████
██████████
██████████
██████████
██████████
██████████
██████████
██████████
██████████
██████████
██████████
██████████
███████
BTC  ◉PLAY  ◉XMR  ◉DOGE  ◉BCH  ◉STRAT  ◉ETH  ◉GAS  ◉LTC  ◉DASH  ◉PPC
     ▄▄██████████████▄▄
  ▄██████████████████████▄        █████
▄██████████████████████████▄      █████
████ ▄▄▄▄▄ ▄▄▄▄▄▄ ▄▄▄▄▄ ████     ▄██▀
████ █████ ██████ █████ ████    ▄██▀
████ █████ ██████ █████ ████    ██▀
████ █████ ██████ █████ ████    ██
████ ▀▀▀▀▀ ▀▀▀▀▀▀ ▀▀▀▀▀ ████ ▄██████▄
████████████████████████████ ████████
███████▀            ▀███████ ▀██████▀
█████▀                ▀█████
▀██████████████████████████▀
  ▀▀████████████████████▀▀ 
✔️DICE           
✔️BLACKJACK
✔️PLINKO
✔️VIDEO POKER
✔️ROULETTE     
✔️LOTTO
Castlereagh
Member
**
Offline Offline

Activity: 154
Merit: 21


View Profile
March 07, 2018, 01:57:50 PM
 #9

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!

Haha, my pleasure Grin Happy stealth procrastinating!

niccolo_21
Newbie
*
Offline Offline

Activity: 3
Merit: 0


View Profile
April 29, 2018, 10:17:57 AM
 #10

thanks very much, will try it out today
Pages: [1]
  Print  
 
Jump to:  

Bitcointalk.org is not available or authorized for sale. Do not believe any fake listings.
Sponsored by , a Bitcoin-accepting VPN.
Powered by MySQL Powered by PHP Powered by SMF 1.1.19 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!