Extract Data from Web Query

pattaman

New Member
Joined
Sep 23, 2019
Messages
5
Office Version
  1. 365
Platform
  1. MacOS
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?
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
try

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

pusd.png
 

pattaman

New Member
Joined
Sep 23, 2019
Messages
5
Office Version
  1. 365
Platform
  1. MacOS
Thanks for this, silly question but I've never used code before, how do I input this?
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
first update your profile about Excel / platform version then I can say more
version.png
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

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
macblankquery.png

(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
refresh1.png
then OK, Close

That's all I think :cool:
 
Last edited:

pattaman

New Member
Joined
Sep 23, 2019
Messages
5
Office Version
  1. 365
Platform
  1. MacOS
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
check version and/or ask them (Microsoft) why you don't have PQ
 

Watch MrExcel Video

Forum statistics

Threads
1,122,588
Messages
5,597,046
Members
414,116
Latest member
sfullnet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top