One of the lesser-known features of Google Sheets is GOOGLEFINANCE, which allows you to track current or historical data for stocks in the stock market. Here’s how to use it.
What is Google Finance?
Finance is a real-time Google tool that displays current market information and aggregates economic news. It’s currently integrated with Google Search, so if you Google the ticker symbol for a specific company, like WMT for Walmart or AAPL for Apple, you’ll instantly see the current stock price and historical data for that title. You can click on any of these stocks to go to the company’s Google Finance page, which displays the company’s financial statements and related news, and lets you compare it to other stocks.
While there are other, more powerful tools that you can use to keep track of stocks, Google Finance is the only one that can integrate effectively with Google Sheets. Whether you’re new to stocks or an experienced trader, this integration is the easiest way to import and track stock data into a spreadsheet.
By the way, the Google Finance Sheets integration is only available in English and does not yet include most international exchanges. So, if you want to trade on the Asian or European exchanges, this might not be the best option for you.
Google Finance job
The function that extracts stock market data is called “GOOGLEFINANCE”. The function’s syntax is very simple and uses five arguments, four of which are optional.
Google Finance Formula
The first argument is the pointer symbol. These are the tokens that companies have when they are listed on an exchange, like GOOG for Google or BAC for Bank of America. You can also select the stock exchange where your chosen stocks are listed to avoid inconsistencies. Since Bank of America is listed on the New York Stock Exchange, write “NYSE: BAC.”
To get the stock symbols and stock markets you want, you’ll need to do some research. You can search for it on Google Finance or the portfolio management tool of your choice.
The second argument is the attribute you want to display. By default, it is set to “Price” if you leave it blank. Here are some attributes that you can extract using the function:
Prix : Le prix de l'action spécifique en temps réel.
Volume : Le volume d'échange actuel.
high : Le cours le plus élevé du jour en cours ou du jour choisi.
bas : Le prix bas du jour actuel ou du jour choisi.
volumeavg : Le volume d'échange quotidien moyen.
pe : Le ratio cours/bénéfice.
eps : Le bénéfice par action.Note that the attributes you can view vary depending on whether you are using current or historical data. Below is a complete list of attributes that you can use for discussion. It is important to remember that the current data is updated every 15 minutes and is therefore not completely real time.
The third argument is the start date, which only applies if you’re using historical data. You can type “TODAY()” or leave this argument blank to display real-time data. The fourth argument specifies either the end date or the number of days from the start date. If left blank, the function will return data for one day.
The last argument is the interval, which allows you to specify the frequency of the data. You can set it to “daily” or “weekly”.
Note that Google Sheets treats the pointer symbol and attribute arguments as text. So make sure to include it in quotes or else you will get an error.
Work tracking at work
In this example, let’s say you want to know the current price of Facebook’s stock. Facebook’s stock is listed on the NASDAQ under the ticker symbol FB. In this case, you will enter the first argument as “NASDAQ:FB” with “price” as an attribute. So the formula would be =GOOGLEFINANCE (“NASDAQ:FB”, “price”).
Simple example of Google Financial Sheets
If you want to display the daily closing quotes for a particular week, say the week starting on October 15, 2018, you can specify that date range in the third and fourth arguments. The corresponding code becomes = GOOGLEFINANCE(“NASDAQ:FB”, “price”, DATE (2018,10,15), DATE (2018,10,20)). Note that displaying historical data expands the information generated in the table data, which occupies adjacent cells.
You can also use the function to automatically generate data for the action list. Simply write the indicators in a column and then use the cells in the first argument. Since the indicator symbol is in cell C4, you can type =GOOGLEFINANCE(C4, “price”). Below is a list of stocks with their corresponding current prices.
List of stock indices in Google Financial Sheets
If you want to keep track of the list of attributes, you can enter them into separate cells, as in the image above. Then you can bind the second argument to the cell containing the attribute name. For the NYSE:IBM price cell in the example below, the formula would be =GOOGLEFINANCE(C$2,$B5).
Google stock with tags
Google Sheets Optimization
The advantage of having your stocks on Google Sheets is that you can use various data processing tools on them.
For example, let’s say you want to use Google Sheets to track the value of all your financial assets, such as stocks, savings accounts, term deposits, etc. Thanks to Finance, the price of your shares will be updated in real time, which will allow you to have an overview of your position at all times.
Convert currencies with notes
Another great feature of Google Sheets is the ability to convert currencies in real time. To do this, simply type the stock ticker “CURRENCY:” followed by the symbols of the two currencies you wish to convert, such as “USDGBP” or “EURJPY.” You can also view historical currency data by selecting a date.
For example, if you live in Europe and want to convert US dollars to Euros, type =GOOGLEFINANCE (“CURRENCY:USDEUR”) and multiply this number by the amount of US dollars to be converted.
Convert currencies in Google Financial Sheets
This function can be used in many cases, apart from foreign exchange transactions. For example, if your business involves paying in another currency, you can set up an invoice that automatically converts the payments you receive into your local currency.