Power Query URL Change

msarfaraz

New Member
Joined
Oct 18, 2012
Messages
46
let
Source = Csv.Document(Web.Contents("https://query1.finance.yahoo.com/v7/finance/download/" & "BTC-USD" & "?period1=1581415348&period2=1613037748&interval=1d&events=history&includeAdjustedClose=true"),[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Adj Close", type number}, {"Volume", Int64.Type}})
in
#"Changed Type"



How I can change the above query instead of "BTC-USD" I want a column (Text field) to pick the dynamically.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
Change "BTC-USD" to refer to the relevant column.
 

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
674
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
First, you need to transform your query into a function by adding one more line at the top, and then referencing your new variable in the web link:

(ticker as text) as table =>
let
Source = Csv.Document(Web.Contents("https://query1.finance.yahoo.com/v7/finance/download/" & ticker & "?period1=1581415348&period2=1613037748&interval=1d&events=history&includeAdjustedClose=true"),[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Adj Close", type number}, {"Volume", Int64.Type}})
in
#"Changed Type"

Second, you create a separate query with just one column, which will be your list of tickers (not sure if these tickers even exist, just taking a guess :)).

[ATTACH type="full"]31816[/ATTACH]

The last step is to go to Add Column > Invoke Custom Function > choose the first query as your Function query > parse Tickers column as ticker parameter.
1613049222344.png


Afterwards, click on "double arrow" icon to expand your formula results.
1613049269019.png
 

Attachments

  • 1613049313137.png
    1613049313137.png
    2.3 KB · Views: 1

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
674
Office Version
  1. 365
  2. 2013
Platform
  1. Windows

ADVERTISEMENT

Perfect! So now you can apply step no 3 to your CoinsSymbol query, which is:


The last step is to go to Add Column > Invoke Custom Function > choose the first query as your Function query > parse Tickers column as ticker parameter.
1613049222344.png



Afterwards, click on "double arrow" icon to expand your formula results.
1613049269019.png
 

msarfaraz

New Member
Joined
Oct 18, 2012
Messages
46
Perfect! So now you can apply step no 3 to your CoinsSymbol query, which is:


The last step is to go to Add Column > Invoke Custom Function > choose the first query as your Function query > parse Tickers column as ticker parameter.
1613049222344.png



Afterwards, click on "double arrow" icon to expand your formula results.
1613049269019.png
Get this error
 

Attachments

  • Capture.JPG
    Capture.JPG
    32.2 KB · Views: 2

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
674
Office Version
  1. 365
  2. 2013
Platform
  1. Windows

ADVERTISEMENT

It looks like your tickers column (CoinsSymbol query) was build using some other sources. Can you please try first to create a "static" Power Query (by using "Enter Data"), enter some 3-5 tickers that you know will work, and invoke the function again? We'll take it from there.

1613052658802.png
 

msarfaraz

New Member
Joined
Oct 18, 2012
Messages
46
From there it works, can it possible to take from that table since it's dynamic and will update once a new coin will be available?
 

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
674
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
I didn't experience such issue myself but it looks like the solution might be to go to File > Options and Settings > Options > Privacy > Ignore Privacy Levels...

 

msarfaraz

New Member
Joined
Oct 18, 2012
Messages
46
I didn't experience such issue myself but it looks like the solution might be to go to File > Options and Settings > Options > Privacy > Ignore Privacy Levels...

 

Attachments

  • Capture.JPG
    Capture.JPG
    48.2 KB · Views: 2
  • Capture1.JPG
    Capture1.JPG
    57.7 KB · Views: 2

Watch MrExcel Video

Forum statistics

Threads
1,130,217
Messages
5,640,939
Members
417,180
Latest member
nomans2325

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