GOOGLEFINANCE is one of the lesser-known features in Google Sheets that allows you to keep track of current or historical financial paper data in the stock market. Here’s how to use it.

What is Google Finance?

Finance is Google’s real-time tool that displays current market information and aggregates business news. It is currently integrated with Google search, so if you look up a particular corporation’s ticker symbol on Google, such as WMT for Walmart or AAPL for Apple, you will immediately see the current stock price and historical data for that security. You can click on one of these stocks to go to the company’s Google Finance page, which shows the company’s financials, relevant news, and allows you to compare it to other products.

While there are other, more reliable tools you can use to track securities, Google Finance is the only one that can seamlessly integrate 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 in a spreadsheet.

By the way, the Google Finance sheet integration is only available in English and does not include most international exchanges yet. So if you want to transact on Asian or European exchanges, this might not be the best option for you.

Google Finance Feature

The function that retrieves stock data is called «GOOGLEFINANCE». The syntax of the function is quite simple and takes five arguments, four of which are optional.

Google Finance Formula

The first argument is the ticker symbol. These are the codes that companies have when they are listed on the stock market, such as GOOG for Google or BAC for Bank of America. You can also specify the exchange where the stocks you have chosen are listed to avoid discrepancies. Since Bank of America is listed on the New York Stock Exchange, you must enter «NYSE:BAC».

To get the ticker codes and stock exchanges you need, you need to do some research. You can find them on Google Finance or your portfolio management tool of choice.

The second argument is the attribute you want to display. It defaults to «price» if left blank. Here are a few attributes that you can retrieve using the function:

  • price: the price of a particular stock in real time.
  • volume: current trading volume.
  • high: the maximum price of the current or selected day.
  • low: the minimum price of the current or selected day.
  • volumeavg: average daily trading volume.
  • pe: price-to-earnings ratio.
  • EPS: earnings per share.

Note that the attributes you can display depend on whether you are using current or historical data. Here is the complete list of attributes you can use for the argument. It is important to remember that live data is updated every 15 minutes, so it is not fully displayed in real time.

The third argument is the start date, which only applies if you are using historical data. You can enter «TODAY ()» or leave the field blank to display real-time data. The fourth argument specifies either the end date or the number of days from the start date. If you leave this field 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».

It should be noted that Google Sheets treats the ticker symbol and attribute arguments as text, so be sure to put quotes around them or you’ll get an error.

Stock tracking in action

For example, let’s say you want to see the current price of a Facebook stock. Facebook is listed on the NASDAQ with the ticker code FB. In this case, you enter the first argument as «NASDAQ:FB» along with «price» as an attribute. So the formula for this would be =GOOGLEFINANCE("NASDAQ:FB","price") .

An example of using Google Financial Sheets

If you want to view daily closing prices for a specific week, such as the week of October 15, 2018, you provide that date range in the third and fourth arguments. The code for this becomes =GOOGLEFINANCE("NASDAQ:FB","price",DATE(2018,10,15),DATE(2018,10,20)) . Note that viewing historical data expands the generated information into an array of data that occupies adjacent cells.

You can also use the function to automatically generate data for a list of stocks. Just print the tickers in the column, then use the cells in the first argument. Since our ticker code is in cell C4, you should type =GOOGLEFINANCE(C4,"price") . Below is a list of stocks with their respective current prices.

Google Finance Sheets Stock ticker list

If you want to keep track of a list of attributes, you can print them in separate cells like in the picture above. You can then bind the second argument to a cell with an attribute name. For the price cell for NYSE: IBM in the example below, the formula would be =GOOGLEFINANCE(C$2,$B5) .

Google Finance Sheets with Attributes

Maximizing Google Sheets

The best part about your shares in Google Sheets is that you can use different data manipulation tools.

For example, let’s say you want to use Google Sheets to track the value of all your financial assets, such as your stocks, savings accounts, time deposits, and more. Using Finance, your stock price will be updated in real time, so you get a complete picture of your position at any given time.

Currency Conversion Using Sheets

Another great feature of Google Sheets is that it can convert currencies in real time. You can do this by entering the stock ticker «CURRENCY:» followed by the codes for the two currencies you want to convert, such as «USDGBP» or «EURJPY». You can also display historical currency data by specifying the date.

For example, if you live in Europe and want to convert a few US dollars to Euros, you would enter =GOOGLEFINANCE("CURRENCY:USDEUR") and multiply this number by the amount of the convertible US dollar.

Google Finance Sheets Currency Conversion

It has many good use cases besides forex trading. For example, if your job involves getting paid in another currency, you can set up an account that automatically converts payments you receive into your home currency.

Похожие записи