Mukesh Dipto
This article will illustrate how to import historical stock prices into Excel from Yahoo Finance. Yahoo! Finance was first introduced by Yahoo! Network. It offers stock prices, press releases, financial reports, and original material, as well as financial news, data, and opinion. In this article, we will demonstrate the steps to import historical stock prices in our excel sheet from Yahoo! Finance.
Download Free Template
You can download the free template from here.
What Is a Historical Stock Price?
Historical stock price depicts the price of a stock on a specific date in the past. Investors from different industries use historical prices to analyze stock decisions. An Investor can gather historical stock from different resources. The resources include historical stock price websites and online software. A historical stock price gives an overview to an investor about a specific stock traded on a certain date.
3 Easy Steps to Import Historical Stock Prices into Excel from Yahoo Finance
To import stock prices into Excel from Yahoo! Finance, we do not need to import data manually. We will update the ticker symbol, start date, and end date. After that, stock prices update automatically in our excel sheet from Yahoo! Finance. A ticker symbol refers to a short form of a company on a particular stock market. We will discuss the whole process in the following 3 steps.
STEP 1: Create Query
First and foremost, we will create a query to import the historical stock prices from Yahoo! Finance of a specific company. For instance, we want to create a query with Microsoft’s historical stock prices for the last 3 months. Let’s see the steps to perform this method.
- To begin with, go to Yahoo! Finance Search for the company Microsoft in the search bar.
- In addition, go to the ‘Historical Data’ tab.
- Furthermore, set the Time Period span 3M.
- Then, right-click on the Download option.
- Select the option ‘Copy link address’.
- Now, open a new excel sheet.
- Moreover, go to the Data.
- Then, from the ribbon, click on the option ‘From Web’.
- A new dialogue box will open.
- After that, paste the link in the URL field.
- Click on OK.
- Finally, we get a preview of data like the following image.
STEP 2: Fix Variables
In the second step, we will use the named range. We have to set a named range to import historical stock prices into Excel from Yahoo! Finance. We want to import data for a specific ticker symbol, start date, and end date. So in this step, we will create 3 named ranges called Ticker, StartDate, and EndDate. Let’s see the steps in this section.
- First, go to the same worksheet where we imported the query data.
- Next, create fields like the following image for Ticker, StartDate, and EndDate.
- Then, select cell L4. Type the following formula in that cell:
="="&((K4-DATE(1970,1,1))*60*60*24)- Press Enter.
- The above command converts the date of cell K4 into a timestamp.
- Similarly, insert the following formula in cell L5:
="="&((K5-DATE(1970,1,1))*60*60*24)- Press Enter.
- So, we get results like the following image.
- Afterward, set the named range Ticker for cell J2.
- Subsequently, the set named range StartDate for cell L4.
- In the end, select cell L5 and set the named range EndDate.
STEP 3: Import Stock Prices in a Dynamic Way
In the final step, we create a dynamic template. We will only update the name of the ticker, StartDate, and EndDate. As a result, we will get historical stock prices for the updated parameters. Let’s see the steps to do this.
- Firstly, go to the Query Select the option Edit from the ribbon.
- A new dialogue box will open.
- Secondly, go to the Home tab and select the option ‘Advanced Editor’.
- Another dialogue box named ‘Advanced Editor’ will open. In the dialogue box we can see the following code:
let
Source = Csv.Document(Web.Contents("https://query1.finance.yahoo.com/v7/finance/download/"&Ticker&"?period1"&StartDate&"&period2"&EndDate),[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"
- Thirdly, add Tickers, StartDate, and EndDate in the code in the following way:
let
Ticker = Excel.CurrentWorkbook(){[Name="Ticker"]}[Content]{0}[Column1],
StartDate = Excel.CurrentWorkbook(){[Name="StartDate"]}[Content]{0}[Column1],
EndDate = Excel.CurrentWorkbook(){[Name="EndDate"]}[Content]{0}[Column1],
Source = Csv.Document(Web.Contents("https://query1.finance.yahoo.com/v7/finance/download/"&Ticker&"?period1"&StartDate&"&period2"&EndDate),[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"- Now, click on the Done button.
- We have to adjust the query code like the highlighted part of the above image. It helps to generate the code correctly.
- Furthermore, click on the ‘Close & Load’ option.
- The above action will update the code in the worksheet.
- Afterward, set the Ticker value to AAPL. This ticker symbolizes the company Apple.
- After that, go to the Data tab and click on Refresh All.
- Lastly, we get historical stock prices for the company Apple.
Read More: How to Add Stock Data Type in Excel (2 Effective Methods)
Conclusion
In conclusion, this tutorial shows how to import historical stock prices in excel from Yahoo! Finance. Use the practice worksheet that comes with this article to put your skills to the test. If you have any questions, please leave a comment below. Our team will make every effort to react to you as quickly as possible. Keep an eye out for more inventive Microsoft Excel solutions in the future.
Related Articles
- How to Download Historical Stock Data into Excel (with Easy Steps)
- Get Stock Quote with Excel Add-in (With Easy Steps)
- How to Calculate Correlation between Two Stocks in Excel (3 Methods)
- How to Get Historical Data of NSE Stocks in Excel (2 Effective Ways)
- How to Get Stock Quotes in Excel (2 Easy Ways)
- Stock return analysis using histograms & 4 skewness of histograms
Very useful and clear tutorial.
Hi
I tried this query for Indian market(NSE) , it is giving Error ( Expression Error Column1 not found), plz help
Hi NAYAZ,
Thanks for following our article. We have checked the article again and found no errors. Please go through the whole article again and create the template. Or you can do this easily by downloading the given template.
Keep in mind that if you want to import stock prices from any other website, then it will not work. You should use “^NSEI” in the ticker box.
Let us know if your problem is fixed.
Regards.
-Alok Paul
Author at ExcelDemy
Hi Mukesh- what changes to the code are necessary to change the interval from daily to weekly? Thank you, Brian J
Thank you BRIAN for your wonderful question
If you want to import stock prices on a weekly basis, you can visit this website link, from which we scraped our live data in accordance with this article.
Then, click on the Frequency option and select Weekly for your desired frequency.
Finally, apply the rest procedures mentioned in the article. I hope this may solve your issue.
Bishawajit, on behalf of ExcelDemy
Good Day, Thank you for your expertise Sir. I’m having the same issue where I’d like to create weekly and monthly sheets. Everything is fine until I get to the step to enter the code in the advanced editor.
It can be imported from the URL in a monthly price format, but as soon as the advanced editor code is loaded, it defaults right back to daily prices. Would you be so kind as to offer any suggestions Sir? Thank you so much.
Thank you JACK MACEY, for your wonderful question
If you want to import stock prices on a daily, weekly, monthly basis, you can visit this website link, from which we scraped our live data in accordance with this article.
Then, click on the Frequency option and select whatever you want for your desired frequency.
Finally, apply the rest procedures mentioned in the article. I hope this may solve your issue.
Bishawajit, on behalf of ExcelDemy
Hi this is amazing! Thank you very much.
I wanted to ask if it’s possible to import in only one sheet the historical data for more than one ticker. Instead of creating 50 different sheets for each ticker, I want one sheet where it importsthe price for 50 tickers? Is this possible? How can I do this?
Hi NICOLÁS,
Thanks for following our article. Please go through the whole article and create the template. And, you can use one ticker for only one sheet to import stock prices from any other website.
Let us know if your problem is fixed.
Regards,
Bishawajit Chakraborty
Author at ExcelDemy
Thank you very much, this will help me alot if I can solve the issue with the following msg
Expression.Error: The name ‘Ticker’ wasn’t recognized. Make sure it’s spelled correctly.
Please write Ticker correctly both in your xlsx file and the code. Do not try to give invalid ticker or outdated ticker. Provide problems in detail for better service please.
This is really amazing,
Now I need your generous advice as I tried to copy the same to new sheets from the same workbook but everytime it reads only from the first sheet.
I need this code to download at least 10 different tickers in different sheets of the same workbook
First, you have to create different power queries for each worksheet for other tickers and copy the same code. Otherwise, it triggers the first. Then for 10 different tickers,the code is
this code creates 10 tables for 10 different tickers. You have to select one table according to Ticker and hover over the query in Workbook Query Section and press View in Worksheet. You will get the table have to create 10 sheets separately.