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

What Is the Historical Stock Price?

The historical stock price is the stock price in a specific date in the past. Historical prices are used to analyze stock decisions.

STEP 1 – Create a Query

Create a query to import historical stock prices from Yahoo! Finance of a specific company. Here, Microsoft’s historical stock prices for the last 3 months.

• Go to Yahoo! Finance.
• Search for Microsoft in the search bar.

• Go to ‘Historical Data’.

• Set the Time Period: 3M.

• Right-click the Download option.
• Select ‘Copy link address’.

• Open a new Excel sheet.
• Go to Data.
• Click ‘From Web’.

• A new dialog box will open.
• Paste the link in the URL field.
• Click OK.

• A data preview is displayed.

STEP 2 – Fix the Variables

Set a named range to import historical stock prices into Excel.

You want to import data for ticker symbol, start date, and end date. Create 3 named ranges (Ticker, StartDate, and EndDate).

• Go to the worksheet containing the data query.
• Create fields for Ticker, StartDate, and EndDate as shown below.

• Select L4. Enter the following formula:

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

• Enter the following formula in L5:

• Press Enter.
• This is the output.

• Set Ticker to J2.

• Set StartDate to L4.

• Set EndDate to L5.

STEP 3: Import Stock Prices in a Dynamic Way

To create a dynamic template, update ticker, StartDate, and EndDate and get the historical stock prices for these parameters.

• Go to Query.
• Select Edit.

• A new dialog box will open.
• Go to the Home tab and select ‘Advanced Editor’.

• In ‘Advanced Editor’ the following code will be displayed:

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"

• Add Tickers, StartDate, and EndDate to the code:

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"

• Click Done.

• Adjust the query code as shown in the highlighted part of the above image.
• Click ‘Close & Load’.

• The code will be updated in the worksheet.
• Set Ticker value to AAPL. Ticker represents Apple, here.

• Go to the Data tab and click Refresh All.

• See the historical stock prices for Apple.

Download Free Template

Download the free template here.

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