How to Do Live Currency Data on Excel !

in #cryptocurrency7 years ago (edited)

Data-Analyst.png

How to Do Live Currency Data on Excel!

I figured how to make live and self- automated excel for my cryptocurrency portfolio. I wanted to share this with you guys.

Step 1 - First, I am opening a clean excel sheet

1.JPG

Step 2 - Creating Empty Template

I am just creating a template for me to enter data later. (I haven’t entered any numbers yet, just created a table for what I will be entering for

2.JPG

Step 3 - How to export live crypto data? (tricky part)

In order to export live data, you need to go Excell – Data – New Query – From Other Source – From Web. Then you have to enter following.
https://api.coinmarketcap.com/v1/ticker/

4.png

3.JPG

To see it better, lets convert it to table and extend the columns. Then, click Load and close.

5.JPG

There you go, now we have live data for all the coins. This will be your raw data that you reference later for corresponding cell. (But I like to use this page of the excel, just to quickly check a price for a currency)
This is how it should look like;

6.JPG

Step 4 - Exporting Individual Coin Data to Reference on Your Chart

You can also export individual cryptocurrency data. Repeat step 3, but for the api link use following
For bitcoin - https://api.coinmarketcap.com/v1/ticker/bitcoin
For litecoin - https://api.coinmarketcap.com/v1/ticker/litecoin
For ethereum - https://api.coinmarketcap.com/v1/ticker/ethereum

I think you had the idea :) (If you are not sure how to spell the name of a currencry, you can check by clicking its name from www.coinmarketcap.com)
Click ‘’Record’’ , and click ‘’Into Table’’
7.JPG
It will be easier to reference from these later on. This is how it should look like
8.JPG

Step 5 - Refresh Rate for Live Data

This is a live data and in order to set refresh rate, click the Litton arrow on ‘’Refresh All’’ then click ‘’Connection Properties’’
You can make your file to refresh everytime you open the data and set some interval for automated refresh. (you can also refresh manually from ‘’Excel – Data - Refresh All’’ button)

9.png

10.JPG

Step 6 - Referencing for Our Personal Table

Okay ! Now we have our live data, we can just reference these data to our template table

To reference the data from live price, click in the corresponding cell, then put ‘’ = ‘’ symbol , go to bitcoin live price page and reference it by shift + left click on live data value on your exported raw data.

11.JPG

12.JPG

13.JPG

Step 7 - How It Should Look Like Once You Are Done

I only showed it basic live data export and reference for Bitcoin and Ethereum . But You can do lots of fun things (putting Pie chart, Percent profit calculations… )
I am sharing my personal Excel sheet for my portfolio.

Let me know If you need any help doing this setup. I will always respond to the comments

14.JPG

I hope this helps guys ! Let me know what you think :)

Sort:  

I know my images looks small but If you guys zoom in, It's actually pretty good. Just saying :)

This comment has received a 1.55 % upvote from @aksdwi thanks to: @readante.

Hey, @originalworks check my work please :)

The @OriginalWorks bot has determined this post by @readante to be original material and upvoted it!

ezgif.com-resize.gif

To call @OriginalWorks, simply reply to any post with @originalworks or !originalworks in your message!

Thanks <3

@readante: Interesting idea & approach. Although I find it too analytical, your post sparks a lot of ideas in my mind. I am going to test it on Airtable https://airtable.com/invite/r/sXnoGwcf to see if that Excel Cloud Competitor Product just can be used to developed your guidelines.

The second most important idea, is that you are reinforcing in my mind, another finding where it says that the Steem Blockchain can be Query from Excel. And here you are giving proof of it.

Your Post have been Upvoted & Resteemed

Wow okay ! I saw your links below too, thanks for those ! I think there more than one way to do this.

Another way is , a website called ''Cointracking'' you record your currencies and it just keeps updating the values

4.JPG

Thank you for your interest at my post !!

Let me attempt to find that other post over querying Steem BlockChain, because it will complement your findings/approach/creation tool.

site:steemit.com querying steem block chain using excel

For finding additional information just run the Google Site Operator and you will get everything publish on steemit on that subject.

This is GREAT! thanks! It would be really cool if you would upload a simple template for everyone to download and customize for themselves

Hi, It sounds good, I can also do that next time !

Very useful! I will try it right now.

Bringing crypto market data to the Excel domain makes easier to manipulate and find insights that usually online services don't offer.

Thanks a lot @readante. Upvoted, resteemed and now following you :)

Thank you ! I am glad it was helpful for you. Let me know IF you have any questions !

I couldn't try it because my Office version is 2013, and I need to install the Power Query add-in (Get & Transform).

In Excel documentation is mentioned that this add-in is free, but when I click the link to download it I get a "Page can't be found".

Same problem happened to me while trying to transfer online data. I also had older version of Excel, so I did not have Power Query.

I just had to update my excel to current version.
Luckily, I am still student so my school in Montana allow me to download it for free.

Sorry, I do not know how to add Query add-in to older version of Excel :/

Hehe, lucky you!
Well, it is supposed to be a free download, but for some unkown reason is unavailable. I'll check it later, but anyways... Thanks for the info! Big Excel fan here...

Simple but amazing!

Thanks man !

Wow, thank you for this very helpful excel tutorial @readante ! :)Resteeming it.

Thank you , I appreciate it :)

You're welcome! :)

Thanks for sharing this informative skills for excel, I have learned a lot. Thanks for sharing skills with us. :)

Glad you liked it!

This post was resteemed by @steemvote and received a 25.69% Upvote

Good Post thank you bro :) Following and follow me please :)

Thanks man !