--- title: "GOOGLEFINANCE in Google Sheets: syntax and attributes list" canonical: "https://www.owox.com/blog/articles/googlefinance-function-google-sheets" updated: "2026-10-05" --- # GOOGLEFINANCE function in Google Sheets: syntax, attributes list and examples GOOGLEFINANCE pulls current and historical prices of stocks and currency pairs into Google Sheets. This guide covers the syntax, the complete list of attributes, historical data, exchange rates, candlestick and sparkline charts, the limits Google documents, and the reasons the function returns #N/A. [Google Sheets Tips](/blog/topics/google-sheets-tips) · Updated October 2, 2026 · [Vadym Kramarenko](/team/vadym-kramarenko) · 12 min read ![GOOGLEFINANCE function in Google Sheets: syntax, attributes list and examples](https://cdn.owox.ai/www/webflow/6a19998efc5b9ef8fd2dc2e0_UNLOCK-FINANCIAL-INSIGHTS.png/public) The GOOGLEFINANCE function returns current or historical market data for a stock or a currency pair. The shortest form needs only a ticker: > `=GOOGLEFINANCE("NASDAQ:GOOG")` It returns the current price, which can be delayed by up to 20 minutes. Add an attribute to ask for something else, and dates to get history: | You need | Formula | | --- | --- | | Current price | `=GOOGLEFINANCE("NASDAQ:AAPL", "price")` | | Another current value, for example the 52-week high | `=GOOGLEFINANCE("NASDAQ:AAPL", "high52")` | | Closing prices for a period | `=GOOGLEFINANCE("NASDAQ:AAPL", "close", DATE(2024,5,20), DATE(2024,5,31))` | | The closing price on one day, as a single value | `=INDEX(GOOGLEFINANCE("NASDAQ:AAPL", "close", DATE(2024,5,20)), 2, 2)` | | An exchange rate | `=GOOGLEFINANCE("CURRENCY:USDEUR")` | ## GOOGLEFINANCE syntax > `=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])` * **ticker:** the symbol of the security, in quotes. Google asks for the exchange and the ticker together, “NASDAQ:GOOG” rather than “GOOG”. Without the exchange, the function picks one itself. This is the only required argument. * **attribute (optional):** which value to return, in quotes. The default is “price”. The attribute is required as soon as you pass a date. * **start\_date (optional):** the first day of historical data. With a start date and no end date, the function returns that single day. * **end\_date|num\_days (optional):** the last day of historical data, or a number of days counted from the start date. * **interval (optional):** how often to return a row of history: “DAILY” or “WEEKLY”. The numbers 1 and 7 mean the same; any other number returns #N/A. Two things in this syntax decide what you get back: * **No dates:** the request is real-time and the result is one value in one cell. * **Any date:** the request is historical and the result is a table with a header row (Date and the attribute), even for a single day. Use the historical attributes; “price” is also accepted and returns the closing price, while the other real-time attributes, such as “pe” or “marketcap”, return #N/A with a date. Write dates with the [DATE function](/blog/articles/date-functions-google-sheets), for example DATE(2024,5,20), or with [TODAY](/blog/articles/now-today-functions-google-sheets), for example TODAY()-30. ## GOOGLEFINANCE attributes list ### Real-time attributes | Attribute | Returns | | --- | --- | | “price” | Price quote, delayed by up to 20 minutes | | “priceopen” | Price at market open | | “high” | Current day’s high price | | “low” | Current day’s low price | | “volume” | Current day’s trading volume | | “marketcap” | Market capitalization | | “tradetime” | Time of the last trade | | “datadelay” | How far the real-time data is delayed | | “volumeavg” | Average daily trading volume | | “pe” | Price/earnings ratio | | “eps” | Earnings per share | | “high52” | 52-week high price | | “low52” | 52-week low price | | “change” | Price change since the previous trading day’s close | | “changepct” | Percentage change since the previous trading day’s close | | “beta” | Beta value | | “closeyest” | Previous day’s closing price | | “shares” | Number of outstanding shares | | “currency” | Currency in which the security is priced | ### Historical attributes | Attribute | Returns | | --- | --- | | “open” | Opening price for the date or dates | | “close” | Closing price for the date or dates | | “high” | High price for the date or dates | | “low” | Low price for the date or dates | | “volume” | Volume for the date or dates | | “all” | All of the above, one column each | ### Mutual fund attributes are not supported Older lists of GOOGLEFINANCE attributes include a third group for mutual funds: returnytd, netassets, yieldpct, morningstarrating, expenseratio and others. [Google’s documentation](https://support.google.com/docs/answer/3093281) now states that mutual fund attributes are not supported in Google Sheets (checked in October 2026), so do not build a sheet on them. It also notes that some attributes do not return a result for every symbol. ## The Turning Point OWOX Data Marts See your first report built in real time. _15 minutes._ 1. Connect your data warehouse 2. Pick your metrics 3. Get a live Google Sheets report In the time it takes to write a ticket. Then imagine never writing that ticket again. [Book a Demo](/book-a-call) We'll use your actual use case ## Get current stock data To get several values for one company, keep the attribute names in a column and pass the cell as the second argument: > `=GOOGLEFINANCE("AAPL", C3)` * **“AAPL”:** the ticker of Apple Inc. * **C3:** the cell that holds the attribute name, here price. ![Google Sheets with a list of attributes in column C and the formula =GOOGLEFINANCE(“AAPL”, C3) in column F, returning the price, opening price, high, low, market capitalization, trade time, volume and average volume of Apple stock](https://cdn.owox.ai/www/webflow/67898855b056727092400bb6_52833.png/public) Copied down, the formula returns the price, the opening price, the day’s high and low, the market capitalization, the time of the last trade, the volume and the average volume. The same works in the other direction: tickers in a column, one attribute in the formula, `=GOOGLEFINANCE(B3, "price")`. One formula takes one ticker. A list such as “AAPL, GOOG” in a single formula returns #N/A. ## Get historical stock data Add a historical attribute and a start date. The fourth argument is either an end date or a number of days: > `=GOOGLEFINANCE(B3, "close", DATE(2024,5,20), 7)` * **B3:** the cell with the ticker, AAPL. * **“close”:** the closing price. * **DATE(2024,5,20):** the first day, May 20, 2024. * **7:** the number of days to cover, counted from the start date. ![Google Sheets with the formula =GOOGLEFINANCE(B3, “close”, DATE(2024,5,20), 7) in F3, returning a Date column and a Close column with Apple closing prices from May 20, 2024](https://cdn.owox.ai/www/webflow/6789885501ed09d61b5e2201_52835.png/public) The result is a table with two columns, Date and Close, and one row per trading day. Weekends and market holidays have no row: May 27, 2024 was Memorial Day, and the list goes from May 24 straight to May 28. For one row per week instead of one per day, add the interval: `=GOOGLEFINANCE("AAPL", "close", DATE(2024,5,1), DATE(2024,5,31), "WEEKLY")` returns the closing prices of the four Fridays from May 3 to May 24. To put one historical price into one cell, take the value out of the table with INDEX: `=INDEX(GOOGLEFINANCE("AAPL", "close", DATE(2024,5,20)), 2, 2)` returns 191.04, the second row and second column of the result. ## Build a candlestick chart A candlestick chart shows four prices for each period: open, close, high and low. The body of the candle spans the open and the close, and the thin lines above and below it reach to the high and the low. ### Step 1: get the data The “all” attribute returns everything the chart needs: > `=GOOGLEFINANCE("AAPL", "all", TODAY()-10, TODAY())` ![Google Sheets with the formula =GOOGLEFINANCE(“AAPL”, “all”, TODAY()-10, TODAY()) in C2, returning Date, Open, High, Low, Close and Volume columns for five trading days of Apple stock](https://cdn.owox.ai/www/webflow/678988567d521262d53380a8_53435.png/public) The formula returns the Date, Open, High, Low, Close and Volume columns for the trading days of the last 10 days. ### Step 2: add a text date The chart needs the dates as labels. Add a column next to the data and convert each date to text with the [TEXT function](/blog/articles/text-function-google-sheets): > `=TEXT(C3, "MMM DD YYYY")` Copy the formula down for all rows. ![Google Sheets with the formula =TEXT(C3, “MMM DD YYYY”) in column I, turning each date of the price table into a label such as Jun 24 2024](https://cdn.owox.ai/www/webflow/6789885533aae12744bb1834_53437.png/public) ### Step 3: insert the chart Select the data and choose **Insert > Chart**. In the chart editor, open the **Setup** tab and pick **Candlestick chart** as the chart type. The data range has to include the text date and the Open, High, Low and Close columns: the text date is the X-axis, the four prices are the series. ![Google Sheets with a candlestick chart titled AAPL Stock Price below the price table, one candle per trading day from June 24 to June 28, 2024](https://cdn.owox.ai/www/webflow/67898857793e29d61e9151f5_53439.png/public) If the candles look flat, open **Customize > Vertical axis** and set the minimum and maximum close to the lowest and highest prices in the data. More chart types are covered in the guide to [charts in Google Sheets](/blog/articles/charts-for-visualization-google-sheets). ## Get currency exchange rates For currencies the ticker is the word CURRENCY, a colon and two three-letter currency codes with nothing between them: the currency you convert from, then the currency you convert to. > `=GOOGLEFINANCE("CURRENCY:USDEUR")` With the codes in cells, join the parts with ampersands: > `=GOOGLEFINANCE("CURRENCY:" & B3 & C3)` ![Google Sheets with source currencies in column B, target currencies in column C and the formula =GOOGLEFINANCE(“CURRENCY:” & B3 & C3) in column F, returning the rates for USD to EUR, CAD to GBP and MXN to PLN](https://cdn.owox.ai/www/webflow/6789885525e9589883d38b6a_52839.png/public) Currencies have no trading day, so the open, high, low and volume attributes return #N/A for a currency pair. ### Convert an amount Multiply the amount by the rate: > `=GOOGLEFINANCE("CURRENCY:" & B2 & E2) * B3` ![Google Sheets with amounts in USD in column B and the formula =GOOGLEFINANCE(“CURRENCY:” & B2 & E2) \* B3 in column E, converting 100, 50 and 20 US dollars to euros](https://cdn.owox.ai/www/webflow/678988553f64bdf9dd570029_52841.png/public) B2 and E2 hold the currency codes, USD and EUR, and B3 holds the amount. The full list of currency codes and more conversion patterns are in the guide to [currency conversion with GOOGLEFINANCE](/blog/articles/googlefinance-currency-conversion-google-sheets). ### Get historical exchange rates Historical rates follow the same rules as historical prices: an attribute, a start date and an end date. > `=GOOGLEFINANCE("CURRENCY:" & B3 & C3, "price", DATE(2024,5,25), DATE(2024,5,31))` ![Google Sheets with the formula =GOOGLEFINANCE(“CURRENCY:” & B3 & C3, “price”, DATE(2024,5,25), DATE(2024,5,31)) in F3, returning a Date column and a Close column with the USD to EUR rate for each day from May 25 to May 30, 2024](https://cdn.owox.ai/www/webflow/678988567d521262d53380ab_52843.png/public) The function returns the USD to EUR rate for each day of the last week of May 2024. ## Show a price trend in one cell with SPARKLINE [SPARKLINE](/blog/articles/sparkline-google-sheets) draws a small chart inside a cell, and it accepts the table that GOOGLEFINANCE returns: > `=SPARKLINE(GOOGLEFINANCE(B3, "price", TODAY()-30, TODAY()), {"charttype", "line"; "color", "blue"; "linewidth", 2})` * **GOOGLEFINANCE(B3, “price”, TODAY()-30, TODAY()):** the prices of the ticker in B3 for the last 30 days. * **{“charttype”, “line”; “color”, “blue”; “linewidth”, 2}:** the chart options: a blue line, 2 pixels wide. ![Google Sheets with a list of tickers in column B and a SPARKLINE formula around GOOGLEFINANCE in column E, drawing a small blue line chart of the last 30 days of prices next to each ticker](https://cdn.owox.ai/www/webflow/6789885568e219c27eee643d_52845.png/public) Copied down next to a list of tickers, the formula gives a 30-day trend line for each of them. ## Limits of GOOGLEFINANCE [Google’s documentation](https://support.google.com/docs/answer/3093281) lists several limits that are worth knowing before you build a report on the function (checked in October 2026): * **Quotes can be delayed by up to 20 minutes** and are not sourced from all markets. The “datadelay” attribute shows the delay for a ticker. * **Most international exchanges are not supported.** * **Not for professional use.** The data is provided for information, not for trading, and professional use may require a licence from the data provider. * **Historical data stays in the sheet.** It cannot be read through the Sheets API or Apps Script: the cells return #N/A there. * **Dates are treated as noon UTC.** For exchanges that close before that time, the result can be shifted by a day. ## GOOGLEFINANCE errors ### #N/A error **⚠️ Error:** The function returns #N/A. This is its answer to almost every problem, so hover over the cell and read the message. ✅ **Solution:** Check these causes in turn: * **The ticker is not recognized.** Add the exchange, “NASDAQ:AAPL”, and check the symbol on Google Finance. * **The attribute is misspelled or not available** for this symbol. Compare it with the list above. * **A real-time attribute is used with dates.** With a date in the formula, attributes such as pe, marketcap or high52 return #N/A. Use open, close, high, low, volume or all. * **The date is not a trading day.** A single date that falls on a weekend or a market holiday returns #N/A. Use a range of dates instead. * **The interval is a number other than 1 or 7.** * **Several tickers are passed in one string.** Use one formula per ticker. To show something more useful than the error, wrap the formula: `=IFERROR(GOOGLEFINANCE(B3, "price"), "no data")`. ### #REF! error **⚠️ Error:** A historical formula returns #REF! with the message that the array result was not expanded. ✅ **Solution:** Historical data is a table and needs empty cells below and to the right of the formula. Clear those cells or move the formula. ### A date and a header where one number was expected **⚠️ Error:** A formula with a single date fills four cells: Date, Close and a row of values. ✅ **Solution:** That is how historical requests work. Take the one value with INDEX: `=INDEX(GOOGLEFINANCE("AAPL", "close", DATE(2024,5,20)), 2, 2)`. ### #ERROR! (formula parse error) **⚠️ Error:** The formula is not accepted at all. ✅ **Solution:** Check that the ticker and the attribute are in straight double quotes, that the parentheses are paired, and that the arguments are separated the way your spreadsheet locale expects: a comma in most English locales, a semicolon in many European ones. ## Related Google Sheets functions Functions that are often used next to GOOGLEFINANCE, each with its own guide: * [Currency conversion with GOOGLEFINANCE:](/blog/articles/googlefinance-currency-conversion-google-sheets) Currency codes, conversion formulas and historical rates in detail. * [SPARKLINE:](/blog/articles/sparkline-google-sheets) Draws a chart inside a single cell. * [IMPORTXML:](/blog/articles/importxml-function-google-sheets) Imports data from a web page with an XPath query, for figures GOOGLEFINANCE does not provide. * [ARRAYFORMULA](/blog/articles/array-formulas-google-sheets): Applies a formula to an entire range of data. * [QUERY](/blog/articles/query-function-google-sheets): Filters, sorts and summarizes a table with SQL-like syntax, for example the table of historical prices. * [AVERAGE](/blog/articles/average-functions-google-sheets): Calculates the arithmetic mean of a range, for example the average closing price of a period. * [IF](/blog/articles/if-function-google-sheets): Returns one result when a condition is true and another when it is false. ## GOOGLEFINANCE brings the rate, your own numbers come from elsewhere GOOGLEFINANCE covers one kind of data: public market prices and exchange rates. The figures you multiply by those rates are a different matter. Revenue by country, ad spend in three currencies or invoices from a billing system sit in a data warehouse, and they usually reach the sheet as an export that someone has to repeat every week. The [OWOX Data Marts extension for Google Sheets](https://workspace.google.com/marketplace/app/owox_data_marts/94902851409) covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas. ![Google Sheets with the OWOX Data Marts extension open beside an ad spend table: the Unified Ad Spend data mart, nine of fourteen columns ticked and a Save & Run button – the rows in the sheet come from a governed data mart, not a pasted export](https://cdn.owox.ai/www/content-marketing-article/googlefinance-function-google-sheets/screens/1.png/public) What changes for the person who maintains the sheet: * **Refresh replaces re-export.** _Refresh current report_ in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it. * **The source is written on the sheet.** The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report. * **No SQL and no warehouse access needed.** The analyst decides which data marts and columns are available. You choose from that list. The extension does not write or change the analyst’s SQL. GOOGLEFINANCE stays in the sheet for the rates, and the amounts next to it come from the same data mart on every refresh. The extension comes with the OWOX Cloud plans of [OWOX Data Marts](https://www.owox.com/app-signup); [spreadsheet reporting](/features/spreadsheet-reporting) describes both ways data reaches a sheet. ## Your New Normal Turn your data into decisions. Governed data marts give you the clean foundation ML needs to actually work. * No AI hallucinations * Analyst-governed definitions * Every number traces to SQL [Get started free](https://www.owox.com/app-signup) ## FAQ ## Frequently Asked Questions What are some common issues I might face while using the GOOGLEFINANCE function, and how can I solve them? Almost every problem shows up as #N/A: a ticker that is not recognized, a misspelled attribute, a real-time attribute such as pe used together with dates, a single date that is not a trading day, or an interval other than DAILY or WEEKLY. Historical formulas return a table and show #REF! when the cells below them are not empty. How can I import and analyze historical currency exchange rates? To import and analyze historical currency exchange rates, use the GOOGLEFINANCE function with appropriate parameters. For example, =GOOGLEFINANCE("CURRENCY:USDEUR", "close", DATE(2022,1,1), DATE(2022,12,31)) fetches the USD to EUR exchange rates for the year 2022. Can I obtain historical stock data with the GOOGLEFINANCE function? How? Yes. Add a historical attribute, a start date and an end date: =GOOGLEFINANCE("NASDAQ:AAPL", "close", DATE(2022,1,1), DATE(2022,12,31)) returns a table with the date and the closing price for each trading day of 2022. Add "WEEKLY" as the fifth argument for one row per week. Is it possible to track currency exchange rates using GOOGLEFINANCE? Yes. Use the word CURRENCY, a colon and the two currency codes written together: =GOOGLEFINANCE("CURRENCY:USDGBP") returns the current USD to GBP rate. Add "price", a start date and an end date to get the rate for each day of a period. How do I start using the GOOGLEFINANCE function to track my favorite stocks? To start using the GOOGLEFINANCE function, open Google Sheets and select a cell where you want the data to appear. Then, type =GOOGLEFINANCE followed by the parameters you want to specify, such as the stock symbol or attribute. What exactly does the GOOGLEFINANCE function do in google sheets? The GOOGLEFINANCE function in Google Sheets retrieves current or historical financial market data. It can fetch information such as stock prices, trading volumes, market capitalization, and more. ## Who wrote this ![Vadym Kramarenko](https://cdn.owox.ai/www/webflow/6798e703afdf787334941cde_Vadym-Kramarenko.png/public) [Vadym Kramarenko](/team/vadym-kramarenko) · Growth Marketing Manager Vadym Kramarenko is a Growth Marketing Manager at OWOX, where he drives user acquisition and product-led growth strategies. He hosts the OWOX podcast, interviewing analytics professionals about data-driven marketing, attribution, and reporting best practices. Vadym specializes in turning complex analytics concepts into practical, actionable marketing frameworks. [LinkedIn](https://www.linkedin.com/in/vadim-kramarenko/) · [All articles](/team/vadym-kramarenko) [Google Sheets Tips](/blog/topics/google-sheets-tips) ## Related articles [Google Sheets Tips · Pivot tables in Google Sheets: how to create and use them · October 2, 2026](/blog/articles/pivot-tables-google-sheets) [Google Sheets Tips · UNIQUE function in Google Sheets: syntax, examples and errors · October 2, 2026](/blog/articles/unique-function-google-sheets) [Google Sheets Tips · VLOOKUP in Google Sheets: formula, another sheet and examples · October 2, 2026](/blog/articles/vlookup-in-google-sheets) [See all articles →](/blog/articles) ## Book a call You are booked. The invitation is on its way to , with the calendar invite and the meeting link. [Reschedule](#) [Cancel](#) ## Links Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link. - [Google Sheets Tips](https://www.owox.com/blog/topics/google-sheets-tips) — /blog/topics/google-sheets-tips.md - [Vadym Kramarenko](https://www.owox.com/team/vadym-kramarenko) — /team/vadym-kramarenko.md - [DATE function](https://www.owox.com/blog/articles/date-functions-google-sheets) — /blog/articles/date-functions-google-sheets.md - [TODAY](https://www.owox.com/blog/articles/now-today-functions-google-sheets) — /blog/articles/now-today-functions-google-sheets.md - [Book a Demo](https://www.owox.com/book-a-call) — /book-a-call.md - [TEXT function](https://www.owox.com/blog/articles/text-function-google-sheets) — /blog/articles/text-function-google-sheets.md - [charts in Google Sheets](https://www.owox.com/blog/articles/charts-for-visualization-google-sheets) — /blog/articles/charts-for-visualization-google-sheets.md - [currency conversion with GOOGLEFINANCE](https://www.owox.com/blog/articles/googlefinance-currency-conversion-google-sheets) — /blog/articles/googlefinance-currency-conversion-google-sheets.md - [SPARKLINE](https://www.owox.com/blog/articles/sparkline-google-sheets) — /blog/articles/sparkline-google-sheets.md - [IMPORTXML:](https://www.owox.com/blog/articles/importxml-function-google-sheets) — /blog/articles/importxml-function-google-sheets.md - [ARRAYFORMULA](https://www.owox.com/blog/articles/array-formulas-google-sheets) — /blog/articles/array-formulas-google-sheets.md - [QUERY](https://www.owox.com/blog/articles/query-function-google-sheets) — /blog/articles/query-function-google-sheets.md - [AVERAGE](https://www.owox.com/blog/articles/average-functions-google-sheets) — /blog/articles/average-functions-google-sheets.md - [IF](https://www.owox.com/blog/articles/if-function-google-sheets) — /blog/articles/if-function-google-sheets.md - [OWOX Data Marts](https://www.owox.com/app-signup) - [spreadsheet reporting](https://www.owox.com/features/spreadsheet-reporting) — /features/spreadsheet-reporting.md - [Google Sheets Tips · Pivot tables in Google Sheets: how to create and use them · October 2, 2026](https://www.owox.com/blog/articles/pivot-tables-google-sheets) — /blog/articles/pivot-tables-google-sheets.md - [Google Sheets Tips · UNIQUE function in Google Sheets: syntax, examples and errors · October 2, 2026](https://www.owox.com/blog/articles/unique-function-google-sheets) — /blog/articles/unique-function-google-sheets.md - [Google Sheets Tips · VLOOKUP in Google Sheets: formula, another sheet and examples · October 2, 2026](https://www.owox.com/blog/articles/vlookup-in-google-sheets) — /blog/articles/vlookup-in-google-sheets.md - [See all articles →](https://www.owox.com/blog/articles) — /blog/articles.md