Are you keen on tracking historical stock prices in Power BI? Leverage the power of Power Query to seamlessly fetch and analyze stock prices from Yahoo Finance. This blog post will guide you through a step-by-step process, providing a convenient Power Query code snippet that you can integrate directly into your Power BI reports.
Fetching Stock Data with Power Query
Let’s dive into a Power Query function designed to retrieve historical stock prices from Yahoo Finance. This function requires two parameters: Stockquote (the stock symbol) and Till_Period (the end date for the historical data).
(Stockquote as text, Till_Period as text) as table =>
let
Source = Csv.Document(Web.Contents("https://query1.finance.yahoo.com/v7/finance/download/" & Stockquote & "?period1=511056000&period2=" & Till_Period & "&interval=1d&events=history&includeAdjustedClose=true"), [Delimiter=",", Columns=7, Encoding=65001, 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"This Power Query function utilizes Yahoo Finance’s API to download historical stock prices in CSV format and transforms the data into a table with essential columns such as Date, Open, High, Low, Close, Adjusted Close, and Volume.
How to Use the Power Query Code in Power BI
- Open Power BI Desktop: Launch Power BI Desktop and open the report where you want to integrate the stock prices.
- Go to Home Tab: Navigate to the “Home” tab in Power BI Desktop.
- Get Data: Click on “Get Data” and choose “Blank Query.”
- Open Advanced Editor: In the Power Query Editor, go to the “View” tab and click on “Advanced Editor.”
- Paste the Code: Copy and paste the provided Power Query function into the editor.
- Input Parameters: After pasting the code, input the stock symbol and end date (in the Unix timestamp format) as parameters. Customize the function according to your specific needs.
- Load Data: Click on “Done” to close the editor. The data will be loaded into your Power BI report.
Now, you have a dynamic connection to Yahoo Finance, enabling you to update historical stock prices with a straightforward refresh.
Calculating Unix Timestamp in Power BI
Additionally, let’s explore how to calculate Unix timestamps in Power BI. A Unix timestamp represents the number of seconds that have elapsed since January 1, 1970 (the Unix epoch). Use the following DAX formula to convert a date to a Unix timestamp:
UnixTimestamp = ([Date] - DATE(1970, 1, 1)) * 86400Replace [Date] with the appropriate column reference containing the date.
Calculating Unix Timestamp in Excel
Additionally, let’s explore how to calculate Unix timestamps in Excel. A Unix timestamp represents the number of seconds that have elapsed since January 1, 1970 (the Unix epoch). Here’s a simple formula to convert a date to a Unix timestamp:
=(A1-DATE(1970,1,1))*86400Replace A1 with the cell reference containing the date. This formula subtracts the Unix epoch from the given date and multiplies the result by the number of seconds in a day (86400).
Further Guidance and Tutorial
For a more detailed walkthrough of this process, including visual demonstrations and additional insights, check out the accompanying video tutorial on my YouTube channel. The video provides a visual guide to help you navigate through the steps and customize the Power Query function for your specific requirements within Power BI.
Conclusion
In this blog post, we’ve equipped you with a potent Power Query function to seamlessly fetch historical stock prices from Yahoo Finance directly into Power BI. Additionally, we’ve shared a quick method to calculate Unix timestamps in Power BI. These tools empower you to perform advanced financial analyses and visualizations within the powerful environment of Power BI.
NOTE:
Privacy and Disclaimer:
This tutorial and accompanying blog post are created for educational purposes only. Any attempt to access, retrieve, or use personal data from external platforms, such as Yahoo Finance, should be conducted in compliance with their privacy policies and terms of service. Users are strongly encouraged to contact Yahoo Finance directly for any concerns related to data privacy and compliance. The content provided is meant to educate and guide users in leveraging data for analytical purposes, and all actions taken are at the discretion and responsibility of the individual user.
Leave a Reply