How to Download Historical Data from Yahoo Finance to Excel

Pulling historical stock data into a spreadsheet is the first step toward analyzing trends, testing investment strategies, or creating custom portfolio trackers. Yahoo Finance is a go-to source for this information, offering a wealth of free and accessible financial data. This guide will walk you through several methods for getting that data directly into Excel, from a simple manual download to a dynamic, auto-refreshing connection.

Why Use Yahoo Finance for Historical Stock Data?

While there are dozens of paid financial data services, Yahoo Finance remains incredibly popular for a few key reasons. It’s free, user-friendly, and offers extensive historical data for a wide range of securities, including stocks, ETFs, indices, and mutual funds. You can typically get decades of daily, weekly, and monthly price data, making it an excellent resource for anyone from students to serious independent investors.

Once you have this data in Excel, you can use pivot tables, build charts, calculate moving averages, backtest trading ideas, or run any other custom analysis you can think of. Let’s look at the best ways to bridge the gap between Yahoo Finance’s website and your Excel workbook.

Method 1: Manual CSV Download (The Simple, Reliable Way)

The most straightforward method for getting data is to download it as a CSV (Comma Separated Values) file. This is a universal file format that Excel opens perfectly. It's a quick, one-time pull that's ideal when you just need a snapshot of data for a specific period.

Step-by-Step Instructions:

Navigate to Yahoo Finance: Open a web browser and go to finance.yahoo.com.
Search for a Ticker: Use the search bar at the top to find the stock or ETF you want. For this example, let's use Apple Inc. (ticker: AAPL). Type "AAPL" into the search bar and select it from the dropdown. This takes you to its main summary page.
Open the Historical Data Tab: Look for the tabs below the company name and chart, such as "Summary," "Chart," "Statistics," and find "Historical Data." Click on it.
Set Your Parameters: This is where you customize the data you'll be downloading.
- Time Period: You can choose from presets like "1D," "5D," "1Y," "5Y," or "Max," or you can set a custom date range. Your ability to get specific data will depend on the asset's history.
- Frequency: Choose whether you want "Daily," "Weekly," or "Monthly" data points.
- Show: Make sure "Historical Prices" is selected.

Once you've set your desired time period and frequency, click the blue "Apply" button to update the data shown on the screen.

Download the Data: To the right of the "Apply" button, you’ll see a "Download" link. Click this, and your browser will download a file named something like AAPL.csv.
Open in Excel: Navigate to where your browser saved the file (usually your "Downloads" folder) and open it. Excel will automatically parse the file and place the data into columns.

Understanding the Downloaded Data

Your spreadsheet will have several columns. Here’s what they mean:

Date: The trading day, week, or month for that row of data.
Open: The price at the market's opening.
High: The highest price the stock reached during that period.
Low: The lowest price of the period.
Close: The price at the market's closing.
Adj Close: The adjusted closing price. This is often the most important column for analysis, as it accounts for dividends and stock splits, giving you a truer picture of an investment's return over time.
Volume: The number of shares traded during that period.

Method 2: Excel Power Query (The Smarter, Dynamic Way)

Manually downloading a CSV is great, but what if you want to update your data frequently without repeating all those steps? This is where Excel’s built-in Power Query tool comes in handy. With this method, you create a live connection to Yahoo Finance that you can refresh with a single click. This works for modern versions of Excel (2016 and newer, including Microsoft 365).

Step-by-Step Instructions:

Get the Page URL: Follow steps 1-4 from the manual method above. Navigate to the stock's "Historical Data" tab on Yahoo Finance and set the default date range you want. For this example, let’s go to the page for Microsoft (ticker: MSFT) and leave the time period as "1 Year."
Copy the URL: Copy the full URL address from your browser. It will look something like this: https://finance.yahoo.com/quote/MSFT/history?period1=...
Open Excel's "From Web" Connector:
- In a blank Excel workbook, go to the Data tab on the ribbon.
- In the "Get & Transform Data" section, click "From Web."
Paste the URL: A dialog box will appear. Paste the URL you copied from Yahoo Finance into the text field and click "OK."
Select the Data Table:Excel will analyze the web page and show a "Navigator" window. This window displays all the data tables it found on that page. You may see several options, like "Document," "Table 0," and "Table 1."
- Click on each table one by one until you see a preview of the historical stock prices you recognize. It is usually "Table 1" for Yahoo Finance's current layout.
- Once you've found the correct one, select it.
Load the Data:At the bottom of the Navigator window, click the "Load" button. Excel’s Power Query engine will work for a moment and then import the data into a new worksheet, neatly formatted as an Excel Table.

The Magic of Refreshing

The biggest benefit of using Power Query is how easy it is to update your file. As new trading data becomes available, you don't have to repeat the import process. Simply do one of the following:

Go to the Data tab and click the "Refresh All" button.
Right-click anywhere inside the imported data table and select "Refresh."

Excel will go back to the Yahoo Finance page, grab the latest data according to your original query, and update your table automatically. If you build charts or formulas that reference this table, they will all update instantly, saving you enormous amounts of time.

Method 3: VBA (For Automation and Advanced Control)

For those comfortable with a little bit of code, you can use Excel’s Visual Basic for Applications (VBA) to create a highly customized data downloader. This method offers the most control and allows you to build sophisticated tools - for example, a button that pulls data for a list of tickers you have in a column.

This approach works by constructing a specific download URL that directly retrieves a CSV and using that within a VBA script. The URL format is as follows:

https://query1.finance.yahoo.com/v7/finance/download/{TICKER}?period1={START_DATE}&period2={END_DATE}&interval=1d&events=history

The key challenge is that period1 and period2 need to be Unix timestamps (the number of seconds since January 1, 1970). Here’s a basic script you can adapt.

How to Use VBA to Download Data:

Open the VBA Editor: In Excel, press Alt + F11. This will open the VBA editor window.
Insert a New Module: In the editor, go to Insert > Module. A blank code window will appear.
Paste the Code: Copy and paste the following VBA code into the module.
Run the Macro:

Close the VBA editor by pressing Alt + F11 again.
In your Excel sheet, go to the Developer tab (enable it if it's not visible).
Click Macros, select GetYahooFinanceData, and click Run.

This script will prompt you for a ticker symbol and a start date. It will then automatically create a new worksheet, name it, and pull in the historical data.

Final Thoughts

Each of these methods provides a reliable way to get historical data from Yahoo Finance into Excel, allowing you to focus on your analysis rather than on data collection. You can choose the simple CSV download for quick one-off tasks, use the refreshable Power Query connection for ongoing projects, or build a powerful VBA macro for ultimate control and automation.

Pulling financial data is just one piece of the puzzle. At a certain point, manually managing data from different marketing platforms, sales tools, and financial sites becomes a full-time job. That is exactly why we built Graphed. Our platform connects directly to dozens of sources like Google Analytics, Shopify, Salesforce, and your ad accounts, putting all your business data in one place and turning manual reporting chaos into a thing of the past.