Extract Data from Web Query

[pattaman]

Hi Guys,

So I have run a web Query using a .iqy text file which is pulling data from a popular cryptocurrency site called CoinMarketCap.

I am using api links for individual coins in order to get there price is USD updated every minute to try and track in an excel ticker.

An example of a url is: https://api.coinmarketcap.com/v1/ticker/steem/

When I run the web query it returns the below in my excel sheet:

[
{
"id": "steem",
"name": "Steem",
"symbol": "STEEM",
"rank": "78",
"price_usd": "0.1935110207",
"price_btc": "0.00002029",
"24h_volume_usd": "4120576.12869",
"market_cap_usd": "69524222.0",
"available_supply": "359277842.0",
"total_supply": "376251936.0",
"max_supply": null,
"percent_change_1h": "1.01",
"percent_change_24h": "3.14",
"percent_change_7d": "17.65",
"last_updated": "1580923443"
}
]

I want to be able to extract the price_usd to use to multiply other cells by and I can't figure out how to extract values from the query without stopping it from updating.

Any help please?

 

[sandy666]

try

// Price USD
let
    Source = Json.Document(Web.Contents("https://api.coinmarketcap.com/v1/ticker/steem/")),
    TFR = Table.FromRecords(Source)[price_usd]
in
    TFR

 

[pattaman]

Thanks for this, silly question but I've never used code before, how do I input this?

 

[sandy666]

first update your profile about Excel / platform version then I can say more

 

[sandy666]

you need to say something like: updated

The new functionality (Power Query) is live for all Office Insiders Fast customers in Excel for Mac version 16.26.521.0 16.26 (19052200) and later.
---
so
Data - Get Data - From Other Sources - Blank Query

(if you don't see that, it means you don't have Power Query and solution is ended before it's start)

but if you've Power Query and action above open Power Query Editor, go to Advanced Editor, replace whole code there with copied from the post, Done, change name Query1 to Price USD,
Close&Load - it should load small table to the sheet.
Find Connections - Query Price USD - Properties - Usage tab and set

then OK, Close

That's all I think

 

[pattaman]

Hi,

So I have Office Insiders Fast for Mac but still no access to power query?

The only options I get are get external data from:
- HTML
- Text
- New Database Query (From SQL Server or Database)

 

[sandy666]

check version and/or ask them (Microsoft) why you don't have PQ