How to Import Stock Prices into Excel from Yahoo Finance

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.

Create Query to Import Historical Stock Prices into Excel from Yahoo Finance

  • In addition, go to the ‘Historical Data’ tab.

Create Query to Import Historical Stock Prices into Excel from Yahoo Finance

  • Furthermore, set the Time Period span 3M.

Create Query to Import Historical Stock Prices into Excel from Yahoo Finance

  • Then, right-click on the Download option.
  • Select the option ‘Copy link address’.

Create Query to Import Historical Stock Prices into Excel from Yahoo Finance

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

NOTE: The above image shows a part of the full dataset. If we scroll we will see more data. You can download the free template with this article to get access to the full data.

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.

Fix Variables for Importing Historical Stock Prices into Excel

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

Fix Variables for Importing Historical Stock Prices into Excel

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

Fix Variables for Importing Historical Stock Prices into Excel

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

Import Stock Prices into Excel from Yahoo in a Dynamic Way

  • A new dialogue box will open.
  • Secondly, go to the Home tab and select the option ‘Advanced Editor’.

Import Stock Prices into Excel from Yahoo in a Dynamic Way

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

Import Stock Prices into Excel from Yahoo in a Dynamic Way

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

Import Stock Prices into Excel from Yahoo in a Dynamic Way

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

Import Stock Prices into Excel from Yahoo in a Dynamic Way

  • 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

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

3 Comments
  1. Very useful and clear tutorial.

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

Leave a reply

ExcelDemy
Logo