Early access is open. Free sample datasets are live now. Download a sample

Learn

How to Get Historical Stock Data into Excel

6 min read · Updated 19 November 2026 · By the marketdata.co data team

Excel is still the fastest way to eyeball a price history, check a number, or hand a table to someone who doesn't code. The trouble starts on import: dates turn into text, timestamps lose their time zone, and adjusted and raw prices get mixed up. Here is a clean way to get historical stock data into Excel and keep it correct.

Datasets

What a good file looks like before you open it

Daily bars in our schema have one row per symbol per day:

symbol, date, open, high, low, close, volume, vwap, unadjusted_close, adjusted_close, dividend, split_factor

Three columns cause most of the trouble:

  • date is YYYY-MM-DD. Excel usually reads this correctly, but some regional settings swap day and month.
  • close, unadjusted_close and adjusted_close. unadjusted_close is what printed that day. close is adjusted for splits only. adjusted_close is back-adjusted for splits and dividends. Returns and long-term charts should use adjusted_close; anything that compares to real traded prices should use unadjusted_close.
  • symbol is on every row, so you can stack several stocks in one sheet and filter.

Worked example (illustrative numbers)

Say the sheet has a stock that closed at $200.00 on one day and $203.50 the next, with no dividend between. The daily return is 203.50 / 200.00 - 1 = 1.75%. If the stock had paid a $1.00 dividend with an ex-date on the second day, the close return understates what a holder made, and the adjusted_close return gets it right. That gap is small on one day and large over ten years.

Details

Two ways to load it

Option 1: CSV with Power Query (no code)

  1. Download the CSV.
  2. In Excel, go to Data > Get Data > From File > From Text/CSV and pick the file.
  3. In the preview, set File Origin to UTF-8 and check that date shows as a date. If it shows as text, click Transform Data, select the column and set Data Type > Date. For intraday files with a timestamp like 2026-10-31T15:40:00Z, use Date/Time/Timezone so the UTC offset is kept.
  4. Click Close & Load. You get a proper Excel table that you can refresh later when the file changes.

Option 2: API straight into a sheet with Power Query

Use Data > Get Data > From Web and paste a request URL. The example below is the shape the API will take; it is available in early access, so treat it as a preview.

https://api.marketdata.co/v1/stocks/bars?symbol=MSFT&start=2024-01-01&end=2026-10-31&format=csv&apikey=YOUR_KEY

Compute returns

With the table loaded as Prices and sorted by symbol then date, add a column:

=IF([@symbol]=OFFSET([@symbol],-1,0), [@adjusted_close]/OFFSET([@adjusted_close],-1,0)-1, "")

The IF stops the formula from computing a return across two different symbols. Format the column as a percentage. For a cumulative return per symbol, divide the last adjusted_close by the first and subtract 1.

To practise before you have stock data, our free BTC and ETH daily bar file uses the same column layout for prices (open, high, low, close, volume, vwap). Its first row looks like this:

symbol interval timestamp open high low close volume
BTC-USDT 1d 2026-07-01T00:00:00Z 58624.71 61334 57800.19 60024 25093.44662

Source: Binance. Columns quote_volume, vwap, trade_count and taker_buy_volume are omitted here.

Watch out

Common mistakes

Opening the CSV by double-click. Excel guesses types on open and can drop leading zeros or reformat dates for good. Import through Power Query so you choose the types.

Mixing the three close columns. A chart of unadjusted_close across a stock split shows a fake crash. A backtest on close misses dividends. Decide which one each column needs before you build formulas.

Hitting the row limit. A worksheet holds 1,048,576 rows. Ten years of daily bars for 400 stocks fits; one year of 1-minute bars for one stock (about 98,000 regular-session rows) fits too, but a few dozen symbols won't. Load large files to the Data Model, or use Python.

Losing the time zone. If you strip the Z from UTC timestamps, Excel treats them as local time, and an intraday chart can shift by four or five hours against the New York session.

Details

Get the data

Daily stock bars as CSV, ready for Excel. What you'll get: split and dividend adjusted and raw prices side by side, one row per symbol per day, delisted stocks included in the full-universe file.

FAQ

Frequently asked questions

How do I get historical stock data into Excel?

Download a CSV of daily bars and import it with Data > Get Data > From Text/CSV, setting the date column type in Power Query. For data that updates, point Power Query at a web API that returns CSV and refresh the query.

Should I use close or adjusted close in Excel?

Use adjusted close for returns, performance and long-term charts, since it accounts for splits and dividends. Switch to the unadjusted close when you need the price that actually traded on a given day.

Can Excel handle intraday data?

Yes, for small amounts. One stock's 1-minute bars for a year fit on a sheet, but many symbols will pass the 1,048,576-row limit. Use the Data Model or Python for larger sets.

Why are my dates showing as text?

Excel didn't recognise the format under your regional settings. In Power Query, set the column's data type to Date, or to Date/Time/Timezone for ISO timestamps.

Keep reading