Have you ever wanted to track your financial portfolio in Google Sheets? Well, look no further! In this article, I’ll show you how to use the powerful GoogleFinance function to get live and historical stock prices and currency exchange rates. With this information at your fingertips, you’ll be able to create your very own financial portfolio tracker in Google Sheets. So, let’s dive right in!
Getting Started with GoogleFinance Function
Google Sheets is an incredibly popular cloud-based spreadsheet application, and the GoogleFinance function is a game-changer when it comes to fetching current or historical securities information from the Google Finance site.
Here’s the syntax of the function:
GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])Let’s break down the syntax and understand each argument.
Ticker
The “ticker” argument represents the ticker symbol of the security you want to fetch data for. This could be a stock or a currency.
Attribute
The “attribute” argument varies depending on whether you want live or historical data for currency exchange rates or stock prices. For example, if you want live currency exchange rates, you can use the “currency” attribute.
Now, let’s explore some real-world examples to see the GoogleFinance function in action.
Live and Historical Currency Exchange Rates
To get started, let’s take a look at how to fetch live and historical currency exchange rates using the GoogleFinance function.
Live Currency Exchange Rates
If you want to find the live currency exchange rate from US Dollar to Indian Rupee, you can use the following formula:
=GOOGLEFINANCE("CURRENCY:USDINR")This formula will return the current exchange rate, showing you how much 1 USD is equal to in Indian Rupee.
Historical Currency Exchange Rates
To fetch historical currency exchange rates, you can use a formula like this:
=GOOGLEFINANCE("CURRENCY:USDEUR", "price", DATE(2018,1,1))In this formula, you can specify the start date and the end date, and the function will return the historical exchange rates within that timeframe. You can even check out my detailed tutorial on how to convert currency in Google Sheets using the GoogleFinance function for more tips and tricks.
Live and Historical Stock Prices
Now, let’s move on to the exciting world of stock prices. The GoogleFinance function can help you fetch both live and historical stock prices.
Live Stock Prices
To get the real-time stock price of a specific ticker, you can use a formula like this:
=GOOGLEFINANCE("NSE:RELIANCE","price")In this example, we are fetching the stock price of “RELIANCE” from the National Stock Exchange of India Ltd. You can use different attributes like “high”, “low”, and more to get specific data.
Historical Stock Price Data
If you’re interested in historical stock price data, you can use a formula like this:
=GOOGLEFINANCE("NSE:RELIANCE", "open", DATE(2018,6,1), DATE(2018,6,10), "DAILY")In this formula, you can specify the start date, end date, and interval. The function will return the opening price of the stock “RELIANCE” for the specified period.
If you want to get other attributes like closing price, high, low, or volume, you can specify the attribute as “all”. This will give you all the desired information at once.
Live and Historical Mutual Fund Data
The GoogleFinance function can also be used to fetch live and historical mutual fund data. Here’s an example formula:
=GOOGLEFINANCE("MUTF_IN:EDEL_LARG_MID_1D0HAMC","price")Unfortunately, I don’t have enough mutual fund ticker symbols to test this function thoroughly. However, you can find a wide range of attributes associated with mutual fund data in Google Sheets.
Now that you have a good understanding of how to use the GoogleFinance function in Google Sheets, you can unleash its power and take control of your financial data. So get started today and create your very own financial portfolio tracker!
Hope you found this article helpful. For more in-depth tutorials and tips, be sure to visit Crawlan.com. Happy spreadsheeting!
