Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Yes. Power Query can import and clean historical stock data, then load it into an Excel table; Excel’s chart tools turn that table into a close-price, OHLC, candlestick-style, or volume stock chart. Power Query does not supply stock prices by itself: you need a CSV, workbook, accessible web/API source, or database. The refreshable workflow is data source → Power Query → Excel table → stock chart.
What you need before you start
Use a source that provides one row for each trading period—daily, weekly, or monthly—with consistent fields. The minimum for a closing-price chart is Date and Close. For an OHLC or candlestick-style chart, use Date, Open, High, Low, and Close. Add Volume for a chart that includes trading volume.
| Date | Open | High | Low | Close | Volume |
|---|---|---|---|---|---|
| 2026-01-02 | 100.25 | 103.10 | 99.80 | 102.75 | 1250000 |
The example is a layout illustration, not market data. Keep the chart fields together in the expected order: Date, Open, High, Low, Close, or Date, Volume, Open, High, Low, Close for the volume subtype. The exact stock-chart labels can vary by Excel edition, so choose the subtype whose name and preview correspond to the selected columns.
Before importing, decide whether your close field is raw or adjusted. Providers may supply raw close, split-adjusted prices, or prices adjusted for both splits and dividends. These produce different historical charts. Use the field that matches your purpose and label it in the workbook; do not silently substitute one for another.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
Import historical prices with Power Query
A downloaded CSV is a reliable starting point because it avoids dependence on a finance website’s page layout. In modern Excel, use the Data tab’s Get Data or Get & Transform Data controls. Microsoft’s Power Query overview describes the tool as a way to connect to, transform, load, and refresh external data.
- In Excel, select Data → Get Data → From File → From Text/CSV.
- Select the downloaded price file and review the delimiter, header detection, and preview.
- Select Transform Data to open the Power Query Editor rather than loading unreviewed data.
For a workbook source, choose the workbook connector from Get Data and select the relevant sheet or table. For a web source, the usual route is Data → Get Data → From Other Sources → From Web; exact labels differ across Excel versions and platforms. Microsoft documents these import routes in Import data from data sources using Power Query.
A visible table on a finance website is not necessarily a usable or stable source. Pages may be rendered by JavaScript, require sign-in, limit requests, block automated access, or change structure. Prefer a provider’s downloadable CSV or documented API. Check its authentication, rate limits, and permitted use; do not try to bypass access controls.
Clean and verify the query
In Power Query, remove title and footer rows, blank rows, repeated headers, and API metadata. If the source contains more than one security, filter its ticker field to the security you intend to chart. Promote the first row to headers only if it actually contains field names, then rename the relevant columns to clear, consistent names: Date, Open, High, Low, Close, and, if available, Volume.
Recommended Free Tools
- Set
Dateto the Date type. If the source uses a locale-specific format such as day/month/year, use Change Type → Using Locale and verify several rows against the original file. - Set OHLC fields to a numeric type and volume to a whole-number type when appropriate. Automatic type detection can be wrong, so inspect the result. Remove currency symbols or separators only when necessary and in a way that preserves the numeric value.
- Replace provider placeholders such as
N/Awith nulls where appropriate, then remove rows with errors or missing chart-critical values. - Sort by Date in ascending order. Check for duplicate dates for the same ticker and interval before charting.
As a practical validity check, High should be at least as large as Open, Low, and Close; Low should be no greater than those values. These checks help find a mis-mapped column or bad source row, but Excel does not enforce them as financial-data rules.
If you want to inspect or adapt the transformation, this illustrative M query reads a local CSV. Replace the path and column names to match your file; an API or web source needs a different source step.
let
Source = Csv.Document(
File.Contents("C:\Data\stock-history.csv"),
[Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
),
PromotedHeaders = Table.PromoteHeaders(
Source,
[PromoteAllScalars = true]
),
ChangedTypes = Table.TransformColumnTypes(
PromotedHeaders,
{
{"Date", type date},
{"Open", type number},
{"High", type number},
{"Low", type number},
{"Close", type number},
{"Volume", Int64.Type}
}
),
RemovedErrors = Table.RemoveRowsWithErrors(
ChangedTypes,
{"Date", "Open", "High", "Low", "Close"}
),
SortedRows = Table.Sort(
RemovedErrors,
{{"Date", Order.Ascending}}
)
in
SortedRowsThis example assumes the source already has the stated headers and a date format Power Query can interpret. If dates or numbers are misread, set their types using the appropriate locale instead of relying on automatic detection. Microsoft discusses culture and numeric-interpretation considerations in its Excel.Workbook connector documentation. Floating-point values can also display extra decimal digits; formatting a cell changes its display, while rounding changes the underlying value.
Load the data into an Excel table
When the preview is correct, select Home → Close & Load To and load the result as a Table on a worksheet. A table makes the query output inspectable and gives the chart a source that can grow as refreshed data adds rows. Keep the query’s source path or connection settings stable if this workbook will be reused.
Create a close, OHLC, or volume stock chart
Select only the fields required for the intended chart, including the date column, then choose Insert and the stock-chart options. Use the following field selections as a guide; Excel’s displayed subtype names may differ.
| Chart purpose | Select columns in this order | What it shows |
|---|---|---|
| Closing-price trend | Date, Close | Closing price over time |
| OHLC or candlestick-style price chart | Date, Open, High, Low, Close | Opening, high, low, and closing prices for each period |
| Price with volume | Date, Volume, Open, High, Low, Close | Trading volume together with the OHLC values |
Do not select every column indiscriminately. Ticker, adjusted-close, dividend, split, or metadata fields can prevent the desired subtype from appearing or produce an unintended chart. If the chart options are unavailable, first try the two-column Date-and-Close selection, then check that dates and prices are genuine date and numeric values and that there are no blank or error rows.
Excel stock charts visualize the data supplied for each period; they do not predict future prices. For presentation, give the chart a specific title such as AAPL Daily OHLC, label the price axis with its currency, and make the chart wide enough that date labels remain legible. Use actual trading dates rather than adding zero-value weekend or holiday rows. If Excel treats dates as ordinary labels or spaces them oddly, review the horizontal-axis date settings.
Refresh the query and keep the chart current
After the source file is updated or the data service is available again, select Data → Refresh All. The query retrieves and transforms records, the worksheet table receives the result, and the chart reads that table. Check the refreshed table for the expected dates before relying on the chart.
Free tools Windows power users keep installed
One-click scans. No signup required.
Best Value
Build the chart from the query-loaded table rather than a fixed cell range. A fixed range may omit newly added rows even when the query refresh succeeds. If the chart does not change, check whether the query refreshed, whether the new records survived its filters, and whether the chart source still points to the table. Refreshing is a triggered or configured data operation, not continuous streaming of market prices.
For an operational workbook, record the data provider and whether prices are adjusted, keep the source location stable, and consider displaying a last-refreshed time. These details help distinguish a stale workbook from a current one.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Fix common stock-chart problems
| Symptom | Likely cause | What to check |
|---|---|---|
| Stock-chart subtype is unavailable | Wrong number or order of fields; dates or prices are text; unrelated or blank columns were selected. | Select only the required columns in the expected order, verify Power Query types, and remove blank/error rows. Test with Date and Close first. |
| Chart looks reversed or implausible | Open, High, Low, or Close was mapped incorrectly, or values were interpreted using the wrong locale. | Compare sample rows with the original provider data and verify High is not below Low; correct names or types in the query. |
| New dates do not appear after refresh | The query failed, filters excluded the rows, the source moved, or the chart uses a fixed range. | Inspect the refreshed table, check filters and source settings, then confirm the chart references the table. |
| Power Query cannot read the web source | The page is script-rendered, authenticated, rate-limited, blocked, or its structure changed. | Use an official download or documented API and check its credentials and limits. |
| Dates shift or parse incorrectly | Locale ambiguity, text dates, or timestamps and time zones are being handled as simple dates. | Apply the correct locale, validate representative rows, and retain timestamps until their time-zone meaning is clear. |
| Displayed prices have extra digits | Numeric precision is visible in the display. | Format display precision as needed; do not round source values unless the analysis calls for it. |
Power Query or STOCKHISTORY?
STOCKHISTORY is a formula-based alternative for supported Excel environments when a historical series is all you need. Microsoft identifies it as the route for historical financial data, while the Stocks linked data type supplies connected stock fields. Check the current Microsoft documentation for Stocks, geography, and historical data for availability and requirements; these connected features are not universal across Excel editions or accounts.
| Choose | When it fits |
|---|---|
| Power Query | You need to clean or combine CSV, workbook, API, web, or database data and repeat those transformations on refresh. |
STOCKHISTORY |
You have access to the function, want a formula-based historical series, and do not need a more involved import pipeline. |
| A dedicated finance platform or licensed feed | You need intraday coverage, defined redistribution rights, provider support, or data capabilities beyond a spreadsheet workflow. |
Platform and data limitations
Power Query is integrated into modern Excel editions, but connector and refresh behavior is not identical across Windows, Mac, and Excel for the web. Review Microsoft’s Power Query availability overview and version-specific data-source limits for your platform, particularly if the workbook relies on web sources, cloud locations, gateways, or the Data Model.
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallMicrosoft’s linked Stocks data type is distinct from a Power Query import, and some Excel features may not work as expected with linked data types. See Microsoft’s linked data types FAQ and tips before building a workflow around them. Microsoft also says stock information is delayed, provided as-is, and not intended for trading purposes or advice in its stock quote guidance.
For any provider, verify the data’s delay, history, adjustments, exchange coverage, API limits, and licensing. A workbook refreshed on demand is not a real-time market-data system, and a chart should not be treated as investment advice or as a substitute for a provider’s data terms.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
