Convert JSON data to time series data in excel

[SweMan]

For the last 10 years or so I have wanted to convert JSON data to time series data in Excel. I never found an easy solution. This has irritated me.

Coinmarketcap has an API for crypto currencies and JSON data https://api.coinmarketcap.com/v2/ticker/ https://api.coinmarketcap.com/v2/ticker/

{
"data": {
"1": {
"id": 1,
"name": "Bitcoin",
"symbol": "BTC",
"website_slug": "bitcoin",
"rank": 1,
"circulating_supply": 17078187.0,
"total_supply": 17078187.0,
"max_supply": 21000000.0,
"quotes": {
"USD": {
"price": 7618.12,
"volume_24h": 4704600000.0,
"market_cap": 130103677948.0,
"percent_change_1h": -0.31,
"percent_change_24h": 2.33,
"percent_change_7d": 1.37
}
},
"last_updated": 1528273173
},
​

I want to schedule a daily data fetch in excel but I only want to store the price, crypto currency name and the time stamp in a column format:

Date, Bitcoin, Ethereum, Ripple etc
2018-05-01, 5000, 500, 0.5
2018-05-02, 5001, 501, 0.51
etc


Such data format is very important in order to be able to do any scientific research. How can this be done?

 

[John_w]

You could write VBA code which requests the JSON data using XMLhttp and parses it using a JSON parser. For example, there is code at https://www.mrexcel.com/forum/excel...n-data-real-time-post5045963.html#post5045963 which could be adapted for your request.

Alternatively, you could try Excel's Power Query to import the data and convert it to the required format. However, I don't know how to use the UI to get the data into the format you want.

 

[SweMan]

Thanx John. I will have a look at the vba code