How to Import Stock Prices into Excel from Yahoo Finance

Get FREE Advanced Excel Exercises with Solutions!

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

18 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

  3. Hi Mukesh- what changes to the code are necessary to change the interval from daily to weekly? Thank you, Brian J

    • Reply Avatar photo
      Bishawajit Chakraborty Jan 17, 2023 at 4:31 PM

      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.

        • Hello JACK MACEY,

          I understand you are facing issues while creating the weekly and monthly sheets using the same given code. The provided code works perfectly for Daily frequency. But for Weekly and Monthly intervals, you have to adjust the parameter of the frequencies in the code accordingly.

          In this line below, we have declared the parameter of the frequency.

          "https://query1.finance.yahoo.com/v7/finance/download/"&Ticker&"?period1"&StartDate&"&period2"&EndDate

          We have to add the interval=1wk or interval=1mo parameters to the URL, which specify the Weekly and Monthly intervals.

          Here are the modified codes.

          For Weekly frequency,

          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&"?interval=1wk&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"

          And for Monthly frequency,

          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&"?interval=1mo&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"

          Try this way. And, let me know if it works.

          Regards,
          Yousuf Khan Shovon

        • Thank you for taking the time to respond Sir. Unfortunately, when I follow the instructions, select monthly, then copy the link address to input into the code, the sheet always loads prices for every day. I’m not sure what to change to fix it. The link I copy from yahoo finance is for the monthly frequency.
          Thank you again for your expertise in helping to solve this issue.

        • Avatar photo
          Bishawajit Chakraborty Feb 22, 2023 at 3:12 PM

          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

  4. 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?

    • Reply Avatar photo
      Bishawajit Chakraborty Feb 22, 2023 at 3:52 PM

      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

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

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

      
      let
          TickerList = {"AAPL", "GOOG", "MSFT", "AMZN", "FB", "TSLA", "NVDA", "JPM", "BAC", "V"},
          StartDate = Excel.CurrentWorkbook(){[Name="StartDate"]}[Content]{0}[Column1],
          EndDate = Excel.CurrentWorkbook(){[Name="EndDate"]}[Content]{0}[Column1],
          #"Loop Through Tickers" = List.Transform(TickerList, each
              let
                  Ticker = _,
                  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"
          ),
          #"Loop Through Tickers1" = #"Loop Through Tickers"{8}
      in
          #"Loop Through Tickers1"

      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.

  7. I have a question regarding max and min function used in query. Like how to fetch only maximum value in particular column instead of whole table. Is there any way around?

  8. I have a question about how to include max or min function in query. I want maximum value of close price in defined period instead of whole table. Is there any way to find max value?

    • Reply Avatar photo
      Lutfor Rahman Shimanto May 21, 2023 at 2:18 PM

      Hello PCLOUD

      Greetings from our website! Thank you for sharing your question on the platform. There is a way to resolve your submitted query of finding the maximum value of the Close price in a defined period instead of the whole table. I can assist you with an Excel PowerQuery code to reach your goal.

      PowerQuery Code:

      let
          Source = Csv.Document(Web.Contents("https://query1.finance.yahoo.com/v7/finance/download/MSFT?period1=1589932800&period2=1590710400&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}}),
          MaxClosePrice = List.Max(#"Changed Type"[Close])
      in
          MaxClosePrice
      

      The line MaxClosePrice = List.Max(#”Changed Type”[Close]) calculates the maximum value of the Close column in the transformed table. The result is assigned to the variable MaxClosePrice.

      I hope this will achieve your goal. I am also giving you the Workbook used to investigate your issue to help you understand better. Feel free to contact us again with any other inquiries or concerns.

      WORKBOOK:
      DOWNLOAD WORKBOOK

      Regards
      Lutfor Rahman Shimanto
      ExcelDemy Team

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo