Bitcoin Forum
August 22, 2019, 12:38:26 PM *
News: Latest Bitcoin Core release: 0.18.0 [Torrent] (New!)
 
   Home   Help Search Login Register More  
Pages: [1]
  Print  
Author Topic: I've created an Excel Crypto Portfolio Tracker !  (Read 44 times)
grazeppelin
Jr. Member
*
Offline Offline

Activity: 34
Merit: 3


View Profile
May 06, 2018, 05:44:55 PM
 #1

I created this sample portfolio tracker in Excel that draws live data from the CoinMarketCap API and refreshes on demand. If you have holdings across various exchanges (XRB at Bitgrail, BTC at GDAX, REQ at Binance...etc) this allows you to consolidate your entire portfolio, and also to value it more accurately in Satoshis and US dollars. Its also much quicker than logging into a site and allows for the full capability of Excel to analyze, you can run all the functions, graphs...etc that you want.

One of the things that the exchanges generally get terribly wrong is portfolio valuation and tracking. In addition to often not showing you the dollar price per coin, they also show the spot price at their exchange rather than the average across all exchanges. In many altcoins there can be quite a large spread in price between your exchange and the market average, and it generally always tends to move towards the average as arbitrage removes the difference. This is why its often better to value your portfolio using the prices on CoinMarketCap, which derives prices by "taking the volume weighted average of all prices reported at each market."

In my example file, I've only added the price feed for a few popular coins, but you can get live data for any coin on CMP by doing this:

    Go to http://coinmarketcap.com and select the coin you are interested in.

    Click the Tools tab and copy paste the link under API Tracker. For example, lets use DASH. The API tracker link can be seen on the tools page and is: https://api.coinmarketcap.com/v1/ticker/dash/

    Go into the Excel spreadsheet and click the Data tab. Select New Query -> From Other Sources -> From Web, here is a screenshot. Copy paste the API link into the box, click OK.

Note: If your version of Excel doesn't have Query Editor installed by default, you can get it from the Microsoft site.

    This will load the Query Editor like in this screenshot. Simply click on the Record field in the table and it should expand to look like this. Click on Convert Into Table on top.

    This will make a nice table for you to import into a new spreadsheet, you can name it by changing the Name field on the right side.

    Click save to import and this will make a new sheet with the name entered in the Name field. The data will automatically refresh every time you open the file. You can also manually refresh at any time by clicking Refresh All under the Data tab.

You can also make a quick macro to Refresh All and assign it to a button to add to the spreadsheet to make it even faster.

Having it all in Excel makes it easy to chart and analyze now.

I can also post the full excel file if someone is interested.

Happy investing Smiley

*edit: Here is the Excel file

https://drive.google.com/file/d/1Gj2c_JkiBywfJ__mWHHsF1zOm8FEsOI3/view?usp=sharing

Password: crypto123
1566477506
Hero Member
*
Offline Offline

Posts: 1566477506

View Profile Personal Message (Offline)

Ignore
1566477506
Reply with quote  #2

1566477506
Report to moderator
1566477506
Hero Member
*
Offline Offline

Posts: 1566477506

View Profile Personal Message (Offline)

Ignore
1566477506
Reply with quote  #2

1566477506
Report to moderator
1566477506
Hero Member
*
Offline Offline

Posts: 1566477506

View Profile Personal Message (Offline)

Ignore
1566477506
Reply with quote  #2

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

Posts: 1566477506

View Profile Personal Message (Offline)

Ignore
1566477506
Reply with quote  #2

1566477506
Report to moderator
1566477506
Hero Member
*
Offline Offline

Posts: 1566477506

View Profile Personal Message (Offline)

Ignore
1566477506
Reply with quote  #2

1566477506
Report to moderator
1566477506
Hero Member
*
Offline Offline

Posts: 1566477506

View Profile Personal Message (Offline)

Ignore
1566477506
Reply with quote  #2

1566477506
Report to moderator
rosyantony
Newbie
*
Offline Offline

Activity: 70
Merit: 0


View Profile
July 20, 2018, 06:57:17 AM
 #2

Hey... It's a very informative post Thank you for posting!! I appreciate you
I would like to share all Crypto Portfolio Tracker info here: https://coinpedia.org/crypto-tracking-tools/
Pages: [1]
  Print  
 
Jump to:  

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!