HomeGoogle DocsSpreadsheetHow to Use the GoogleFinance Function in Google Sheets

How to Use the GoogleFinance Function in Google Sheets

Published on

You can track your financial portfolio in Google Sheets using the GoogleFinance function. In this detailed Google Sheets tutorial, I am sharing with you how to use the GoogleFinance function in Google Sheets.

You can use the GoogleFinance function in Google Sheets to get live as well as historical stock prices and currency exchange rates.

With that information, hope you can create your financial portfolio tracker in Google Sheets. My expertise is on Google Sheets functions and formulas. So I am concentrating on the usage side of the function.

How to Use the GoogleFinance Function in Google Sheets

Google Sheets is the amazingly popular cloud-based Spreadsheet application from Google. In this Spreadsheet application, you can use the GoogleFinance Function to fetch current or historical securities information from the Google Finance site.

Syntax:

GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])

Ticker:

In this syntax, the argument “ticker” represents the ticker symbol of the security to consider to get live or historical data.

Attribute:

The argument “attribute” is different for live currency and stock prices, historical currency and stock prices, and for the mutual funds.

If you want to get the live or historical currency exchange rates you can use the “currency” symbol (examples follow).

1. Live and Historical Currency Exchange Rates

Example Formula:

a. Live Currency Exchange Rate

=GOOGLEFINANCE("CURRENCY:USDINR")

This GoogleFinace formula would fetch the live currency exchange rate from US Dollar to Indian Rupee. That means this formula would find and return 1 USD is equal to how much Indian Rupee.

Here I haven’t used any attributes as it’s only required if you want to fetch the historical currency exchange rates.

b. Historical Currency Exchange Rate

Example Formula:

=GOOGLEFINANCE("CURRENCY:USDEUR", "price", DATE(2018,1,1))

You can learn in detail about how to use the GoogleFinance function in Google Sheets to fetch current or historical currency exchange rates by following my below tutorial. There you can find some additional tips too.

Visit: How to Convert Currency in Google Sheets Using GoogleFinance Function

The additional tips that you can find include;

a. How to Import ISO 4217 Currency Codes into Google Sheets?

b. How to Create Currency Exchange Rates Table in Google Sheets?

c. Tips on Currency Conversion and SUM.

2. Live and Historical Stock Prices

Example Formula:

a. Live Stock Prices

=GOOGLEFINANCE("NSE:RELIANCE","price")

This GoogleFinance formula would return the real-time stock price of the ticker “RELIANCE”. Needless to say, I’ve used the “price” attribute here.

The Prefix “NSE” with the ticker symbol indicates the stock exchange in which the stock is trading. It’s the National Stock Exchange of India Ltd.

Here the attributes are not limited to “price”. There are attributes to find the current day’s high price, low price etc. All of them are detailed in the below tutorial.

Visit: Real-Time Stock Prices in Google Doc Spreadsheet

b. Historical Stock Price Data

In historical stock data, other than the arguments ticker and attribute you may want to use the start date, end date, and intervals.

The Supported Attributes in GoogleFinance Historical Stock Price Data

open | close | high | low | volume

Example:

=GOOGLEFINANCE("NSE:RELIANCE", "open", DATE(2018,6,1), DATE(2018,6,10), "DAILY")

This formula would return the opening price of the stock “RELIANCE” for the period from 01/06/2018 to 10/06/2018.

In this formula, the interval is “DAILY”. You can change that to “WEEKLY” if you want weekly data.

See the example screenshot below. In that, I’ve entered the above ticker symbol in cell B2 and all the attributes in the row # 4.

The formula in cell B5 is the above same formula but used cell references instead of directly entering the ticker and attributes inside the formula.

When I copy and paste the formula in Cell B5 to D5, the ticker symbol stays the same but the attributes moved to D4. It’s applicable to the formulas in Cell F5, H5, and J5.

example to GoogleFinance historical data

To get opening, closing, high, low and volume of a stock that all at once, you can use the attribute “all”. See that GoogleFinance formula variation.

=GOOGLEFINANCE("NSE:RELIANCE", "all", DATE(2018,6,1), DATE(2018,6,10), "DAILY")

All attribute in Google Finance

3. Live and Historical Mutual Fund Data

Here is one example formula.

=googlefinance("MUTF_IN:EDEL_LARG_MID_1D0HAMC","price")

I don’t have enough ticker symbol of mutual funds to test the function. There are lots of attributes associated with the Mutual Fund Data in Google Sheets that you can see Here.

Hope you could understand how to use the GoogleFinance Function in Google Sheets. Thanks for the stay!

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

3 COMMENTS

    • Hi, SPH,

      It’s not possible as GoogleFinance itself is an array formula. If you use the start date and end date arguments, the formula would return an array result.

      So in your case, you need to copy-paste the formula down.

LEAVE A REPLY

Please enter your comment!
Please enter your name here