Google Sheets is a powerful tool that can be used for a variety of purposes, including stock analysis. In this guide, we will walk you through the steps necessary to perform stock analysis in Google Sheets. We will cover everything from importing data to creating graphs and calculating important metrics. By following this guide, you can perform all your stock analysis in Google Sheets and get accurate results in no time!
What is Google Sheets, and how do you use it to analyze stocks?
Google Sheets is a free online spreadsheet application that is part of the Google Docs suite. It offers almost all of the features of Microsoft Excel and is widely used by businesses and individuals for a variety of purposes, including stock analysis.
To use Google Sheets for stock analysis, you first must import the data into the sheet. This can be done manually by entering the data or using the Wisesheets add-on. Once the data is in the sheet, you can start performing your analysis.
Some of the things you can do with Google Sheets for stock analysis include:
- Create graphs and charts to visualize your data
- Calculate important metrics like return on investment (ROI)
- Analyze trends over time
- Compare different stocks side-by-side
All of these things can be done easily in Google Sheets, and we will show you how to do them in this guide.
Keep reading to learn how to perform stock analysis in Google Sheets!
How to import stock data into Google Sheets
There are many ways to import stock data into your spreadsheet, such as manually copy-pasting the data from a site like Yahoo Finance, using an API, scrapping a website, using the Google Finance function, and using the Wisesheets add-on. However, the first set of methods tends to be time-consuming, ineffective, and expensive that's why in this article, we will focus on the last two.
The GOOGLE FINANCE FUNCTION
The easiest way to get stock data in your google sheet spreadsheet is to use the built-in =GOOGLEFINANCE function. We have an extensive tutorial you can check out on the subject here, but here are the basics.
Using the Google Finance Function, you can get real-time stock data for stocks, ETFs, and other asset classes covered by Google Finance across 50+ global exchanges.
Using the following function syntax =GOOGLEFINANCE("ticker", "attribute"). You can cess this information with ease. For example, to get apple's latest stock price, all you have to do is enter =GOOGLEFINANCE("aapl", "price")
There are 20 attributes available in the function, which you can check out here.
In addition, to live price data, you can also access historical stock price data using the function. The function syntax is slightly different, though.
=GOOGLEFINANCE("ticker", "metric", start date, end date, interval)
To get the historical price data for Apple from January 01, 2021 to May 24, 2021 you need to enter: =GOOGLEFINANCE("AAPL", "price", "01/01/2021","24/05/2021", "daily")
For a list of the available historical attributes, click here.
Wisesheets
Wisesheets also provides real-time and historical pricing data for stocks in Google Sheets but what's most impressive is that you can also get historical financials, key metrics, growth metrics, dividend data, revenue breakdowns, and more.
You can click here for a complete add-on guide; however, here are some of the most powerful things you can do.
Enter the company of your choice on the statement dump menu of the add-on and get all financial statements, key metrics, and growth metrics at once.
Use the WISE function to get only the specific data you are looking for. The function syntax is simple =WISE("ticker", "parameter", "period", "quarter"). Using this function, you build models like this that allow you to easily compare companies.
Furthermore the WISEPRICE function is also available with the syntax =WISEPRICE("ticker", "parameter", "end days", "start date", "end date"). This function is very similar to the Google Finance function but includes additional data and the ability to refresh this data any time you want.
Altogether Wisesheets covers 50+ global exchanges and includes the following data parameters per function, which you can find here.
How to create a basic stock analysis spreadsheet
In this section, we will show you how to create a very basic Google Sheets stock analysis spreadsheet. This will include creating a stock comparison spreadsheet and doing some calculations.
Creating your stock list
The first thing you need to do is find a list of companies you would like to analyze and paste them into a new spreadsheet. You can easily Google a stock list by searching for the type of stock list. For example, "biotechnology stock list".
Once you have your stock list ready, then you need to choose which parameters you would like to compare companies by. Again, you can see the whole list here. In this case, we will focus on important fundamental metrics.
Then use the WISE, WISEPRICE, and GOOGLEFINANCE functions to get the data you need. In this case, only the WISE and WISEPRICE functions are required.
Once you set up your function correctly by locking down the cells, as you can see above, you can simply drag the function down and get all the data you need.
Now you can make any formatting changes you like to make your stock list look as you wish.
How to perform fundamental analysis on stocks in Google Sheets
In order to do a fundamental analysis of stocks in Google Sheets, you need to find data that is not readily available in the Google Finance function. The best way to get this data is using Wisesheets.
Financial statement analysis
The first step is finding the company's balance sheet, income statement, and cash flow statement. For how to analyze each of these in detail, check out this article.
The simplest way to analyze each of these financial statements is to look at the difference between years and quarters amongst the most important financial items for that particular type of company. For example, when analyzing technology stocks, it is very important to look at the revenue, research and development expense, capital expenditures, free cash flow, etc.
This allows you to see if the company is growing, how well they are managing their cash flow and how solid is their current financial position.
Trend analysis
Trend analysis is a technique in which you use visual representations to help you assess the general direction of particular financial statement items. Using the statement dump feature, you can insert a column in your spreadsheet and add sparklines, so your spreadsheet looks like this:
This is a very powerful technique, as you can see at one glance where particular financial statement items are trending and where they are likely to go in the future.
Percentage of analysis
One of the best ways to assess how different financial statements are changing over time relative to others is by performing a percentage change analysis. This is where you calculate the percentage change of different financial statement items to each other. For example, for Apple, you can see that the revenue is growing faster than the research and development expenses (in the last year) which is a very good sign.
The formula to calculate the percentage change is (latest number – previous number)/previous number. Once you calculate this for a particular value, you can drag it across to get the historical numbers.
Tips for improving your stock analysis skills with Google Sheets
There are a few things you can do to improve your stock analysis skills with Google Sheets. The first thing is to practice. The more you use Google Sheets, and the more you play around with the functions, the better you will become at using them.
Another tip is to read articles and watch tutorials on how to use Google Sheets for stock analysis. This will help you learn new tips and tricks that you can use to improve your analysis. You can check out our Youtube channel, where we have many tutorials on the subject.
One more tip is to learn from pre-built stock analysis templates that inspire you to perform your own analysis in the way you want. You can leverage the Wise templates functionality to access templates made by the community and make any changes you'd like.
Finally, try to find a mentor or someone who is experienced in stock analysis. This person can help you understand the process and advise you on improving your skills.
How to export your stock analysis data into a PDF or Excel file
Once you have finished your analysis, you may want to export your data into a PDF or Excel file. To do this, simply go to File > Download as > PDF or Excel. You can then save the file to your computer and view it anytime.
Exporting your data into a PDF or Excel file is a great way to share your analysis with others or keep a record of it for yourself. It also allows you to view the data in a different format than Google Sheets.
Conclusion
Google Sheets is a great tool for performing stock analysis. It is easy to use and has many powerful functions that can help you perform your analysis quickly and efficiently. By following the steps in this guide, you will be able to set up your own stock analysis spreadsheet and start performing your own analysis. Thanks for reading! We hope this guide was helpful.
