Analyzing Company Financial Data Using Google Sheets
Using any other spreadsheet tool than Excel for your financial analysis seems blasphemous, doesn't it? After all, it's been the de facto standard since spreadsheets started being used on Wall Street. What happens, though, when you are working with a machine that doesn't have Excel installed? Using a cloud-based spreadsheet package can take care of this problem. This article will show you how to start analyzing company financial data using Google Sheets.
Why Use Google Sheets?
Have you ever been away from your work computer but needed to use Excel? You are able to get access to a computer, but you discover it doesn't have Excel installed. Further, since most of your spreadsheets are installed on your local hard drive, it wouldn't do you much good even if Excel was installed.
With Google Sheets, you need only sign into your Google account and you have access to a spreadsheet program and your files (on the cloud). There is no loss of continuity. You simply start using the program from a browser.
Diehard Excel users may complain that Google Sheets doesn't contain all the functionality that Excel has. For a while, that was true. However, the gaps are closing, and unless you are a "spread head" with zealot devotion to the program, the differences are going to make much of a difference to you.
One benefit that Google Sheets has over Excel is that it is free to use. When you create a Google account, you then have access to Google Sheets (and other Google programs like Docs, etc.). You need only type in the following in your browser (while logged in):
https://sheets.google.com
Caveat - Google sheets, being a Google product, tends to work best in Google's Chrome browser. Although, I have used it without trouble in Microsoft Edge.
Microsoft charges for Excel, and in recent years has made it part of a subscription service. Office 365 costs $9.95 per month (yearly subscription saves a bit more). However, depending on what you are using Google Sheets for, it could end up costing you some money as well.
When you obtain financial data using GOOGLEFINANCE, there is an access restriction of 20,000 calls per day for free accounts. While 20,000 may sound like a lot, if you update information on multiple companies with many data points, you can hit that limit rather quickly. When you upgrade to a GSuite account, you'll get 30GB of storage and the limits will be raised to 100,000 calls per day.
These plans change often, so be sure to check before paying for access.
Current pricing plans for Google:
https://gsuite.google.com/pricing.html
You can check the quotas and limitation from Google directly:
https://developers.google.com/apps-script/guides/services/quotas
Problems with Google Sheets
Nothing is perfect, and that includes Google Sheets. One problem with the program is that it is created by Google. The company has a knack of shutting down programs without warning. Sometimes, they transfer the access to other companies, and then it's hit or miss whether the new companies charge.
There are people who don't like Google's dominance on the internet and protest the use of anything related to the company. However, Microsoft has been criticized for its dominating its segment of the market. If you protest either company, you can find alternatives here:
https://fossbytes.com/best-alternatives-microsoft-office/
Many people are leary of using the cloud, especially when storing sensitive data. These are valid concerns, with all the news about compromised data, etc. There are no easy answers. Cloud-based access is convenient. It is interesting though, what many people will share on their phones and social media. They often do this with reckless abandonment.
How to Use GOOGLEFINANCE for Financial Data
Now that we've gotten all the preliminary information out of the way, let's talk about how to access financial data using Google Sheets. To do this in Excel, you typically need to download add-ons, which may or may not cost money (some are freemium access). Also, they have the potential to crash within Excel. There are often quotas and access restrictions with these solutions, too.
The good news is Google Sheets allow access to this functionality natively. Google has made it a part of the core functionality of the program. Even better, it's extremely easy to use. Here are the steps:
Step 1 - Enter Stock Symbols
Feel free to choose whichever stock symbols you want to track. The ones listed in the image above are ones I chosen at random. You are also free to enter them anywhere you like in the spreadsheet. If you feel like you want to enter as a row, that will work, too.
Step 2 - Use the GOOGLEFINANCE Function
Just like Excel, use "=" in front of functions. When you start type "go" or "goo" after the equals sign, the help function will appear showing you the functions available to choose start with the letters you specify (in this case "goo"). Then, select the GOOGLEFINANCE function from the list.
When you select the function, the help feature will display the parameters available. Any parameter that is surrounded by [] is optional. For GOOGLEFINANCE, ticker is the only required attribute for this function. Note, you can hardcode the symbol (as shown in help with "GOOG" or reference a cell (A1, etc.).
The price function is the default. Therefore, if you did not enter any information for the second parameter, the function call will still return the latest price.
If you need more information about the function, the end of the help section contains a link to learn more about it. The following shows this information:
Step 5 - Complete the Function Call
After you enter in the parameters (attributes), you simply hit <Enter>. You could repeat this procedure for all the stock symbols you listed, or if you referenced them by cell, you can simply copy and paste the formula. Whichever method you decide, you'll get results that look similar to the following:
Yep! It's really that easy! No muss, no fuss. The functionality is inherent in Google Sheets.
What About Financial Statement Data?
Getting stock prices is something that financial people like to access. However, they often want more when analyzing companies. Having access to financial statement data could prove useful for this task.
Both Yahoo Finance and Google used to provide financial statement data. Somewhere around 2017-ish, both companies decided to stop offering this information. Perhaps they were throwing a bone to data providers who charge for this information. Who knows? Whatever the reason, you won't have access to any extensive financial statement data with GOOGLEFINANCE.
GOOGLEFINANCE does offer limited data points that could be useful. Here is a subset of data that could be useful:
- marketcap
- pe
- eps
- beta
The rest of the data deals primarily with stock and mutual fund information (close, open, volume, etc.)
As you can see, the data provided in this native format is limited.
Roll Your Own Data Importing with IMPORTHTML
If you're the adventurous type, you could try using IMPORTHTML. It is another native function that grabs information from web pages. This function does require a bit of HTML knowledge.
HTML is the language used by browsers. It stands for Hypertext Market Language.
This function works best with HTML tables and lists, but it's not foolproof. If the function cannot decipher the table or list, it won't retrieve it. What could seemingly be a ripe table for the taking will just appear as N/A in the cell, if there is something that is preventing IMPORTHTML from reading it.
The IMPORTHTML function is beyond the scope of this article. However, you can find a good introduction by searching on the web. The following article is a good place to start.
Create a Web Scraper
If you have any coding chops (or willing to learn), you could try to scrape the data from your favorite websites. This is a rather detailed undertaking, to be sure. If you have ever worked with scraping before, you'll spend a lot of time setting up the scaping code.
It's not that the coding itself is difficult. It's just that you have to weed through the pages you want to consider as candidates for scraping. You'll need to inspect the HTML code to find out the tags the software will need to find the needed information.
The worst part about scraping is that you spend a significant chunk of time, only to have the web owner change the HTML code on you sometime in the future. Often, it's not a small change, either. It will require you to go back to the drawing board. You'll either need to figure out where the changes occured, or you'll simply start over with the process from the beginning.
There are also ethical issues with scraping. Most web owners won't mind a casual scraping of information from a few websites here or there. However, a website owner how makes it a habit of scraping frequently, will experience the ire from the owner of the scraped website. This is one of the motivations behind website owners changing their HTML code.
It's all about bandwidth. Scraping counts towards their quota for bandwidth. Many web hosting companies claim they offer unlimited bandwidth, but when you read the fine print, you'll find they limit the amount of bandwidth and harddrive space you're allowed. I think this is false advertising, but whatever it is, it is real and webmasters will take action when people are using up an inordinate amount of their bandwidth.
Another potential problem with scraping too much information is you could be faced with legal programs. When webmasters discover websites are scraping, they may have their lawyers contact the scrapers. Yes, they can know where the activity is coming from using tools such as Google Analytics.
If you scrape a website without the owner's permission, you could be in violation of copyright law. If you are lucky, all you'll get is a cease and desist notice from the lawyers. However, every so often, companies like to mark their territories, so to speak. They will extend their legal actions to set examples. Does anyone remember Napster? The recording industry shutdown the company, but also went after individuals. The fines were in the thousands of dollars, too.
There's no telling what could happen if a legal battle ensues. Even if you win, you still have to appear in court and present your case. And, if you lose, you'll have the pay the settlement amount plus court and legal fees. That does not include the fees you'll pay if you have to hire a lawyer to represent you!
Scraping is my least favorite means of obtaining data online for the reasons mentioned previously. My favorite method is using an application programming interface given by the provider of data. This helps to protect you (to some degree) as the provider is granting you access to obtain the data.
Of course, it's probably a good idea to ensure the provider has the legal means to provide you with the data. This is a small risk as it's not likely a pirate would develop an API for data he or she doesn't own, but anything is possible, I suppose.
What Can You Do with Data Provided by GOOGLEFINANCE?
I am not a lawyer and you shouldn't misconstrue this as any type of legal advice. If you're plan is to develop a service for others using data returned from GOOGLEFINANCE, I am going to go out on a limb and state that this probably is not a wise move.
Check with legal council before going down this rabbit hole. First, Google discloses that the data is provided "as is". That means that if the data is wrong and you provide it to someone else as a service, they may try to take action against you.
Second, Google states as part of the disclaimer that the data is for informational purposes only. That statement should be motivation enough to avoid using the data as a service to others.
The scenario gets a bit fuzzier when you are reporting on the data via a blog post. Here, you are not providing the data itself, you are only providing insight about the data. If this is your plan, I would still run it by legal council. It may be perfectly fine to do this, but it's better to get the blessing of someone qualified rather than make assumptions. It is wise to credit the source of data, but people should always do that anyway.
Use a Data Provider
You'll find several providers of financial statement data online. I found one that I thought was useful and inexpensive. It's certainly not the only provider, but it works. The solution adds funtionality to Google Sheets as an add on. The free version does include some basic financial statement data information, but not a complete set.
I was initially going to include a tutorial in this article for this functionality. However, the article is quite long without adding it in, so I'll write the tutorial in a follow-up article.
No matter which provider you choose, if any, align your goals with the terms and services of the provider. I've touched on this earlier in the section, What Can You Do Data Provided by GOOGLEFINANCE?
Conclusion
GOOGLEFINANCE is a worthy function that can provide useful data to financially-inclined people. It provides a quick and easy way to get this data and is portable. Simply log into your Google account and you have access to your sheets, including any that use this function.
The base functionality won't satisfy the true diehard finance types. But it can provide quick information for the companies you track.
