Stock Market Data in Power BI: Yahoo Finance’s Hidden Hack!

Introduction

This tutorial will guide you through a powerful workaround to import historical stock data into Power BI using Yahoo Finance, especially after the CSV download option was removed. By intercepting internal API calls and transforming the data, you will learn how to create a function in Power BI that can fetch data for multiple stocks dynamically.

Step 1: Understanding the Problem

• Yahoo Finance has removed the CSV download option for stock data, making it challenging to import historical data directly into Power BI.
• This limitation can hinder data analysis and visualization efforts, particularly for stock market enthusiasts and analysts.

Step 2: Intercepting Yahoo Finance API Calls

• Use a tool like Postman or your web browser's developer tools to capture the API requests made by Yahoo Finance.
• Follow these sub-steps:
  1. Open Yahoo Finance in your browser.
  2. Find the stock you want data for (e.g., Apple).
  3. Open developer tools (F12 in most browsers).
  4. Navigate to the Network tab and refresh the page.
  5. Look for API calls that retrieve stock data (usually in JSON format).
  6. Copy the API endpoint URL for later use.

Step 3: Extracting and Transforming Data in Power BI

• Open Power BI and navigate to the Power Query Editor.
• Follow these steps to extract data from the intercepted API:
  1. Go to Home > New Source > Web.
  2. Paste the API URL you copied.
  3. Click OK to retrieve the data.
  4. Once the data loads, you may need to parse the JSON structure:
     • Select the JSON column and click Transform.
     • Choose Expand to see nested data.
  5. Clean and shape your data as needed for analysis.

Step 4: Creating a Dynamic Function for Multiple Stocks

• To create a reusable function for fetching data for different stocks:
  1. In the Power Query Editor, go to Home > Advanced Editor.
  2. Create a new function with the following structure:

     (stockSymbol as text) =>
     let
         Source = Json.Document(Web.Contents("https://finance.yahoo.com/quote/" & stockSymbol & "/history")),
         Data = Source[quoteSummary][result]{0}[historicalData]
     in
         Data
     

  3. Save this function.
  4. To use the function, invoke it in a new query by passing the stock symbol of your choice.

Conclusion

You have successfully learned how to intercept API calls from Yahoo Finance and import stock data into Power BI. By creating a dynamic function, you can now fetch historical data for multiple stocks easily. This method enhances your data analysis capabilities and allows for more informed decision-making in stock trading and investment analysis.

Next Steps

• Experiment with different stocks using your new function.
• Consider adding visualizations to your Power BI report to analyze trends over time.
• Explore further resources on Power BI and API integration to expand your data analysis skills.