Import COINMARKETCAP data into Excel with automatically update in few steps by bdr90

View this thread on: hive.blog | peakd.com | ecency.com

cryptocurrency · @bdr90 · Jul 2 '17 (edited)

$9.26

Import COINMARKETCAP data into Excel with automatically update in few steps

<html>
<p>Hello Stemians! :)</p>
<p>Today i was looking for a way to import data from coinmarketcap to excel because i need to update information about curencies, i use this for track how valuable are my savings.&nbsp;</p>
<p><strong>And I found resolving!</strong>&nbsp;</p>
<p>Now you can import this data to your sheets;).</p>
<pre><code>I used Excel 2013, so i think in Excel 2016 this work too. Let me know.&nbsp;</code></pre>
<p><code>---------------------------------------------------------------------------------------</code></p>
<p>1. You should download <strong>„Microsoft Power Query for Excel”</strong>. When you finished, install it.&nbsp;</p>
<pre><code>You can download from: <a href="https://www.microsoft.com/en-us/download/details.aspx?id=39379&amp;CorrelationId=bc3557e1-418d-4325-b101-ad17694d3901">https://www.microsoft.com/en-us/download/details.aspx?id=39379&amp;CorrelationId=bc3557e1-418d-4325-b101-ad17694d3901</a></code></pre>
<p>2. Run your Excel – now you should have a new tab <strong>„Power query”</strong> - click it</p>
<p><img src="https://s10.postimg.org/6fofhm43t/image.png" width="800" height="179"/></p>
<p><br></p>
<p>3. Next click <strong>„FROM WEB”</strong>. Now should apper window. Copy ticker API (https://api.coinmarketcap.com/v1/ticker/) and paste in new window in Excel. Click <strong>"OK"</strong></p>
<p>This api provide all data about every cryptocurrency on coinmarketcap. If you want you can use other api (https://coinmarketcap.com/api/) to select currency or select another data. On this API is all data you can get from this site. In my opinion the better way is select data inside Excel.</p>
<p><img src="https://s24.postimg.org/mrgkdv2lx/image.png" width="800" height="290"/></p>
<p><br></p>
<p>7. Now you see table of many record, but not data. Go click buton <strong>„To Table”</strong>. In next windows don't change anything. Click <strong>"OK"</strong></p>
<pre><code>In this window in properties section you can change table name to whatever you like.</code></pre>
<p><img src="https://s2.postimg.org/rh3nux1gp/image.png" width="800" height="230"/></p>
<p><img src="https://postimg.org/image/ilmv6yphr/"/><img src="https://s11.postimg.org/o9t5xutub/image.png" width="800" height="293"/></p>
<p><br></p>
<p>8. Ok, now We want see any data. <strong>Click a small icon</strong> (with two opposite arrows) next Column1. There you can select columns what you interested. I recomended select all. Click <strong>"OK"</strong></p>
<p><img src="https://s24.postimg.org/9btjoeu45/image.png" width="800" height="401"/></p>
<p><br></p>
<p>10. On This steps you can change columns names. But if you don't want do on now, you can change it later, normally in sheet. When you finish, click <strong>"Close &amp; Load"</strong></p>
<p><img src="https://s3.postimg.org/gt5dv4yxf/image.png" width="800" height="184"/></p>
<p>&nbsp;</p>
<p>12. From this time, when you click <strong>„Refresh all”</strong> buton, the information on this sheet wil be upadted. &nbsp;</p>
<p><img src="https://s16.postimg.org/ewcic12o5/image.png" width="800" height="427"/></p>
<p>------------------------------------------------------------------------------------------------------------------------------------</p>
<p>If you like post with tricks and hack let me know. In future i try post more information about solutions that make everyday work easier.</p>
<p>If you think my post is helpful, go <strong>upvote</strong> :)&nbsp;</p>
<p>Thank you!</p>
<p><br></p>
</html>

👍 noisy, h3rmes, przemko, sydneyguy1, hass, kryptotr, adrix, eltapatio, jakir, greenelefent, samoil, docsavage9, gmartabtc, mckibbinusa, adminer, kryptominik, dieterhubert, jcletics, sriharsha88, tokensale, preetha, jbobos, freman, bdr90, aymanamer

`author`	bdr90
`permlink`	import-coinmarketcap-data-into-excel-with-automatically-update-in-few-steps
`category`	cryptocurrency
`json_metadata`	{"tags":["steemit","cryptocurrency","blog","crypto","howto"],"image":["https://s10.postimg.org/6fofhm43t/image.png","https://s24.postimg.org/mrgkdv2lx/image.png","https://s2.postimg.org/rh3nux1gp/image.png","https://postimg.org/image/ilmv6yphr/","https://s11.postimg.org/o9t5xutub/image.png","https://s24.postimg.org/9btjoeu45/image.png","https://s3.postimg.org/gt5dv4yxf/image.png","https://s16.postimg.org/ewcic12o5/image.png"],"links":["https://www.microsoft.com/en-us/download/details.aspx?id=39379&CorrelationId=bc3557e1-418d-4325-b101-ad17694d3901","https://api.coinmarketcap.com/v1/ticker/","https://coinmarketcap.com/api/"],"app":"steemit/0.1","format":"html"}
`created`	2017-07-02 16:30:30
`last_update`	2017-07-02 17:11:33
`depth`	0
`children`	12
`last_payout`	2017-07-09 16:30:30
`cashout_time`	1969-12-31 23:59:59
`total_payout_value`	7.032 HBD
`curator_payout_value`	2.226 HBD
`pending_payout_value`	0.000 HBD
`promoted`	2.000 HBD
`body_length`	3,500
`author_reputation`	238,892,018,182
`root_title`	"Import COINMARKETCAP data into Excel with automatically update in few steps"
`beneficiaries`	`[]`
`max_accepted_payout`	1,000,000.000 HBD
`percent_hbd`	10,000
`post_id`	7,034,131
`net_rshares`	1,254,935,333,057
`author_curate_reward`	""

properties (23)vote details (25)

voter	rshares	pct
noisy	1,178,458,623,451	100%
adrix	683,899,112	100%
aymanamer	0	100%
sydneyguy1	3,773,899,771	100%
przemko	6,300,067,049	100%
bdr90	0	0%
h3rmes	62,903,949,536	100%
kryptotr	718,702,465	100%
hass	1,489,854,349	100%
freman	0	100%
eltapatio	349,193,022	100%
jbobos	0	100%
jakir	257,144,302	100%
preetha	0	100%
tokensale	0	100%
sriharsha88	0	100%
jcletics	0	100%
dieterhubert	0	100%
kryptominik	0	100%
adminer	0	100%
mckibbinusa	0	100%
gmartabtc	0	100%
docsavage9	0	100%
samoil	0	100%
greenelefent	0	100%

@bishkopt · Jul 7 '17

I've just made a similar thing in Google Sheets (Docs/Drive):
https://docs.google.com/spreadsheets/d/121y1_U2PiiSDjMGi9BM7PISsLfKkQpqPhOgthWgXcHM/edit?usp=sharing

Enjoy! :)

👍 rf2020

`author`	bishkopt
`permlink`	re-bdr90-import-coinmarketcap-data-into-excel-with-automatically-update-in-few-steps-20170707t214122074z
`category`	cryptocurrency
`json_metadata`	{"tags":["cryptocurrency"],"links":["https://docs.google.com/spreadsheets/d/121y1_U2PiiSDjMGi9BM7PISsLfKkQpqPhOgthWgXcHM/edit?usp=sharing"],"app":"steemit/0.1"}
`created`	2017-07-07 21:41:24
`last_update`	2017-07-07 21:41:24
`depth`	1
`children`	1
`last_payout`	2017-07-14 21:41:24
`cashout_time`	1969-12-31 23:59:59
`total_payout_value`	0.000 HBD
`curator_payout_value`	0.000 HBD
`pending_payout_value`	0.000 HBD
`promoted`	0.000 HBD
`body_length`	173
`author_reputation`	4,880,505,789
`root_title`	"Import COINMARKETCAP data into Excel with automatically update in few steps"
`beneficiaries`	`[]`
`max_accepted_payout`	1,000,000.000 HBD
`percent_hbd`	10,000
`post_id`	7,689,926
`net_rshares`	0
`author_curate_reward`	""

properties (23)vote details (1)

voter	weight	wgt%	rshares	pct	time
rf2020	0 B		0	100%

@shyamkarnani · Jan 26 '18

looked at your Google Doc.....i just wanna ask if we can see the coin prices on differnt exchanges using the API.

properties (22)

`author`	shyamkarnani
`permlink`	re-bishkopt-re-bdr90-import-coinmarketcap-data-into-excel-with-automatically-update-in-few-steps-20180126t120644638z
`category`	cryptocurrency
`json_metadata`	{"tags":["cryptocurrency"],"app":"steemit/0.1"}
`created`	2018-01-26 12:06:39
`last_update`	2018-01-26 12:06:39
`depth`	2
`children`	0
`last_payout`	2018-02-02 12:06:39
`cashout_time`	1969-12-31 23:59:59
`total_payout_value`	0.000 HBD
`curator_payout_value`	0.000 HBD
`pending_payout_value`	0.000 HBD
`promoted`	0.000 HBD
`body_length`	113
`author_reputation`	9,601,142
`root_title`	"Import COINMARKETCAP data into Excel with automatically update in few steps"
`beneficiaries`	`[]`
`max_accepted_payout`	1,000,000.000 HBD
`percent_hbd`	10,000
`post_id`	32,456,446
`net_rshares`	0

@dieterhubert · Jan 15 '18

I have Excel 2016 and by trying to install query i got the massage it is already included. Thank you.

properties (22)

`author`	dieterhubert
`permlink`	re-bdr90-import-coinmarketcap-data-into-excel-with-automatically-update-in-few-steps-20180115t065302740z
`category`	cryptocurrency
`json_metadata`	{"tags":["cryptocurrency"],"app":"steemit/0.1"}
`created`	2018-01-15 06:53:06
`last_update`	2018-01-15 06:53:06
`depth`	1
`children`	0
`last_payout`	2018-01-22 06:53:06
`cashout_time`	1969-12-31 23:59:59
`total_payout_value`	0.000 HBD
`curator_payout_value`	0.000 HBD
`pending_payout_value`	0.000 HBD
`promoted`	0.000 HBD
`body_length`	101
`author_reputation`	63,791,385,716
`root_title`	"Import COINMARKETCAP data into Excel with automatically update in few steps"
`beneficiaries`	`[]`
`max_accepted_payout`	1,000,000.000 HBD
`percent_hbd`	10,000
`post_id`	29,636,880
`net_rshares`	0

@eduardosan · Nov 12 '17 (edited)

This is awesome, thanks! 

Do you know how to import more than just the largest 100 coins?

Edit: I figured I would need to add the coin I'd like to import at the end of the API address (e.g, https://api.coinmarketcap.com/v1/ticker/dcorp/ or https://api.coinmarketcap.com/v1/ticker/melon/)

👍 eduardosan

`author`	eduardosan
`permlink`	re-bdr90-import-coinmarketcap-data-into-excel-with-automatically-update-in-few-steps-20171112t165213569z
`category`	cryptocurrency
`json_metadata`	{"tags":["cryptocurrency"],"app":"steemit/0.1","links":["https://api.coinmarketcap.com/v1/ticker/dcorp/","https://api.coinmarketcap.com/v1/ticker/melon/"]}
`created`	2017-11-12 16:52:12
`last_update`	2017-11-12 17:05:24
`depth`	1
`children`	0
`last_payout`	2017-11-19 16:52:12
`cashout_time`	1969-12-31 23:59:59
`total_payout_value`	0.000 HBD
`curator_payout_value`	0.000 HBD
`pending_payout_value`	0.000 HBD
`promoted`	0.000 HBD
`body_length`	289
`author_reputation`	9,644,062
`root_title`	"Import COINMARKETCAP data into Excel with automatically update in few steps"
`beneficiaries`	`[]`
`max_accepted_payout`	1,000,000.000 HBD
`percent_hbd`	10,000
`post_id`	20,165,494
`net_rshares`	617,220,000
`author_curate_reward`	""

properties (23)vote details (1)

voter	weight	wgt%	rshares	pct	time
eduardosan	0 B		617,220,000	100%

@h3rmes · Jul 6 '17

$0.05

Świetna sprawa, mógłbys gdzieś wrzucić na serwer aby można ściągnąć?

👍 h3rmes, zebzda

`author`	h3rmes
`permlink`	re-bdr90-import-coinmarketcap-data-into-excel-with-automatically-update-in-few-steps-20170706t150128233z
`category`	cryptocurrency
`json_metadata`	{"tags":["cryptocurrency"],"app":"steemit/0.1"}
`created`	2017-07-06 15:01:27
`last_update`	2017-07-06 15:01:27
`depth`	1
`children`	1
`last_payout`	2017-07-13 15:01:27
`cashout_time`	1969-12-31 23:59:59
`total_payout_value`	0.052 HBD
`curator_payout_value`	0.000 HBD
`pending_payout_value`	0.000 HBD
`promoted`	0.000 HBD
`body_length`	68
`author_reputation`	391,047,982,698
`root_title`	"Import COINMARKETCAP data into Excel with automatically update in few steps"
`beneficiaries`	`[]`
`max_accepted_payout`	1,000,000.000 HBD
`percent_hbd`	10,000
`post_id`	7,530,670
`net_rshares`	11,121,140,249
`author_curate_reward`	""

properties (23)vote details (2)

voter	weight	wgt%	rshares	pct	time
h3rmes	0 B		11,121,140,249	18%
zebzda	0 B		0	0%

@bdr90 · Jul 9 '17

https://drive.google.com/file/d/0B5P-Yj05puVWT3JweHR3ZEo2cFk/view

properties (22)

`author`	bdr90
`permlink`	re-h3rmes-re-bdr90-import-coinmarketcap-data-into-excel-with-automatically-update-in-few-steps-20170709t163452844z
`category`	cryptocurrency
`json_metadata`	{"tags":["cryptocurrency"],"links":["https://drive.google.com/file/d/0B5P-Yj05puVWT3JweHR3ZEo2cFk/view"],"app":"steemit/0.1"}
`created`	2017-07-09 16:34:51
`last_update`	2017-07-09 16:34:51
`depth`	2
`children`	0
`last_payout`	2017-07-16 16:34:51
`cashout_time`	1969-12-31 23:59:59
`total_payout_value`	0.000 HBD
`curator_payout_value`	0.000 HBD
`pending_payout_value`	0.000 HBD
`promoted`	0.000 HBD
`body_length`	65
`author_reputation`	238,892,018,182
`root_title`	"Import COINMARKETCAP data into Excel with automatically update in few steps"
`beneficiaries`	`[]`
`max_accepted_payout`	1,000,000.000 HBD
`percent_hbd`	10,000
`post_id`	7,886,182
`net_rshares`	0

@jakir · Jul 2 '17

I like it very much!!! Amazing post, this is something I'm looking for because I just know the crypto currency 💴 Thank you brother! I started my Steemit adventure! - Follow me @jakir as suggestions about my future posts will be amazing 👏 thank you very much

👍 bdr90

properties (23)vote details (1)

voter	weight	wgt%	rshares	pct	time
bdr90	0 B		733,565,812	100%

@mckibbinusa · Jan 16 '18

Anyone looking for a truly robust solution for importing cryptocurrency, stocks, bonds, and essentially all other all source financial data into Excel should check out XLQ -- more at:

https://steemit.com/cryptocurrencies/@mckibbinusa/new-version-xlq-6-0-now-supports-cryptocurrencies

properties (22)

`author`	mckibbinusa
`permlink`	re-bdr90-import-coinmarketcap-data-into-excel-with-automatically-update-in-few-steps-20180116t153755064z
`category`	cryptocurrency
`json_metadata`	{"tags":["cryptocurrency"],"links":["https://steemit.com/cryptocurrencies/@mckibbinusa/new-version-xlq-6-0-now-supports-cryptocurrencies"],"app":"steemit/0.1"}
`created`	2018-01-16 15:38:00
`last_update`	2018-01-16 15:38:00
`depth`	1
`children`	0
`last_payout`	2018-01-23 15:38:00
`cashout_time`	1969-12-31 23:59:59
`total_payout_value`	0.000 HBD
`curator_payout_value`	0.000 HBD
`pending_payout_value`	0.000 HBD
`promoted`	0.000 HBD
`body_length`	284
`author_reputation`	15,957,671,851
`root_title`	"Import COINMARKETCAP data into Excel with automatically update in few steps"
`beneficiaries`	`[]`
`max_accepted_payout`	1,000,000.000 HBD
`percent_hbd`	10,000
`post_id`	29,966,814
`net_rshares`	0

@morality · Jul 8 '17

yeah this application could be useful for any trader

properties (22)

`author`	morality
`permlink`	re-bdr90-import-coinmarketcap-data-into-excel-with-automatically-update-in-few-steps-20170708t182329052z
`category`	cryptocurrency
`json_metadata`	{"tags":["cryptocurrency"],"app":"steemit/0.1"}
`created`	2017-07-08 18:23:30
`last_update`	2017-07-08 18:23:30
`depth`	1
`children`	0
`last_payout`	2017-07-15 18:23:30
`cashout_time`	1969-12-31 23:59:59
`total_payout_value`	0.000 HBD
`curator_payout_value`	0.000 HBD
`pending_payout_value`	0.000 HBD
`promoted`	0.000 HBD
`body_length`	52
`author_reputation`	174,776,140,429
`root_title`	"Import COINMARKETCAP data into Excel with automatically update in few steps"
`beneficiaries`	`[]`
`max_accepted_payout`	1,000,000.000 HBD
`percent_hbd`	10,000
`post_id`	7,781,151
`net_rshares`	0

@mskavim · Jan 7 '18

not loading more than 100 rows for me

properties (22)

`author`	mskavim
`permlink`	re-bdr90-import-coinmarketcap-data-into-excel-with-automatically-update-in-few-steps-20180107t183918131z
`category`	cryptocurrency
`json_metadata`	{"tags":["cryptocurrency"],"app":"steemit/0.1"}
`created`	2018-01-07 18:39:21
`last_update`	2018-01-07 18:39:21
`depth`	1
`children`	1
`last_payout`	2018-01-14 18:39:21
`cashout_time`	1969-12-31 23:59:59
`total_payout_value`	0.000 HBD
`curator_payout_value`	0.000 HBD
`pending_payout_value`	0.000 HBD
`promoted`	0.000 HBD
`body_length`	37
`author_reputation`	658,855,845
`root_title`	"Import COINMARKETCAP data into Excel with automatically update in few steps"
`beneficiaries`	`[]`
`max_accepted_payout`	1,000,000.000 HBD
`percent_hbd`	10,000
`post_id`	27,818,519
`net_rshares`	0

@mskavim · Jan 7 '18

https://api.coinmarketcap.com/v1/ticker/?limit=10 

load all data

properties (22)

`author`	mskavim
`permlink`	re-mskavim-re-bdr90-import-coinmarketcap-data-into-excel-with-automatically-update-in-few-steps-20180107t184925062z
`category`	cryptocurrency
`json_metadata`	{"tags":["cryptocurrency"],"links":["https://api.coinmarketcap.com/v1/ticker/?limit=10"],"app":"steemit/0.1"}
`created`	2018-01-07 18:49:27
`last_update`	2018-01-07 18:49:27
`depth`	2
`children`	0
`last_payout`	2018-01-14 18:49:27
`cashout_time`	1969-12-31 23:59:59
`total_payout_value`	0.000 HBD
`curator_payout_value`	0.000 HBD
`pending_payout_value`	0.000 HBD
`promoted`	0.000 HBD
`body_length`	65
`author_reputation`	658,855,845
`root_title`	"Import COINMARKETCAP data into Excel with automatically update in few steps"
`beneficiaries`	`[]`
`max_accepted_payout`	1,000,000.000 HBD
`percent_hbd`	10,000
`post_id`	27,820,383
`net_rshares`	0

@preetha · May 7 '18

Thanks; this was very helpful.

properties (22)