Working with spreadsheets is common, no matter where you are working. However, the way we work with sheets has changed a lot since we used Office 2003, versus now when we use cloud-based solutions like Google Sheets. Coming to working with formulas, as Google Sheets is a cloud-based solution, there are several new formulas that can be useful not just for enterprises, but also for individuals who use Google Sheets to simplify their work or anything else.
One such formula is the GOOGLEFINANCE formula which offers financial information based on your queries right into the cells within the sheet you are working. GOOGLE FINANCE might be one formula, but a handful of powerful functions are baked into it so that you can use the right one when you need it. Today I will basically talk about the functions available within GOOGLEFINANCE, one by one, and also discuss how to use them.
So, without any further delay, let’s get started with the functions and features of GOOGLE FINANCE, along with how to use each of them.
What is GOOGLEFINANCE?
GOOGLEFINANCE is a powerful tool that fetches the stock price of a specific stock, and other details regarding the same like, whether the stock price has seen a spike, and if so, by how much, the maximum and minimum price of the stock on a particular day, and a lot more. I will discuss each of them one by one.
But before getting started, you must know the format to get stock information using GOOGLE FINANCE, which is given below.
GOOGLEFINANCE(ticker, [attribute], [start_date], [num_days|end_date], [interval])
Here’s a brief explanation of each of the elements.
Ticker: Ticker, which is an essential parameter to use the GOOGLEFINANCE formula is an abbreviation that refers to a specific publicly traded security. This is a combination of some characters, both numeric, as well as letters, like RELIANCE for Reliance Industries, GOOGL for Alphabet, which is the parent company of Google, etc. Besides the name of the security, you can also specify the name of the stock exchange where it is traded, just before the name of the ticker, separated by a colon (:).
For example, if you want to know the stock price of Life Insurance Corporation of India on NSE, you can write “NSE:LICI”, as the ticker.
The ticker will be rendered as text, and hence, you should keep it within “”. However, if the ticker is in a different cell, and you want to pass that to GOOGLEFINANCE, you can simply enter the name of the cell as the ticker.
Attribute: This is an optional argument that is again rendered as text, and it refers to the type of information Google will fetch, for the ticker entered by you. If you don’t add any attribute, GOOGLEFINANCE will automatically fetch the stock price. Here is a list of important attributes available within GOOGLEFINANCE that you can use.
|Attribute||Explanation in brief|
|price||Current price of the stock/security (with around 20 minutes delay)|
|priceopen||Price of a stock during market opening|
|volume||Trading volume of a stock on the current day|
|high||The market capitalisation of a stock|
|low||Lowest price of the stock on the current day|
|marketcap||The market capitalization of a stock|
|datadelay||The delay in the data provided|
|tradetime||The time when the stock was last traded|
|volumeavg||The lowest price recorded in the last 52 weeks|
|pe||The closing price of the stock on the last day|
|eps||Earnings for one share|
|high52||Highest price recorded in the last 52 weeks|
|low52||Closing price of the stock on the last day|
|change||Change of the stock price after yesterday|
|changepace||Percentage change of the stock price since yesterday|
|closeyest||The highest price of a stock during a specified interval|
|shares||Outstanding number of shares|
|currency||Currency in which the stock is prices|
|beta||Beta value of a specific stock|
|Historical attribute||Explanation in brief|
|open||Price during market opening|
|close||Price during market closing|
|high||The ratio of price to earnings|
|low||Lowest price of a stock during a specified interval|
|volume||Volume during a specific interval|
|all||Provides all the above information|
Start_date: If you want historical information regarding a stock or security, you can enter the date from which historical data will be fetched. If you don’t specify the date, the current date will be considered.
The date should be entered in the following format.
Num_day|End_date: When you are fetching historical data, if you specify the Num_day, GOOGLEFINANCE will fetch data corresponding to the security from the start date specified, and the number of days after that specified by you.
Alternatively, you can also enter the End_date, which will configure GOOGLEFINANCE to fetch specific data regarding the security, from the start date till the end date.
Interval: This optional parameter can be used if you want historical information at a specific interval. By default it is DAILY, however, you can also set it to WEEKLY if you want. If you set it to weekly, information regarding the security will be displayed on a weekly basis, from the starting date.
For example, if you want the stock price of ITC on NSE starting from 1st September 2013 to 15th September 2013, and every day in between, the GOOGLEFINANCE formula will go as follows, and the results will be as below.
However, market holidays will be excluded from the list.
If on the other hand, you want to see all the information regarding a stock, whose name is entered in a specific cell, say cell A1, and you want the information in cells starting B1, the formula in cell B1 will go as follows.
So, if you are into dealing with stocks, you can use Google Sheets to stay updated and organize the data available. You can also nest or use other Google Sheets formulas with GOOGLEFINANCE to go a step ahead and get more insights for improved data analysis.
Finding Currency exchange rates and currency conversion
Using GOOGLEFINANCE you can find the updated exchange rate between different currencies, and if you have multiple values, you can convert those amounts to another preferred currency easily.
If you want to convert US dollars or USD to Indian Rupee or INR, the formula in the cell will go as follows.
However, if you want to convert an existing value in USD or your preferred currency to INR or any other currency, you can do that, as well. If you have the USD currency value in cell A2, and you want the corresponding INR value in cell B2, the formula in cell B2 will go as follows.
That said, GOOGLEFINANCE is a powerful tool in Google Sheets to deal with financial data. The output from GOOGLEFINANCE can also be used with all other functions available in Google Sheets to achieve your needs. The only thing that you should note is that GOOGLEFINANCE will not work, if you are working offline on Google Sheets, for obvious reasons. As GOOGLEFINANCE fetches results from the internet, you should always be collected to the internet.
Do you have any further questions regarding GOOGLEFINANCE? Feel free to comment on the same below.