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:
dateisYYYY-MM-DD. Excel usually reads this correctly, but some regional settings swap day and month.close,unadjusted_closeandadjusted_close.unadjusted_closeis what printed that day.closeis adjusted for splits only.adjusted_closeis back-adjusted for splits and dividends. Returns and long-term charts should useadjusted_close; anything that compares to real traded prices should useunadjusted_close.symbolis 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)
- Download the CSV.
- In Excel, go to Data > Get Data > From File > From Text/CSV and pick the file.
- In the preview, set File Origin to UTF-8 and check that
dateshows as a date. If it shows as text, click Transform Data, select the column and set Data Type > Date. For intraday files with atimestamplike2026-10-31T15:40:00Z, use Date/Time/Timezone so the UTC offset is kept. - 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.
- Historical daily stock data, available in early access. Suggested price for the full US universe file: $149 one-off.
- A free stock sample file is available on request.
- Practise now with BTC and ETH daily bars (CSV). Source: Binance
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