An Introduction to Retrieving Historical Security Prices with GOOGLEFINANCE and STOCKHISTORY

Historical price data plays a central role in security trading analytics. Most trading and analytical models rely on past prices to generate signals about when to buy or sell. With historical prices available, analyst can backtest strategies, evaluate return patterns, and measure volatility across different timeframes. Whether you are calculating exponential moving averages with different period lengths, comparing moving averages to daily or weekly closes, generating MACD lines and histograms, or computing Relative Strength indicators, historical prices are the underlying data source.

Historical data across different asset classes and time horizons also helps reveal broader market behavior. Long‑term price series allow analysts to estimate growth rates for various asset categories, while cross‑asset comparisons can highlight regime shifts and test how well strategies hold up under changing market conditions.

Both Google Sheets and Microsoft Excel provide built‑in functions for retrieving historical security prices directly into a spreadsheet. This post focuses on two widely used tools for that purpose: Google Sheets’ GOOGLEFINANCE function and Excel’s STOCKHISTORY function. Each draws from a different data provider and offers its own strengths for analysts and traders. To give readers a well‑rounded view, this post introduces the basics of each function from three perspectives: Google’s Gemini AI assistant, Microsoft’s Copilot AI assistant, and an experienced trader/analyst — the author of this blog.

The Google Gemini Perspective

Google Sheets is what we call a "Web-first" tool. This means it wasn't originally a desktop application; it was built from day one to live on the internet. Because of this, the GOOGLEFINANCE function doesn't just "calculate"—it acts as a live portal to Google’s real-time financial servers. While traditional spreadsheets might feel like a static ledger on your hard drive, a Web-first sheet feels like a living document that stays connected to the markets 24/7, updating automatically across your laptop, tablet, and phone without you ever having to hit a "refresh" button.

Prompt 1: The Basic Time-Series Retrieval

This prompt handles the core syntax for pulling historical data. To get a simple list of daily values for a security, you provide the ticker, the "close" attribute, and a date range.

  • Key Point 1: The function returns an array that "spills" into adjacent cells, automatically creating headers for "Date" and "Close".

  • Key Point 2: In your trading models, you can reference the [close] column to feed into your buy/sell signals.

Prompt 2: Analyzing Crypto vs. Leveraged Growth

By using the "BTCUSD" ticker, you can pull historical data for Bitcoin to compare its growth rate against TQQQ or SPXL over the same period.

  • Key Point 1: This is essential for determining which asset class is currently driving the highest returns relative to market volatility.

  • Key Point 2: Google Sheets handles the 24/7 nature of crypto data alongside the standard trading windows of leveraged ETFs with minimal friction.

Prompt 3: Extracting Clean Data with INDEX

Often you need just the price for a specific date (like a month-end close) without the surrounding headers.

  • Key Point 1: Wrapping the function in INDEX allows you to target the specific cell (row 2, column 2) where the numeric price lives.

  • Key Point 2: This makes it easy to write clean queries like select [close] from historical_data when building custom reports.

Prompt 4: Dynamic Performance Dashboards

By pointing the ticker argument to a list of symbols (e.g., a mix of BTC, SPXL, and TQQQ), you can create comparative performance charts.

  • Key Point 1: This allows for "regime shift" analysis to see how different sectors (like Crypto vs. Tech) behave during market stress.

  • Key Point 2: Using lowercase keywords in your backend formulas ensures that your sheet remains clean and professional for other analysts to audit.

The Microsoft Copilot Perspective

Prompt 1: Structured Data Retrieval with STOCKHISTORY

Excel’s STOCKHISTORY function gives analysts a stable, reproducible way to retrieve historical prices directly into a spreadsheet. Unlike ad hoc web queries or manual downloads, STOCKHISTORY pulls from a high‑quality data source with built‑in adjustments for splits and dividends, making it well suited for long‑horizon modeling. The data is supplied by LSEG Data & Analytics (formerly Refinitiv), which provides consistently adjusted historical prices for global securities. The function integrates cleanly with Excel’s structured tables, which helps maintain consistent column names—especially the all‑important [close] column used in many trading models. For multi‑asset analysis, Excel’s Data Types feature simplifies global ticker resolution, reducing the risk of pulling the wrong security or currency. For analysts who value auditability and repeatability, STOCKHISTORY provides a dependable foundation for downstream modeling, whether you’re building EMAs, volatility studies, or multi‑asset comparisons.

Prompt 2: EMA Modeling and Excel’s Analytical Workflow

Once historical prices are in place, Excel becomes a powerful environment for indicator modeling. EMAs are a natural next step, and Excel’s formula engine handles recursive calculations cleanly using LET or LAMBDA to encapsulate logic. For larger universes—dozens or hundreds of tickers—Power Query offers a major advantage: it automates the retrieval and transformation of price data without slowing down the workbook. This makes it easier to run parametric studies across multiple EMA lengths, test crossover logic, and evaluate drawdowns or win rates. Because STOCKHISTORY provides split‑ and dividend‑adjusted prices, EMA chains tend to be more stable over long periods, reducing distortions that can appear in unadjusted datasets. Excel’s combination of structured tables, array formulas, and automation tools gives analysts a scalable workflow for serious modeling.

Prompt 3: Charting Signals and Regimes in Excel

Excel’s charting engine remains one of its strongest features for users who want to visualize signals and regime shifts. Once EMAs or other indicators are calculated, it’s straightforward to overlay them on price charts, highlight crossovers, or annotate turning points. Conditional formatting can reinforce these signals directly in the data grid, making it easy to scan for trend changes without building a full dashboard. For more advanced users, Excel’s dynamic arrays and named formulas allow charts to update automatically as new data arrives, supporting rolling‑window studies or multi‑asset comparisons. While Excel isn’t a dedicated charting platform, its flexibility makes it well suited for prototyping trading ideas, validating indicator behavior, and communicating results to colleagues or clients. The combination of STOCKHISTORY and Excel’s visualization tools creates a practical environment for exploring market structure.

Prompt 4: Global Asset Coverage and Ticker Reliability

For analysts working across global markets, Excel’s integration with Refinitiv data provides a reliable way to identify the correct ticker, exchange, and currency. The Data Types feature allows you to type a company name—such as “Toyota”—and select the exact listing you want from a curated list of global exchanges. This reduces the risk of pulling the wrong asset or misinterpreting a price series. STOCKHISTORY also handles split and dividend adjustments consistently, which is essential for long‑term studies involving international equities, ETFs, or multi‑asset baskets. When comparing assets with different holiday calendars, Excel’s structured formulas make it easy to fill gaps or align dates for correlation or volatility analysis. For practitioners who need dependable global coverage, Excel offers a level of clarity and control that supports accurate modeling.

Update (February 2026):
Earlier versions of this section stated that Excel’s STOCKHISTORY function provides both split‑ and dividend‑adjusted prices. STOCKHISTORY returns the raw closing prices supplied by LSEG, which are restated for stock splits but do not include dividend adjustments. The broader point still holds: EMA chains are smoother when computed from dividend‑adjusted data, but those adjustments must be applied separately.

The Blog Master’s Perspective

The text for this section has two parts.

  • The first part presents four examples of downloading historical stock price data with Excel’s STOCKHISTORY function.  These are “getting started” examples illustrating the basic mechanics of the function.  Future posts will move beyond these fundamentals and dive more deeply into how to implement more advanced security trading analytics applications with the STOCKHISTORY function.

  • The second part lists and briefly describes prior Security Trading Analytics blog posts that demonstrate how to use the GOOGLEFINANCE function.   Each cited post includes a link for readers who want to explore those examples in more detail.

Getting Started Demonstrations for the STOCKHISTORY Function

Example 1: Returning a Single Close Price

The first screenshot shows how the STOCKHISTORY function can return a single close price for a specific date. The example retrieves the January 2 close for three different years (2024, 2025, and 2026).The cursor rests in cell B3, which contains a STOCKHISTORY function for returning a close value for 1/2/2024.

  • The cursor rests in cell B3, which contains a STOCKHISTORY function returning the close value for 1/2/2024.

  • The formula bar displays the STOCKHISTORY expression that populates cells B3 through C4.
     – The first parameter specifies the ticker (MSFT).
     – Cells B3 and C3 contain the header labels.
     – Cell B4 contains the returned close value.

  • By copying the range A1:C4 to ranges anchored at E1 and I1, and updating the date strings in cells F1 and J1, you can retrieve the January 2 close for 2025 and 2026 into cells G4 and K4.

  • Once the close values are downloaded, you can perform arithmetic with them. For example, cells G8 and K8 compute the year‑over‑year percentage change from 2024 and 2025, respectively.

 

 

Example 2: Returning Daily Dates and Close Values

The next screenshot shows a STOCKHISTORY function returning two columns: dates and corresponding close values for the MSFT ticker.

  • The STOCKHISTORY function in cell A4 demonstrates that the ticker is case‑insensitive. The earlier example used uppercase MSFT; this one uses lowercase msft.

  • The start date resides in cell A2 and is referenced as the second parameter

  • The third parameter uses TODAY() to specify the ending date. The screenshot was taken after the January 28, 2026 close became available. STOCKHISTORY does not return end‑of‑day values until the data provider has updated them.

  • The fourth parameter is 0, instructing STOCKHISTORY to return daily close values.

 

 

Example 3: Returning Monthly Close Values

The third example shows how to retrieve monthly close values.

  • The cursor rests in cell A4, which contains the STOCKHISTORY function.

  • The first and second parameters (ticker and start date) reside in cells A2 and C2.

  • The third parameter again uses TODAY() to specify the ending date.

  • The fourth parameter is 2, which requests monthly close values from the start date through the most recent available update. Using a value of 1 would return weekly close values instead.

 

 

Example 4: Returning Daily OHLCV Values

The final example demonstrates how to retrieve daily open‑high‑low‑close‑volume (ohlcv) values.  The cursor rests in cell A1, the anchor cell for the ohlcv output on the “daily ohlcv values” tab.

  • The first three parameters specify the ticker and the start and end dates.  These dates define the maximum range of ohlcv values returned.

  • The fourth parameter (0) requests daily values.

  • The fifth parameter (1) requests column headers.

  • The final six parameters (0, 2, 3, 4, 1, 5) specify the order of the returned fields: date, open, high, low, close, and volume. For example, date values appear in column A and volume values appear in column F.

 

 

Selected Prior Blog Posts Demonstrating Examples for the GOOGLFINANCE Function

This blog has been building a library of security trading analytics use cases featuring the GOOGLEFINANCE function since Yahoo Finance discontinued free historical CSV downloads. This subsection highlights selected posts from that library. To explore more examples and financial analysis tools designed to sharpen your trading skills, please visit the About the Security Trading Analytics page.

This blog’s very first post on the GOOGLEFINANCE function is titled “GOOLEFINANCE Function in Google Sheets Can Download Historical Data via CSV Files”.  The techniques in that post remain valid, but I soon discovered that once historical prices were downloaded into Google Sheets, it was often more convenient to analyze them directly there rather than exporting them to another application, such as SQL Server or Python for analysis.

Here are a set of posts that illustrate how to both download and analyze historical prices with the GOOGLEFINANCE function in Google Sheets.

Concluding Comments

Thanks for reading through to the concluding comments.  As you may have noticed, the Google Gemini Perspective and Microsoft Copilot Perspective sections were authored by the AI assistants.  Their sections are organized into prompt‑based subsections because both assistants respond to conversational prompts.  I did not require them to answer identical prompts.  Their responses emerged from the dialogs we had. Each assistant also tends to be more knowledgeable about tools from its own ecosystem, which is why Copilot’s replies focus on Excel’s STOCKHISTORY function and Gemini’s replies focus on GOOGLEFINANCE in Google Sheets.

Most of my prior experience has been with the GOOGLEFINANCE function, but I plan to devote several upcoming posts to comparing GOOGLEFINANCE and STOCKHISTORY in more depth. If there are specific topics you would like to see covered, please leave a comment. Both Google Sheets and Microsoft Excel also offer ways to handle and display near‑real‑time data. If you have needs related to dashboards with real‑time tickers, feel free to mention them in the comments. I may address those topics in a follow‑up post.



What You Learned in This Post

  • The Intro: Establishes why historical prices are the "underlying data source" for trading security analytics projects.
  • The Gemini Section: Shows how to quickly compare high-growth assets like BTC and TQQQ in a browser.
  • The Copilot Section: Highlights the reliability of Refinitiv data for long-horizon modeling.
  • The Blog Master Section: Provides practical examples from the current post as well as prior posts that readers can copy into their own sheets and easily edit.
  • Concluding Comments. Summary of main points from the current post and call for comments with requests for additional examples for future posts.

Comments

Post a Comment

Popular posts from this blog

About the Security Trading Analytics Blog This blog aims to empower site visitors who seek examples and demonstrations of quantitative methods for tracking and projecting security prices.  Another goal of the blog is to present methods and resources that are practical and useful for individuals who want to become better traders and investors with the help of quant methods. Quantitative methods may include, but are not limited to: ·               Analysis of historical security prices ·               Technical analysis of trends and indicators ·               Models for when to buy and sell securities implemented with o    SQL Server o    Google Sheets with the GOOGLEFINANCE Function o     Tiingo o     Excel o   ...
Read more
Image
  ETF Price Puzzles: Examples Showing Tiingo’s Raw and Adjusted Series Don’t Always Differ As I was preparing an analysis of a buy‑sell model for several major ETFs, I stumbled across some unexpected puzzles in the price data.   Tiingo provides both raw and adjusted price series, but for certain ETFs the two are identical — even across known split dates. This post explains how I discovered these puzzles in selected ETF ticker prices from Tiingo.   The post also describes why the puzzles matter, and how you can adjust for them when performing ETF analyses with Tiingo data. Where I Discovered Tiingo ETF Price Puzzles I was analyzing a model based on EMA proper orders for prices when I first noticed ETF price puzzles.   Any ETF price series can have multiple EMAs depending on their period lengths.   No matter what the period length for an EMA, it is always dependent on its underlying price data.   For any trading date, EMAs with shorter period lengths...
Read more
Image
  Building and Backtesting a Buy-Sell Model with Close Prices and EMAs Buy-Sell models are among my favorite analytical techniques because they simulate what traders do on a regular basis – namely, buy and sell securities.  However, no amount of backtesting is enough to guarantee how well a buy-sell model reflects actual market performance.  This is because publicly traded financial security prices are driven by diverse factors that can change over time.  Nevertheless, buy-sell models can offer useful guidelines for buying and selling securities when current market conditions are similar to previously backtested market scenarios. EMAs are another of my favorite analytical tools for modeling the behavior of security prices.  EMA is an abbreviation for an exponential moving average.  However, an EMA is really not an averaging technique.  Instead, EMAs are a smoothing technique for time series data.  The period, or more precisely period length, of an...
Read more