How to Import Stock Prices into Excel from Yahoo Finance

Illustrate 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.

What Is a Historical Stock Price?

This article will illustrate how to import historical stock prices into Excel from Yahoo Finance. Yahoo! Finance was first introduced by Yahoo! Network. 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.

How to Import Historical Stock Prices into Excel from Yahoo Finance: 3 Easy Steps

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:

• Press Enter.
• The above command converts the date of cell K4 into a timestamp.

• Similarly, insert the following formula in cell L5:

• 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.

Download Free Template

You can download the free template from here.

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 Get Stock Quote with Excel Add-in
• How to Get Live Stock Prices in Excel
• How to Get the Current Stock Price of India in Excel

<< Go Back to Stocks In Excel| Excel for Finance | Learn Excel