Historical Stock Price Function in Power Query

Power Query and Yahoo Finance

This post will walk through how to pull daily stock price from Yahoo! Finance, then transform the data using a technique called a query branch. It will be completed all in the Power Query Editor. We will convert this to a function to reuse on any stock we want.

There are many API to pull stock information that get historical stock prices. Many come with a cost to get this information in a decent format. The technique described here is free but will require some data transformations to get the data in a usable format. The purpose is to explore parameters, web URLs and query branches to design a usable function. If you’re just interested in pulling stock information, skip to the end to grab the M code – just make sure you read the performance considerations.

Note: The content in this blog was first presented at the Power Platform Summit North America on October 18th, 2019.

Getting Started

This blog will use parameters to create functions in Power Query. Some experience using Power Query editor may be helpful, specifically:
– Knowledge of tools such as merge and append queries
– Familiar with query steps and the formula bar

For a detailed look at parameters or if you need to brush up, check out this post on parameters.

Before starting, you will need to ensure the formula bar in the query editor is open.

Open the Power Query Editor by Clicking the Edit Queries on the Home ribbon of Power BI desktop. Then, go to the View ribbon in the Query Editor and make sure the check box for Formula Bar is turned on.

Create the Parameter

First, Create a Parameter. This is a value that we can change and feed into our query, in this case the stock symbol.

In the power query window, under the Home ribbon, Click the bottom half of the Manage Parameters button. From the drop down Select the option New Parameter.

In the Name section, enter the text StockSymbol (without spaces – this makes it much easier to reference later). Give it a description if you like. If you share this report other people can read the description to understand what the parameter is being used for. Change the Type field to Text. Enter MSFT in the Current Value input box. By the way, MSFT is the stock symbol for Microsoft.

Making the Query

Now we have set up a parameter, we can use it to pull in some data. The data source is going to be a web URL. In Power Query editor window, Click the Home ribbon and the button Get Data. Select the item Web in the drop down. In the popup dialogue, Click on the button labeled Advanced.

You’ll notice this brings up a dialog called URL Parts. This allows us to break down the URL into parts. We can easily change individual parts of the URL using this dialog. It will then concatenate it all back together in the order it is entered. Our URL to connect to Yahoo! for a single stock will be split into three parts.

  1. The base URL, which points us to Yahoo! Finance website and the correct data
  2. The stock symbol, in this case will be our parameter
  3. Our other settings (range and interval). These could also be changed in Power BI with a parameter, but we do not want to for this example

The actual URL parts:

1 https://query1.finance.yahoo.com/v8/finance/chart/
2 {StockSymbol}
3 ?range=5y&interval=1d

In the open dialogue box, first Click the button Add part. This will add a new box. Locate the first window and Enter part 1 of the URL. In the second box, Change the abc symbol to a parameter. Make sure Stock Symbol is selected. In the third box, enter part 3 of the URL. We’re setting the range to 5y (5 years of data) and the interval to 1d (daily). You can change these if you want at a later time.

Note: It is important to remember that Stock Symbol is a parameter – change the symbol to parameter and select from the drop down. Do not type Stock Symbol into the box.

Now Hit the button labeled OK. The request will be sent and returned to us in a JSON format.

Rename the query Stock Value. You can edit the name above the Applied Steps section on the right.

Making the Query Branch

The data returned is a little messy and not in the best format. We need to drill down and pull out the appropriate bits of information. Start by drilling down to the correct information. To drill down, Click the underlined result part in the following order:
Chart: Record
Result: List
1: Record

Your screen should look like the image below. If it doesn’t, simply delete the navigation step and start again.

Here, we are presented with three options of paths to drill down further:

Meta: holds some info about the stock, as well as the timeframe and granularity we chose
Timestamp: a list of the dates in the range we selected
Indicators: this holds the price information of stock

Right now, the dates and the price are in two different lists. The price information is another layer down than the dates which makes this more complicated. Extracting these individually would result in a list of random prices and a big list of dates – not helpful if these two pieces of information are not together.

To solve, we will create a Query Branch. The branch will split our query at this step into two paths. One will retrieve the dates, the other the prices. Then we will merge these branches back together to get the dates and prices in the same table.

To start this branch Right Click on the Navigation Step, then Select the option in the drop-down menu Insert Step After. This will reference the previous step and show the same data. Our newly created set is the start of the branch. Rename this new step StartBranch.

Note: the reason for this reference is that the “Navigation” step is not really a step at all. It is actually a collection of steps that Power Query editor groups together. You cannot reference “Navigation”, which will be needed later. You’ll see you cannot rename the Navigation step and if you open the advanced editor you can see the breakdown of individual steps. Another option is two perform any action after the Source step, before you drill down. This will cause Power Query to list each drill down step individually.

Branch 1: Dates

Our first branch we will pull the dates.

Click on timestamp: List.
This will drill down to a list of dates, but they are stored in a UNIX format. UNIX date format is the number of seconds past January 1, 1970 (midnight UTC/GMT), not counting leap seconds. Converting this is quite easy but will take a couple of steps.

First convert the list to a table so we can perform transformations. Click on Transform ribbon. Select the button To Table. Next, under the Add Column ribbon Click the button Custom Column. Change the name to Date and use the following formula in the formula window:

25569 + ( [Column1]/60/60/24 )

Then Select the Date column. Click the Transform ribbon. Under the Data section, Select the Date format. Note: do not select the Date/Time.

Now we have the date but need to preserve its order. This can be solved by adding an index. Go to the Add Column ribbon, Click the little Drop down on the right half of the Index Column button. Select the option From 0 from the drop down menu. Remove the column labeled Column1, as it is not needed anymore. To do this, Right Click on Column1 and select the option Remove from the drop down menu.

This finishes the branch for the dates. Rename this step EndBranchDate by Right Clicking the step in the APPLIED STEPS and Clicking rename.

Branch 2: Prices

Now we need to get the information for the stock prices. Right Click EndDateBranch and Click the option Insert Step After to add the start of the branch. By checking the formula, we can see it says

=EndBranchDate

This step is doing is referencing the step before it, EndBranchDate. It is duplicating the output of this step. We need to get back to the StartBranch step in order to start the second branch. Change the value in the formula bar from = EndBranchDate to = StartBranch.

This now loads us back to this step to drill down to the stock prices. We will use the adjusted close – this is the stock price at the end of the day after adjusting for dividends. Here we need to drill down to this information, by drilling in the following order:

Indicators: Record
adjclose: List
1: Record
adjclose: List

Next, Covert our list to a Table. see above for this step. Here we have the list of prices and again need to preserve the order with an index column. Go to the ribbon labeled Add Column. Click the Index Column and select From 0 in the drop down.

This is the end of this step, so Rename it EndBranchPrice.

To summarize the query so far:

  • Pulled the information for a MSFT stock for 5 years on a daily basis.
  • Drilled down to the dates, converted them to a better format and added an index to preserve order.
  • Revert to an earlier step.
  • Drilled down to the daily prices and added an index column.

Merging the Branches

This leaves two separate tables, but it is only possible to output one of these results. We will need to add a final step to merge these two branches into one table.

On the Home Ribbon, Click the drop down arrow on the Merge Queries button. Then Select the option Merge Queries. This brings up the merge screen. Merge the query with itself. On the bottom half of the merge, Select StockValue (current). Click on the Index column for both top and bottom.

Clicking OK, will merge the data to itself. This is the formula in the formula bar:

= Table.NestedJoin(EndBranchPrice, {"Index"}, EndBranchPrice, {"Index"}, "EndBranchPrice", JoinKind.Inner)

This step uses the Table.NestedJoin formula, which has 6 arguments filled in:

Table.NestedJoin(table1 as table, key1 as any, table2 as any, key2 as any, newColumnName as text, optional joinKind )

In our example, table1 and table2 is the same (EndBranchPrice). This makes sense as we joined it to itself. You will notice that when joining from the same query, the table argument references a step in that query (EndBranchPrice). We really want to join EndBranchPrice to EndBranchDate. We can simply change the second table in the formula bar to EndBranchDate:

Change:

To:

Now, we are joining the EndBranchPrice to the step EndBranchDate. These both have a column named index that we added, which will join our data in the correct order.

Expand the merged table by Clicking the Expand button on the column name. The settings will just Select the Date and Deselect the option to Use original column name as prefix.

Remove the index column as it is not need this anymore. That completes our query with the branch.

Enabling Publish to the Service

If we want to publish this to the service (app.powerbi.com), we will need to make a small edit to our URL. The service will not allow parameters in the base URL. To get around this, we can split our URL using an option in Web.Contents called RelativePath. After Clicking on the Source in the applied steps window, Edit the URL as follows:

From:

To:

Make sure the brackets are correct. Here is the code you can copy and paste into the formula bar:

= Json.Document(
Web.Contents("https://query1.finance.yahoo.com/v8/finance/chart/"
,[RelativePath = StockSymbol  & "?range=5y&interval=1d"]
))

Now we have changed the URL, we need to make a change to the settings. This is because Power BI will try and check the base URL (https://query1.finance.yahoo.com/v8/finance/chart) before it runs the query and adds the second part in RelativePath. This isn’t a valid URL on its own, so it will fail.

To do this, publish the report to the service, and Navigate to the dataset settings. This is found in the service by Clicking the ellipsis in the top right, then the option called Settings in the drop down.

You should be in the tab called Datasets. Then Navigate to the published dataset. Under the option titled Data source credentials, next to Web, Click the option to Edit Credentials. Make sure to check the option to Skip connection test.

If you are interested in learning more, check out Chris Webb’s blog on this here: Using The “Skip Test Connection” Option For Power BI Data Sources To Avoid Scheduled Refresh Failures

Creating the Function

This query uses a parameter which enables us to can convert it to a function. To do this, right click on the query in the Queries pane on the left and select make function.

Now we have a function where we can input any stock symbol and return a list of daily prices. To check multiple stocks, you can add your function to any list of stock symbols. This can be found in Add Column ribbon. Then Clicking the button Invoke Custom Function. This will return a table for each row. Before expanding, it is important to handle errors, otherwise it could break the query. One option is to Right Click the column header, and select the Replace Errors option, and Type the text null.

Performance Considerations

While this query will quickly return single stocks, adding multiple stock will send a different query for each stock. Make sure you design the correct solution to what you are trying to achieve, and check out this article on API considerations.

Final Result

For those who like M code, here is the final function. You can copy and paste this directly into the advanced editor (See this article on how to do this).

let
    Source = Json.Document(Web.Contents("https://query1.finance.yahoo.com/v8/finance/chart/"

,[RelativePath = #"Stock Symbol"  &"?range=5y&interval=1d"]

)),
    chart = Source[chart],
    result = chart[result],
    result1 = result{0},
    Branch = result1,
    timestamp = Branch[timestamp],
    #"Converted to Table" = Table.FromList(timestamp, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Date", each 25569 + ( [Column1]/60/60/24 )),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
    EndBranchDate = Table.Buffer(Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}}) ),
    Custom1 = Branch,
    indicators = Branch[indicators],
    adjclose = indicators[adjclose],
    adjclose1 = adjclose{0},
    adjclose2 = adjclose1[adjclose],
    #"Converted to Table1" = Table.FromList(adjclose2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    EndBranchPrice = Table.Buffer(Table.AddIndexColumn(#"Converted to Table1", "Index", 0, 1) ),
    #"Merged Queries" = Table.NestedJoin(EndBranchPrice, {"Index"}, EndBranchDate, {"Index"}, "EndBranchPrice", JoinKind.Inner),
    #"Expanded EndBranchPrice" = Table.ExpandTableColumn(#"Merged Queries", "EndBranchPrice", {"Date"}, {"Date"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded EndBranchPrice",{"Index"})
in
    #"Removed Columns1"

Visually splitting the steps, the query can be visualized like this:

If you like the content from PowerBI.Tips please follow us on all the social outlets to stay up to date on all the latest features and free tutorials.  Subscribe to our YouTube Channel.  Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.

Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
Store Merchandise

56 Comments

  1. How do I convert the parameter to a label so I can show it in the report?
    There is no way to know which symbol you type in the parameter
    Also what about multiple symbols?

    • Often this function will be added to a list of pre-defined symbols. If you had a list of stock symbols you are interested in, you can use the Add Column > Invoke Custom Function. Choose the column of symbols This will repeat the function for each symbol in your list. In this case the symbol will be included already.
      Otherwise, you could add a custom column to your function that references the parameter – Add Column > custom column, the code is “=StockSymbol”

      Batch queries (large lists of symbols) and optimization will be covered in another blog, it was too much to cover here.

  2. Hi. During the step “Making the Query” after completing 3 part URL and clicking OK in formula bar I get following:
    = Json.Document(Web.Contents(“https://query1.finance.yahoo.com/v8/finance/chart/” & StockSymbol & “?range=5y&interval=1d”))
    but in the result section I get following error:
    Result List
    error null
    Do you have any ideas what might went wrong?

    • I believe you are in the step drilling down – you need to click on List, then Record
      You then should see the same screen as the first screenshot in the
      Making the Query Branch
      step

  3. Steve, I’m ready to publish but I’m lost in the following step:
    To do this, publish the report to the service, and Navigate to the dataset settings. This is found in the service by Clicking the ellipsis in the top right, then the option called Settings in the drop down.
    Q: Where in Excel would I be able to find the “ellipsis” and get to the dataset settings?

  4. How do you extract the Open, High, Low and Close stock prices as well as the Volume of stocks traded.

    • It’s a little trickier. You have to do some extra navigation, including adding index to multiple groups.
      Here’s the code to do this. Those interested can look at the steps:

      let
      Source = Json.Document(Web.Contents("https://query1.finance.yahoo.com/v8/finance/chart/"
      ,[RelativePath = #"Stock Symbol" &"?range=5y&interval=1d"]
      )),
      chart = Source[chart],
      result = chart[result],
      result1 = result{0},
      Branch = result1,
      timestamp = Branch[timestamp],
      #"Converted to Table" = Table.FromList(timestamp, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
      #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1),
      #"Added Custom" = Table.AddColumn(#"Added Index", "Date", each 25569 + ( [Column1]/60/60/24 )),
      #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
      EndBranchDate = Table.Buffer(Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}}) ),
      Custom1 = Branch,
      indicators = Branch[indicators],
      quote = indicators[quote],
      quote1 = quote{0},
      #"Converted to Table1" = Record.ToTable(quote1),
      #"Added Custom1" = Table.AddColumn(#"Converted to Table1", "Custom", each Table.AddIndexColumn(Table.FromList([Value], Splitter.SplitByNothing(), null, null, ExtraValues.Error),"Index",0,1)),
      #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Value"}),
      #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Column1", "Index"}, {"Value", "Index"}),
      EndBranchPrice = Table.TransformColumnTypes(#"Expanded Custom",{{"Name", type text}, {"Value", type number}, {"Index", type number}}),
      #"Merged Queries" = Table.NestedJoin(EndBranchPrice, {"Index"}, EndBranchDate, {"Index"}, "EndBranchPrice", JoinKind.Inner),
      #"Expanded EndBranchPrice" = Table.ExpandTableColumn(#"Merged Queries", "EndBranchPrice", {"Date"}, {"Date"}),
      #"Removed Columns2" = Table.RemoveColumns(#"Expanded EndBranchPrice",{"Index"})
      in
      #"Removed Columns2"

      • Hi Steve, thanks for the new code.
        I’m doing fine so far but am stumped when I have to add a new Custom Column whose value has to be Table. When I clicked on Value then Insert as the Custom column formula I get the result in the whiteboard of a new column with all the rows as “List”.
        I cannot it to display “Table”

        It works when I use your script below:

        = Table.AddColumn(#”Converted to Table1″, “Custom”, each Table.AddIndexColumn
        (Table.FromList([Value], Splitter.SplitByNothing(), null, null, ExtraValues.Error),”Index”,0,1))

        How do I get this to work using the Applied Steps?

        • As I mentioned, it is a little trickier, and that bit was custom written. You can add a custom column and paste the code:

          Table.AddIndexColumn
          (Table.FromList([Value], Splitter.SplitByNothing(), null, null, ExtraValues.Error),”Index”,0,1)

          Deconstructing this, first, it will make a table from the lists of Value
          Table.FromList([Value])
          then, it will add an index column to this new table. This is a way to add an index based for each list before we expand. We want the index to repeat from 0 for each header (close, high, low, volume). This will make sure the correct dates are repeated for each header.

          When writing, I tend to start with the GIUD as much as possible. For example, I added an index column to a table, copied the code from and modified it by replacing the table name with Table.FromList([Value]).

          Again, there are easier ways to do this with other APIs (eg Alpha), but many require you to pay. This is a free one, and therefore the data needs some cleanup! I recommend this article too:
          https://powerbi.tips/2020/02/power-query-api-considerations/

          • Hi Steve. I took some time off to learn more about Power Query and I’m back trying to get this project to work, but nothing I’ve read so far has given me a clue on how to expand and merge multiple columns and their individual index columns. Please let me know if I’m on the right track by reordering the columns and how to merge them.
            Here’s my code.
            Thanks, Rick.

            let
            Source = Json.Document(Web.Contents(“https://query1.finance.yahoo.com/v7/finance/chart/” & StockSymbol & “?range=2y&interval=1d”)),
            chart = Source[chart],
            result = chart[result],
            result1 = result{0},
            #”Start Branch” = result1,
            timestamp = #”Start Branch”[timestamp],
            #”Converted to Table” = Table.FromList(timestamp, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            #”Added Custom” = Table.AddColumn(#”Converted to Table”, “Date”, each 25569 + ( [Column1]/60/60/24 )),
            #”Changed Type” = Table.TransformColumnTypes(#”Added Custom”,{{“Date”, type date}}),
            #”Added Index” = Table.AddIndexColumn(#”Changed Type”, “Index”, 0, 1),
            EndBranchDate = Table.RemoveColumns(#”Added Index”,{“Column1″}),
            Custom1 = #”Start Branch”,
            indicators = Custom1[indicators],
            quote = indicators[quote],
            quote1 = quote{0},
            #”Converted to Table1″ = Record.ToTable(quote1),
            #”Added Custom1″ = Table.AddColumn(#”Converted to Table1″, “Custom”, each Table.AddIndexColumn(Table.FromList([Value], Splitter.SplitByNothing(), null, null,
            ExtraValues.Error),”Index”,0,1)),
            #”Removed Columns” = Table.RemoveColumns(#”Added Custom1″,{“Value”}),
            #”Transposed Table” = Table.Transpose(#”Removed Columns”),
            #”Promoted Headers” = Table.PromoteHeaders(#”Transposed Table”, [PromoteAllScalars=true]),
            #”Changed Type1″ = Table.TransformColumnTypes(#”Promoted Headers”,{{“volume”, type any}, {“open”, type any}, {“low”, type any}, {“close”, type any}, {“high”, type any}}),
            #”Reordered Columns” = Table.ReorderColumns(#”Changed Type1″,{“open”, “high”, “low”, “close”, “volume”})
            in
            #”Reordered Columns”

  5. Hello,
    Thank you for the post.
    Unfortunately, I have a trouble on this step:
    “You should be in the tab called Datasets. Then Navigate to the published dataset. Under the option titled Data source credentials, next to Web, Click the option to Edit Credentials. Make sure to check the option to Skip connection test.”

    Data source credentials are not active (grey).
    And probably without these settings the Function doesn’t work.
    How can I fix it?

    Thank you.

    • I’m unsure, it should give you this option. Do you have other data sources in the query? If so, you may need to add a gateway first.
      If you try to add a gateway, does it list sources?

  6. Thanks for the great tutorial – I’m having some issues with the process of referencing a list of Stock Symbols rather than using a single entry parameter. Can you provide some more granular steps for leveraging a table of stock symbols? Thanks!

    • Hi Scott,

      1. First, create a function. You can use the query you created (or the code in the blog). Right click on the query (in the left pane) and select “Create Function”
      2. Import your list of stocks, You can include multiple columns, but make sure you have stock symbol, and make sure each stock only has one row.
      3. Select “Add Column”. Select “Invoke Custom Function”. Choose the function you just created. For the StockSymbol, change the icon to column and select the column that contains the stock symbol from your table.
      4. This should add a new column with values of “Table”. Click the arrow in the column header, and select “Expand to new rows”

      This blog by Chris Webb expands on functions.

      I will note, this is a free url service which may be slow for large lists. This is because it will go one by one through each stock and repeat the process each time. There are services that offer batch processing – i.e. it returns many stocks at once (e.g. Alpha). You can search for “Stock API” and probably find some, but often will have a price attached. Obviously, choose the best solution for your need.

      • Hello Steve, thanks for your clear explanation. When I try to do the steps above to implement multiple stocks, it gives ‘stack overflow’. So do I have to do it with Alpha? What steps do I have to take? I want to have a table of 20 companies with daily stock prices from the last two years.

        • I would recommend either to use another api (such as alpha) or copy your query and run in dataflows

      • Steve – I tried following your steps, but I’m getting lost. Would you be able to provide a little more step by step? Where am I importing my list of stocks? I was hoping to get a dynamic list based on downloaded investment data…so unioning multiple tables for stock symbol and filtering out duplicates. How do I use this as my source table?

        • It is difficult to do that because it would mean mixing data sources.

          The best way would be to use linked entities in a dataflow (premium feature), plus it will run more efficiently. Otherwise use a paid API where you can batch request stocks

  7. Hi Steve, thanks for such incredible post and responses.
    Since Power BI dropped “Skip Test Connection” and my root url is not valid, I cant find a way to circumvent this. I have a ticker data with >1000 companies for my research and I cant do it manually unfortunately. Here is my code, do you have any ideas?

    (GetData) =>

    let
    Source = Csv.Document(Web.Contents(“https://query1.finance.yahoo.com/v7/finance/download/”
    , [RelativePath = Text.From(GetData) & “?period1=” & Number.ToText(Number.RoundUp((Duration.TotalSeconds((DateTime.LocalNow())- #datetime(1970, 1, 1, 0, 0, 0) – #duration(366, 0, 0, 0))),0)) & “&period2=” & Number.ToText(Number.RoundUp((Duration.TotalSeconds((DateTime.LocalNow())- #datetime(1970, 1, 1, 0, 0, 0))),0)) & “&interval=1d&events=history”]),[Delimiter=”,”, Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #”Change Type” = Table.TransformColumnTypes(Source,{{“Column1”, type text}, {“Column2”, type text}, {“Column3”, type text}, {“Column4”, type text}, {“Column5”, type text}, {“Column6”, type text}, {“Column7″, type text}}),
    #”Promoted Headers” = Table.PromoteHeaders(#”Change Type”, [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”

    • Good news for you, this was only temporary, and “Skip Test Connection” is back! Check the service now and you should see it setting up on your gateway.

  8. Hi Steve,
    Great tutorial! Is there any way to get the historical fundamental data (PE, EPS, Beta, Div, etc.)?

    • There isn’t in this API call, but there are several other sources you could look for. I would recommend doing a search on finance api – depending on your requirements there may be free options available

  9. Excellent walkthrough of PowerQuery / M with live and non-trivial branching – works nicely, including your extra function for multiple stock table (easily setup in Azure SQL DB to keep it all publishable… job done!).

    Gives us continued confidence (and reasons) to move more to M and Power Automate instead of a load of backend SQL pre-processing – the web scraping and API drillthrough of PQ is a gamechanger here. Keep up the good work!

    • Thanks, Phil! I totally agree with these points. These type of calls are a great candidate for dataflows, too. There is lots of work being done in this area – another reason for using PQ!

  10. Hey Steve, I’m trying to use this for multiple ticker symbols at once and following the steps of the comments, I’m having some issues. Whenever I invoke the custom function against my list of stocks, I receive the error: “Formula.Firewall: Query ‘Stocks’ (step ‘Invoked Custom Function’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.”

    I think part of my issue is, the variable used in the function is referencing the original parameter query and not the new list “Stocks” I want the function to iterate through.

    Below is my code of the function:

    let
    Source = (StockSymbol as text) => let
    Source = Json.Document(Web.Contents(“https://query1.finance.yahoo.com/v8/finance/chart/” & StockSymbol & “?range=1y&interval=1d”)),
    chart = Source[chart],
    result = chart[result],
    result1 = result{0},
    StartBranch = result1,
    timestamp = StartBranch[timestamp],
    #”Converted to Table” = Table.FromList(timestamp, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #”Added Custom” = Table.AddColumn(#”Converted to Table”, “Date”, each 25569 + ( [Column1]/60/60/24 )),
    #”Changed Type” = Table.TransformColumnTypes(#”Added Custom”,{{“Date”, type date}}),
    #”Added Index” = Table.AddIndexColumn(#”Changed Type”, “Index”, 0, 1, Int64.Type),
    EndBranchDate = Table.RemoveColumns(#”Added Index”,{“Column1″}),
    Custom1 = StartBranch,
    indicators = Custom1[indicators],
    adjclose = indicators[adjclose],
    adjclose1 = adjclose{0},
    adjclose2 = adjclose1[adjclose],
    #”Converted to Table1″ = Table.FromList(adjclose2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    EndBranchPrice = Table.AddIndexColumn(#”Converted to Table1”, “Index”, 0, 1, Int64.Type),
    #”Merged Queries” = Table.NestedJoin(EndBranchPrice, {“Index”}, EndBranchDate, {“Index”}, “EndBranchPrice”, JoinKind.Inner),
    #”Expanded EndBranchPrice” = Table.ExpandTableColumn(#”Merged Queries”, “EndBranchPrice”, {“Date”}, {“Date”}),
    #”Renamed Columns” = Table.RenameColumns(#”Expanded EndBranchPrice”,{{“Column1”, “Price”}}),
    #”Changed Type1″ = Table.TransformColumnTypes(#”Renamed Columns”,{{“Price”, type number}}),
    #”Add Ticker”= Table.AddColumn(#”Changed Type1″, “Ticker”, each StockSymbol)
    in
    #”Add Ticker”
    in
    Source

    And this is the list of stock tickers, the only column remaining is [Symbol]:
    let
    Source = Stock_Account,
    #”Removed Columns” = Table.RemoveColumns(Source,{“Account”, “Lookup”, “Current Price”}),
    #”Removed Duplicates” = Table.Distinct(#”Removed Columns”),
    #”Invoked Custom Function” = Table.AddColumn(#”Removed Duplicates”, “GetStock”, each GetStock([Symbol]))
    in
    #”Invoked Custom Function”

    • I found something, I’m using an excel file to pull the list of stock symbols and I think the excel data source does not like when I try to call the webpage using that excel source. I was able to copy and paste the values from the list into a new table and that made the function work properly. Not sure why an excel data source throws errors like that

  11. Amazing stuff ! Explained perfectly ! Would suggest you add your comment on how to add multiple stocks in the tutorial immediately.

  12. Hi Steve,

    I was wondering how I might try and change the time horizon of data being pulled. I’m looking to pull the max historical data of a stock but unsure how to do so. I currently have it pulling 10 years. Any help would be appreciated!

    • Just put the range as something like range=9999999y and it should pull all available data

  13. Dear Steve,

    First of all thank you for this amazing tutorial on this subject. Perfectly clear and detailed even for novices.

    I had a few questions/tweaks suggestions:
    1. The data that is downloaded, is it stored? On each daily refresh does the 5 year range of data keep moving (i.e. one extra day now but one less day in the past). If so, would it be possible to tweak the code in order to save the downloaded data and download only the last month for example, in order to reduce the amount of data downloaded on a daily basis?
    2. When searching on Yahoo Finance online, you can also search with the ISIN. Is it possible to do this here as well or the API only works with the ticker?
    3. I noticed that when a ticker is not found (or Yahoo has the ticker but no Historical Data) it gives back an error and doesn’t complete the download. Would it be possible to tweak the code in order not to stop the download for the tickers where there is no problem?

    Thank you for the help!
    Kind regards

    PS: Was the second part of the article published “Batch queries (large lists of symbols) and optimization will be covered in another blog”?

      • Would it be possible to store the data to a table and append only new data ?

        • An incremental refresh would be hard on this data set. Although I don’t think this would speed much up anyway – the data returned is quite small, most of the time is due to the queries sent to the API, which would not change with less data. I would recommend to move to dataflows – this engine handles much better! Or use a more stable (but possible paid for) API

          • sorry still new to the PBI world.
            My question was regard to the data range, I would like to store in the futur (B&H portfolio) the data of past years in order not lost them (a workaround for your answer to Simon’s 1st point)

  14. Hi Steve.

    I am trying to get the gold prices and use “gold” as the “StockSymbol”. I get values back but it does not make sence to me because the values are way to low. For a 5 year period and 1 day granuality I get values like 7,128… Should I do some calculation to get a $ value?

    Kind regards.

      • I am not sure if this is possible for this API, I don’t know enough about it. It may be worth researching other APIs that have this information

  15. Hello, Does this still work ?I tried and I have the impression it doesn’t anymore

      • I manage to get it working (like a charm !) thank you ! (issue was with the drill down I had not the same screen and was a bit lost).

  16. Hi, I get this error in the power bi service datasets tab:
    This dataset includes a dynamic data source. Since dynamic data sources aren’t refreshed in the Power BI service, this dataset won’t be refreshed. Learn more: https://aka.ms/dynamic-data-sources.
    So I can not refresh my data. How can I solve this?

    • Are you using [RelativePath] in the M query – and make sure you set “Skip connection test” when setting up in the service

  17. Could anyone help modify this process to get a daily price in 1 hour or 30 minute intervals? I could get the 1 day intervals to work, but struggling with the hourly price adjustments.

    • You can change part 3 of the URL string.
      Valid intervals: [1m, 2m, 5m, 15m, 30m, 60m, 90m, 1h, 1d, 5d, 1wk, 1mo, 3mo]

      Note that the lower granularity the less range you have. For 1m (1 minute), you can only retrieve 7 days and for 30 minute you can only retrieve 60 days.

      So an example could be:

      1 https://query1.finance.yahoo.com/v8/finance/chart/
      2 {StockSymbol}
      3 ?range=60d&interval=30m

Comments are closed.