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?
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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
 
Upvote 0
Thanks for this, silly question but I've never used code before, how do I input this?
 
Upvote 0
first update your profile about Excel / platform version then I can say more
version.png
 
Upvote 0
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:
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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
Back
Top