Track stocks with Google Sheets


One of the lesser-known features in Google Sheets is GOOGLEFINANCE, which allows you to track current or historical financial securities 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 collects business news. It’s currently integrated with Google Search, so if you look up a specific company’s ticker symbol on Google, such as WMT for Walmart or AAPL for Apple, you’ll immediately see the current stock price and historical data for that security. You can click on any of these stocks to go to a company’s Google Finance page, where the company’s financials and related news are displayed and you can compare it with other commodities.

While there are other more robust tools you can use to track securities, the only one that integrates effectively with Google Sheets is Google Finance. Whether you’re a stock novice or an experienced trader, this integration is the easiest way to import and review stock data into a spreadsheet.

By the way, Google Finance’s sheet integration is only available in English and doesn’t cover most international exchanges yet. So if you want to trade on Asian or European exchanges, this might not be the best option for you.

The Google Finance feature

The function that retrieves inventory data is called ‘GOOGLEFINANCE’. The function’s syntax is quite simple and takes five arguments, four of which are optional.

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

To get the ticker codes and exchanges of your desired stocks, you need to do some research. You can search for them on Google Finance or your chosen portfolio management tool.

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 of the attributes you can get using the function:

price: The price of the specific stock in real time. volume: The current trading volume. high: The highest price of the current or chosen day. low: the current or chosen low price of the day. volumeavg: The average daily trading volume. pe: The price-earnings ratio. eps: Earnings per share.

Keep in mind that the attributes you can display will vary depending on whether you’re using current or historical data. Here’s a full list of attributes which you can use for the argument. It’s important to remember that the current data is refreshed every 15 minutes, so it’s 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 it blank to display real-time data. The fourth argument specifies the end date or number of days from the start date. If left blank, the function returns a single day’s data.

The last argument is the interval, which allows you to specify the frequency of the data. You can set it to “DAILY” or “WEEKLY”.

One thing to note is that Google Sheets handles the ticker symbol and attribute arguments as text, so make sure to put quotes around it or you’ll get an error.

Tracking stock in action

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

To view the daily closing prices for a particular week, such as the week of October 15, 2018, specify that date range in the third and fourth arguments. The code for that would be =GOOGLEFINANCE(“NASDAQ:FB”,”price”,DATE(2018,10,15),DATE(2018,10,20)) . Note that viewing historical data extends the generated information to array data, occupying nearby cells.

You can also use the feature to automatically generate data for a list of stocks. Just type the tickers in a column and then use the cells in your first argument. Since our ticker code is in cell C4, type =GOOGLEFINANCE(C4,”price”). Below you will find a list of stocks with the corresponding current prices.

To keep a list of attributes, you can type them into individual cells, as shown in the image above. You can then link the second argument to the cell with the name of the attribute. For the price cell for NYSE:IBM in the example below, the formula would be =GOOGLEFINANCE(C$2,$B5).

Maximize Google Sheets

The best thing about having your stock on Google Sheets is that you can use various data manipulation tools on it.

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. With Finance, the price of your shares is updated in real time, giving you a complete picture of your position at all times.

Convert currency with sheets

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

For example, if you live in Europe and want to convert some USD to Euros, you would type =GOOGLEFINANCE(“CURRENCY:USDEUR”) and multiply that number by the USD amount you are converting.

This has a lot of great use cases besides foreign exchange trading. For example, if your work involves getting paid in a different currency, you can set up an invoice that automatically converts the payments you receive into your home currency.

Leave a Reply

Your email address will not be published.