Google Finance Sheets Documentation

Google Finance Function in Sheets

In the intricate and rapid world of finance, staying ahead of the curve can make all the difference. With data as the fuel that powers financial insights, it's no surprise that fintech tools are revolutionizing how we manage, analyze, and act upon financial data. 

For many, Google Sheets is a familiar and powerful platform for data manipulation and analysis. GoogleFinance Function is one of its hidden gems, offering direct access to real-time and historical market data. 

In this comprehensive guide, we'll explore how to utilize GoogleFinance in Google Sheets to its fullest potential, empowering analysts, investors, and spreadsheet users to unearth the full spectrum of financial data directly within their Google Sheets environment.

Table of Contents

The Financial Insight Imperative

Financial data is not just numbers on a page; it is a critical narrative that underpins strategic decision-making. Whether you're an experienced financial analyst, a day trader, or a fresh investor dipping your toes into the market, the tools you use to manage your data are paramount.

Financial management is not just for the specialists; any individual or business can benefit from fostering a clear understanding of their financial environment.

A Deep Dive into Spreadsheets

For decades, spreadsheets have stood as the cornerstone of financial analytics, providing a stalwart and adaptable framework for navigating intricate datasets. Their inherent flexibility and reliability render them indispensable tools in the realm of financial management.

Moreover, when seamlessly integrated with live financial feeds, spreadsheets ascend to new heights, emerging as pivotal instruments for capturing and capitalizing on real-time market dynamics. 

In this symbiotic relationship between spreadsheet functionality and live financial data, users gain unparalleled access to timely insights, empowering them to make informed decisions and navigate the ever-evolving landscape of financial markets with confidence and precision.

The Rise of Google Sheets

Source: toptotal.com

Google Sheets has cemented its position as a premier platform for financial analytics, owing to its collaborative functionalities and intuitive interface. This versatile tool has revolutionized the way teams collaborate and manage data, facilitating smoother workflows and enhanced data organization. 

Central to this transformative capability is GoogleFinance, a dynamic feature that seamlessly integrates market data directly into your spreadsheet environment. By harnessing the power of GoogleFinance, users gain immediate access to vital financial information, enabling them to conduct comprehensive analyses and make informed decisions—all within the familiar and collaborative ecosystem of Google Sheets.

Unveiling GoogleFinance in Google Sheets

GoogleFinance in Google Sheets

The incorporation of GoogleFinance in Google Sheets heralds a significant advancement for investors across all spectrums. This function is a streamlined conduit through which individuals can seamlessly access vital stock and currency data for analytical purposes.

By harnessing the power of the GoogleFinance function, users can effortlessly retrieve a plethora of historical data points, including but not limited to opening and closing prices, highs and lows, and trading volumes, alongside real-time metrics such as market capitalization, price-to-earnings ratios (P/E), fluctuations, and earnings per share (EPS). This wealth of information empowers investors to conduct comprehensive analyses directly within the familiar interface of Google Sheets.

The versatility of the GoogleFinance function unfolds through two primary avenues for individual investors. Firstly, it serves as an invaluable tool for meticulously monitoring and managing investment portfolios. Secondly, it facilitates in-depth evaluations of prospective stocks, enabling investors to make informed decisions regarding potential investment opportunities.

Through its multifaceted utility, the GoogleFinance function emerges as an indispensable asset in the arsenal of modern investors striving for informed and strategic financial endeavors.

A Glimpse into GoogleFinance Capabilities

GoogleFinance is equipped to retrieve a diverse array of market data, spanning both real-time and historical metrics crucial for comprehensive financial analysis.

This feature facilitates access to a broad spectrum of information, encompassing market capitalization figures, trading volumes, high and low prices, as well as significant indicators such as price-to-earnings (P/E) ratios, among others. 

This comprehensive coverage extends across a wide range of financial instruments, ensuring users have access to the essential data points necessary for informed decision-making and strategic investment planning.

Take a look at this example, where we can obtain Apple’s Latest Price instantly by using the function call: 

=GOOGLEFINANCE(“Ticker”, “Price”)

Another example: You can use GoogleFinance Function to obtain a company’s PE Ratio. You can use the following function call: 

=GOOGLEFINANCE(“Ticker”, “pe”)

How can we achieve this?

Crafting the Perfect Formula

Utilizing the GoogleFinance in Google Sheets is remarkably straightforward. Simply input "=GOOGLEFINANCE()" into a cell, and within the parentheses, specify the desired parameters: the ticker symbol representing the financial instrument, the metric of interest, and optionally, the start and end dates for the data retrieval. 

This intuitive syntax allows users to effortlessly tailor their queries to extract specific financial information, empowering them to conduct thorough analyses and derive actionable insights. Here’s how:

Ticker:

In the realm of the stock market, a ticker symbol is a distinct sequence of characters that serves as a unique identifier for publicly traded securities. This unique identifier, also known as a ticker, facilitates swift and precise transactions, providing an exclusive label for each security in the complex world of trading.

The ticker symbol incorporated into the GoogleFinance function encompasses a vast array of stocks. These are not limited to a single exchange but span across multiple global exchanges. The function also considers a broad spectrum of currencies, thereby offering comprehensive data and insights.

This expansive coverage by the GoogleFinance function ensures that users have access to real-time information from various exchanges worldwide. It simplifies the process of monitoring and analyzing the performance of different stocks, irrespective of their geographical location or transaction currency. This functionality promotes a holistic understanding of global financial markets, enabling informed investment decisions.

RegionExchange CodeDescriptionDelay (minutes)
AmericasBCBABuenos Aires Stock Exchange20
BMVMexican Stock Exchange20
BVMFB3 – Brazil Stock Exchange and Over-the-Counter Market15
CNSXCanadian Securities ExchangeRealtime
CVEToronto TSX Ventures Exchange15
NASDAQNASDAQ Last SaleRealtime *
NYSENYSERealtime *
NYSEARCANYSE ARCARealtime *
NYSEAMERICANNYSE AmericanRealtime *
OPRAOptions Price Reporting Authority15
OTCBBFINRA OTC Bulletin Board15
OTCMKTSFINRA Other OTC Issues15
TSEToronto Stock Exchange15
TSXToronto Stock Exchange15
TSXVToronto TSX Ventures Exchange15
EuropeAMSEuronext Amsterdam15
BITBorsa Italiana Milan Stock ExchangeRealtime
BMEBolsas y Mercados Españoles15
CPHNASDAQ OMX CopenhagenRealtime
EBREuronext Brussels15
ELIEuronext Lisbon15
EPAEuronext Paris15
ETRDeutsche Börse XETRA15
FRADeutsche Börse Frankfurt Stock ExchangeRealtime
HELNASDAQ OMX HelsinkiRealtime
ICENASDAQ OMX IcelandRealtime
ISTBorsa Istanbul15
LONLondon Stock ExchangeRealtime
MCXMoscow ExchangeRealtime
RSENASDAQ OMX RigaRealtime
STONASDAQ OMX StockholmRealtime
SWX, VTXSIX Swiss Exchange15
TALNASDAQ OMX TallinnRealtime
VIEVienna Stock Exchange15
VSENASDAQ OMX VilniusRealtime
WSEWarsaw Stock Exchange15
AfricaJSEJohannesburg Stock Exchange15
Middle EastTADAWULSaudi Stock Exchange15
TLVTel Aviv Stock Exchange20
AsiaBKKThailand Stock Exchange15
BOMBombay Stock Exchange LimitedRealtime
KLSEBursa Malaysia15
HKGHong Kong Stock Exchange15
IDXIndonesia Stock Exchange10
KOSDAQKOSDAQ20
KRXKorea Stock Exchange20
NSENational Stock Exchange of IndiaRealtime
SGXSingapore ExchangeRealtime
SHAShanghai Stock Exchange1
SHEShenzhen Stock ExchangeRealtime
TPETaiwan Stock ExchangeRealtime
TYOTokyo Stock Exchange20
South PacificASXAustralian Securities Exchange20
NZENew Zealand Stock Exchange20

Metric:

Metric is optional for the function call. If there is no metric specified, real-time price is the default. 

In the context of stock market analysis, a metric is a quantifiable measure that is used to assess specific characteristics deemed significant by Google about various stocks. These metrics are broadly classified into two categories: real-time and historical.

Real-time metrics pertain to data that is in a state of continual flux. This includes information such as a stock's current price, which is subject to change with every transaction executed in the market. It provides investors with the most up-to-date insights into a stock's performance and value.

On the other hand, historical metrics delve into past data related to the stock. This could include information like the closing price for the preceding 100 days. Historical metrics provide a retrospective look at a stock's performance, offering valuable insights into its past trends and patterns.

The breadth of metrics covered by Google spans across both these categories, offering a balanced mix of current and past stock performance data. The specifics of these metrics would be beneficial to provide a more comprehensive understanding. 

Here is the list of the covered historical and real-time metrics:

Historical metricDescription
“open”Price at market open.
“close”Price at market close.
“high”The high price during the specified time period.
“low”The low price during the specified time period.
“volume”The volume during the specified time period.
“all”Returns all of the above.

Historical Google Finance Metrics

Real-time metricDescription
“price”Stock price. In real-time but with a delay of up to 20 minutes.
“priceopen”Opening price (price at market open).
“high”High price of the current day.
“low”Low price of the current day.
“volume”The trading volume of the current day.
“marketcap”The market capitalization of the stock.
“tradetime”The time of the last trade of the stock.
“datadelay”The delay time for the real-time data.
“volumeavg”The average daily trading volume.
“pe”Price-to-earnings ratio
“eps”Earnings per share
“high52”The highest price in the last 52 weeks.
“low52”The lowest price in the last 52 weeks.
“change”The stock price change since the end of yesterday’s trading.
“beta”The beta value
“changepct”The percentage change in price since the end of yesterday’s trading.
“closeyest”Yesterday’s closing price.
“shares”The number of shares outstanding.
“currency”The currency that the stock is priced in.

Real-time Google Finance Metrics

Start Date:

When working with historical data, the "start date" is a crucial parameter that specifies the initial time frame from which you want to begin gathering data.

This date should be inputted following the standard date format: "year, month, day." It serves as the starting point for your data extraction, setting the stage for the period under review.

End Date:

In contrast, the "end date" marks the conclusion of the historical data range you're interested in. Like the start date, this should be formatted as "year, month, day." However, Google's functions offer an additional convenience here.

Instead of specifying a traditional date, you can enter a numerical value, such as 90, to retrieve data from the past 90 days. This feature simplifies the process of obtaining recent historical data.

Interval:

The "interval" option allows you to dictate the frequency at which historical data is retrieved. You have the choice between "daily" and "weekly" intervals.

For instance, if you're interested in tracking the closing price of a stock, selecting a daily interval would yield data showcasing the closing price for each day within your specified period.

Conversely, opting for a weekly interval would compile this information on a week-by-week basis, providing a summary of the closing price for each week in your selected range.

Exemplifying GoogleFinance at Work

While it's not as sophisticated as dedicated portfolio management software, GoogleFinance in Google Sheets can still assist in tracking investment performance, evaluating portfolio diversification, and assessing risk. 

Here's how:

1. Tracking Investment Performance:

  • GoogleFinance allows users to track the current price of individual stocks or other financial instruments. By regularly checking the prices of your investments, you can assess how they are performing over time.
  • You can use GoogleFinance formulas in Google Sheets to automatically update and track the performance of your investments. For example, you can create a spreadsheet where you input the quantity and purchase price of each investment, and then use GoogleFinance functions to fetch the current prices and calculate the total value of your portfolio.

2. Evaluating Portfolio Diversification:

  • GoogleFinance can provide real-time data on various assets, including stocks, bonds, commodities, and currencies. By monitoring the prices and performance of different asset classes, you can evaluate the diversification of your portfolio.
  • You can use Google Sheets to create charts and graphs that visually represent the allocation of your investments across different asset classes. This can help you identify if your portfolio is too concentrated in a particular sector or asset type, and whether adjustments need to be made to achieve better diversification.

3. Risk Assessment:

  • GoogleFinance provides access to historical price data, which can be used to calculate key risk metrics such as volatility and beta. Volatility measures the fluctuation in the price of an asset over time, while beta measures the sensitivity of an asset's returns to changes in the overall market.
  • By analyzing historical price data and calculating these risk metrics, you can assess the risk profile of your portfolio. For example, if your portfolio has a high beta, it may be more volatile and sensitive to market movements. If your portfolio has low volatility, it may be less risky but potentially offer lower returns.
  • Additionally, you can use Google Sheets to perform various risk analysis techniques, such as calculating the Sharpe ratio, which measures the risk-adjusted return of an investment relative to its volatility.

While GoogleFinance can provide valuable information for tracking investments, evaluating portfolio diversification, and assessing risk, it's important to remember that it's just one tool in your investment toolkit. It's also essential to consider other factors such as your investment goals, time horizon, and risk tolerance when making investment decisions.

The Wisesheets Alternative:

The GoogleFinance function, while undoubtedly powerful, does come with a couple of drawbacks that can hinder its effectiveness for serious stock investors. These include its inability to provide historical stock financials and real-time data. However, understanding these limitations only enhances your appreciation for the tool's capabilities.

For those who are committed to optimizing their stock investment strategies and wish to streamline the process of gathering essential data, leveraging GoogleFinance alongside Wisesheets presents a compelling solution.

With Wisesheets seamlessly integrated into Excel or Google Sheets, investors gain access to comprehensive company financials spanning income statements, balance sheets, cash flows, and crucial metrics. What's more, this access extends across 14 different exchanges with an impressive 20-year coverage, available either quarterly or annually.

Simply inputting the ticker symbol initiates a swift one-click process, bringing vital information directly to your fingertips, saving valuable time previously spent on manual data entry and research.

Or you can get the same information but only what you want using the =WISE() function. All you need is the company ticker, parameter, and the period (year, quarter, or TTM).

For example, you could get Amazon's revenue Q3 revenue for 2022 by using the function like this: 

=WISE(“AMZN”, “REVENUE”, 2022, “Q3”)

WISEPRICE Function:

The WISEPRICE function is an invaluable tool for obtaining real-time price data, historical price data, and dividend payment information. Its versatility and user-friendly syntax make it a must-have for anyone serious about their investment endeavors.

Let's break down the syntax of the WISEPRICE function:

  • Ticker/s [required]: This refers to the symbol or symbols of the companies you're interested in. You can either hardcode the ticker symbol (e.g., "AAPL") or reference a cell containing the symbol (e.g., A1:A20).
  • Parameter/s [required]: This specifies the type of data you wish to retrieve, whether it's price information, dividends, or other relevant metrics. You have the flexibility to input multiple parameters or a single parameter. Parameters can be hard-coded (e.g., "Price") or referenced from a cell (e.g., B1:D1).
  • Number of days to end [optional]: For historical price data, you can specify the number of days for which you want data. For instance, entering "5" would fetch the last 5 days of historical price data. You can either input a specific number or reference a cell containing the number of days.
  • Start Date [optional]: If you're interested in historical stock price data from a specific start date, you can input that date. The date should be in either a date format from a cell or follow the "mm/dd/yyyy" format. You can hardcode the date (e.g., "01/30/2022") or reference a cell containing the date (e.g., B1). If only a start date is provided, the function will return the historical attribute for that specific date.
  • End date [optional]: Similarly, for historical stock price data, you can specify a specific end date. The date format requirements are the same as for the start date. You can input the end date directly (e.g., "01/30/2022") or reference a cell containing the date (e.g., B1).

By understanding and utilizing the WISEPRICE function's capabilities, investors can efficiently gather the precise data they need to inform their decision-making processes, ultimately enhancing their overall investment strategy.

Here's an example, using the function call =WISEPRICE("AMZN", "Open", 2)

WISEFUNDS Function:

The WISEFUNDS function is a powerful tool tailored specifically for acquiring essential data regarding ETFs and funds, encompassing crucial metrics such as expense ratio, net asset value, assets under management, and more. Designed to streamline the process of accessing vital information, WISEFUNDS offers a straightforward syntax that simplifies data retrieval for investors.

Here's a breakdown of the WISEFUNDS function syntax:

  • Ticker/s [required]: This parameter pertains to the symbol or symbols of the ETFs or funds for which you're seeking data. You have the flexibility to either hardcode the ticker symbol (e.g., "SPY") or reference a cell containing the symbol (e.g., A1).
  • Parameter/s [required]: Specify the specific data you wish to retrieve using the function. You can input multiple parameters or a single parameter depending on your requirements. Parameters can be hard coded (e.g., "expense ratio") or referenced from a cell (e.g., B1:D1).

Here's an example of a valid function call using the WISEFUNDS function:

=WISEFUNDS("SPY","Expense Ratio")

By leveraging the WISEFUNDS function, investors can efficiently gather pertinent information about ETFs and funds, empowering them to make informed decisions and optimize their investment strategies with confidence.

Building your Own Custom Stock Screener:

Creating a personalized stock screener and accessing essential data becomes effortlessly streamlined with Wisesheets, available to Pro and Elite members.

Get Data:

Compile Your List: Begin by gathering a list of stocks in your Excel or Google Sheets spreadsheet. It's important to note that Wisesheets employs the same ticker system as Yahoo Finance. You can also utilize the screener's built-in functionality to acquire list data.

Select Parameters: Choose the parameters for which you wish to retrieve data. This encompasses a wide range of metrics including income statements, balance sheets, cash flow statements, key metrics, growth metrics, and real-time price data. For a detailed list of available items, refer to the provided link.

Navigate to WISE Screener: Once your list of stocks and parameters are set up, proceed to the WISE Screener section of the add-in.

Selection Process: Use your mouse to select the list of tickers for which you desire data retrieval. Once chosen, click on the designated icon to proceed. Repeat the same process for the selected parameters, ensuring to include the desired period. Available periods range from TTM (trailing twelve months) to specific years and quarters. Omitting the period defaults to the latest fiscal year value. Capitalization and spacing are inconsequential. No period specification is needed for real-time data.

Retrieve Data: With your selections completed, click on the "Get Data" button to generate your screener. The data will be presented in a bucket, housing the chosen tickers and parameters.

Refresh and Manage Data: Easily refresh the screener data using the provided refresh button within the bucket. This allows for obtaining the latest data anytime. Additionally, you can rename the bucket for future reference or delete it when no longer required. It's important to note that the bucket can only be refreshed in the same spreadsheet tab where the tickers and parameters are located together. Refreshing data in another tab won't yield results.

Wisesheets offers a multitude of advantages for stock screening, including swifter data retrieval and updates compared to traditional functions. Furthermore, it provides the flexibility to apply filters, such as sorting companies by revenue, without automatic function refreshes, enhancing efficiency and convenience in stock analysis endeavors.

In Conclusion:

Google Sheets, with its seamless integration of GoogleFinance, levels the playing field for financial analysis, providing a platform where seasoned investors, aspiring analysts, and curious learners alike can tap into the potential of their financial data.

While Google Sheets with GoogleFinance offers a solid foundation for financial analysis, Wisesheets expands upon this foundation by providing a comprehensive suite of tools and functionalities that cater to the diverse needs of investors, analysts, and learners. Whether you're a spreadsheet enthusiast, aspiring CFO, or financial novice, Wisesheets equips you with the resources needed to navigate the complex landscape of financial data analysis effectively.

Guillermo Valles
 | Website

Hello! I'm a finance enthusiast who fell in love with the world of finance at 15, devouring Warren Buffet's books and streaming Berkshire Hathaway meetings like a true fan.

I started my career in the industry at one of Canada's largest REITs, where I honed my skills analyzing and facilitating over a billion dollars in commercial real estate deals.

My passion led me to the stock market, but I quickly found myself spending more time gathering data than analyzing companies.

That's when my team and I created Wisesheets, a tool designed to automate the stock data gathering process, with the ultimate goal of helping anyone quickly find good investment opportunities.

Today, I juggle improving Wisesheets and tending to my stock portfolio, which I like to think of as a garden of assets and dividends. My journey from a finance-loving teenager to a tech entrepreneur has been a thrilling ride, full of surprises and lessons.

I'm excited for what's next and look forward to sharing my passion for finance and investing with others!

Related Posts