Realtime Stock Data Feed Into Google Sheets
Did you know that Google Sheets isn't just a powerful spreadsheet application? More than a cloud-based spreadsheet solution, Google Sheets actually has some exceptionally powerful features, owing to its position in the Google ecosystem. That includes the ability to add live stock prices directly into your Google Sheet.
Because Google has so many utilities, many Google applications (like Google Sheets and Google Docs) can be extended using features like GOOGLEFINANCE. GOOGLEFINANCE makes it possible to add live stock prices into your Google Sheet — thereby potentially creating a real, live stock tracker.
Let's take a look at why you might need a Google Sheets Stock Tracker, how you can create one (or simply download our free Google Sheets stock template below!), and how you can analyze the stock information you pull in.
Why Would You Need a Google Sheets Stock Price Tracker?
Retail trading has opened the door for many people to "day trade" or invest in their spare time. A lot of people want to track stocks for their financial benefit, as a hobby, or just for their education.
Stocks move fast. The U.S. stock exchange opens at 8:30 EST every morning and continues to operate throughout the day. Many traders need to be able to see live prices to know how much they've made (or lost) or to make decisions on investments in the future.
While you probably don't want to use Google Sheets for critical real-time data, it can be extraordinarily helpful for analysis and reporting. You can pull information both from the current day and from historical results — and you can then analyze that activity using Google Sheets information.
Let's take a look at how you would use the GOOGLEFINANCE function to make a simple Google Sheets stock tracker.
How to Use the GOOGLEFINANCE Function
When modern stock advisors talk about tech stocks, they usually refer to "FANG": Facebook, Amazon, Netflix, and Google. These companies aren't everything in the technology market, but they do represent some of the most powerful movers and shakers.
Today, we're going to take a look at how to track these four stocks.
In the above Google Sheet, we see:
- Company. The company's legal name. This is usually just used so that you know which ticker relates to which company, as it can be difficult to determine.
- Ticker. The stock ticker, which is used as its reference on the stock exchange. You might note as you type these in that Google will actually automatically pull up the ticker information through its autosuggestions.
- Current Price. The current, active price from GOOGLEFINANCE. This is pulled with GOOGLEFINANCE(ticker,"price").
- 52-Week Low. The lowest the price has been in 52 weeks, pulled from GOOGLEFINANCE. This is pulled with GOOGLEFINANCE(ticker,"low52″).
- 52-Week High. The highest the price has been in 52 weeks, pulled from GOOGLEFINANCE. This is pulled with GOOGLEFINANCE(ticker,"high52″).
This is all you would need for basic stock tracking.
As you can see, GOOGLEFINANCE is generally called with the ticker (such as FB or AMZN) and then an attribute name (such as "price," "low52," or "high52"). This isn't a lot of information, but it's usually what people look at when they're establishing some basic information regarding stocks.
Attributes for the GOOGLEFINANCE Function
We've looked at three important functions for GOOGLEFINANCE: price, low52, and high52. But there are actually a lot of other attributes that you can use with GOOGLEFINANCE.
Here are some attributes that have to do with the stock itself:
- Volume. This is the trading volume of the stock. This is how much of the stock is actively trading hands, which can indicate how "popular" the stock is among investors.
- Marketcap. This is the market capitalization of the stock. This tells you how much is actually invested in the stock, and therefore often how much the stock might grow.
- Pe. The price/earnings ratio of the stock. This will tell you whether the stock is undervalued or overvalued compared to its earnings.
- Eps. This is the earnings per share of the stock. Again, this should tell you how the stock's current value compares to its earnings.
- Shares. This is the total number of shares for the stock. This is important in terms of the market liquidity.
Let's take a look at our stock sheet with these attributes added:
Now we have a lot more information on our sheet.
Here are some direct performance attributes:
- Priceopen. This is the price of the stock when the market first opens. Many analysts look at open prices for comparisons and for signals.
- Change. This is the amount of change the stock has undergone since the previous day's close. This can show how much trading is being done outside of market hours.
- Closeyest. This is the amount that the stock closed on the prior day. So you'll be able to see how much it was at the close of the last trading day.
- Changepct. This is the percentage of change since the previous day's close. This can indicate how excited people are about this stock after hours.
You can use any of these attributes very easily, as long as you know the ticker of the stock you're trying to track. But it should be noted that any of these points of data are merely a form of data analytics; none of them can guarantee any information about the stock or the company.
Completing Basic Analysis with Google Sheets
Why does this matter? Let's say you're looking for a FANG stock to invest in, but you want to see the stock with the greatest change percentage from the prior day's close.
Here, we can see that most of the stocks have gone down, except for Netflix. But there's an easier way to see this data, too.
By using Google Sheets' charting feature, we can better visualize the stock data that we have. This is one of the reasons you might want to pull GOOGLEFINANCE information into your Google Sheet — so you can perform the analysis functions that are built into Google Sheets.
Showing Price History with GOOGLEFINANCE
In addition to pulling information about a stock now, you can pull information about a stock's price history. Let's pull the price history for Facebook from the periods 01/01/2020 to 01/31/2020.
As you can see, this is done through the following formula:
GOOGLEFINANCE("FB","price",DATE(2020,1,1),DATE(2020,1,31),"DAILY") This is the same pull that was done earlier (FB, price), but this time we have added date variables. We could also pull the price history "WEEKLY" if we wanted to.
And consider if we wanted to price chart this activity:
The only real alternative to using stock prices with the GOOGLEFINANCE function would be manually entering each number in — so this is an important (and versatile) function.
How the GOOGLEFINANCE Function Works
It should be noted that the GOOGLEFINANCE function needs to pull data from the Google Finance application each time it loads. When you open your spreadsheet from now on, you'll see "Loading…" in the cells.
If, for some reason, you can't access the GOOGLEFINANCE application, the cell data isn't going to load. You also won't be able to access this information if you're editing offline. And if you try to download the spreadsheet as an Excel sheet, it isn't going to work.
If you are requesting information from the application that it doesn't have, you'll receive the error "N/A" for not applicable. This simply means that you're using the wrong ticker or the wrong attribute.
How Accurate is GOOGLEFINANCE?
GOOGLEFINANCE should be extremely accurate. It pulls data from known sources in real-time. It's the same information you will receive if you search Google for the current, active stock price. But because GOOGLEFINANCE queries can slow your sheet down (and because they rely on you pulling information from the API), they may not provide results down to the second.
For that reason, GOOGLEFINANCE should really be used more for stock analysis rather than real-time reporting.
Google Sheets Stock Template
You can get started now using our Google Sheets Stock Template .
Just click File -> Make a Copy so that you have your own customizable version.
You can use the Google Sheets Stock Template to track any stock listed on the public stock exchange.
A few reasons you might use a Google Sheets Stock Template include:
- Tracking stocks that you've already invested in. You can use a sheet to determine when you're going to sell stocks, or just how much you've made in your existing stocks.
- Looking for signals that you should invest in stocks. You can set points at which you might enter into a stock, such as if its 52-week low goes up by a certain percentage.
- Comparing different companies. You can take a look at any number of companies using a Google Sheets Stock Template, whether it's just a few of them or dozens of them.
- Creating reports for how stocks perform over time. Using GOOGLEFINANCE, you can pull information for certain dates — which you can use to determine the progress of stocks.
At the end of the day, managing live stock prices in Google Sheets is going to be less about Google Sheets and more about understanding how the stock market works. Without an understanding of how the stock market works, it's difficult to determine what different attributes mean. And if you don't know what those attributes mean, you can't perform the right analysis.
Now you know how to pull in a Google Sheets stock price tracker using the GOOGLEFINANCE function. If you need to pull the information into an application, you can also use the Google Finance API, or you can use Google Finance directly. But the GOOGLEFINANCE function is exceptionally useful for those who want to do their own analysis, data collection, and reporting in Google Sheets. Happy trading!
Jenna I.
Jenna I. is a tech and entertainment expert with a B.S. in Information & Computer Science and MCITP certification. She writes for Udemy, The Gamer, and SVG.
Source: https://productivityspot.com/google-sheets-stock-price-googlefinance-function/
0 Response to "Realtime Stock Data Feed Into Google Sheets"
Enregistrer un commentaire