Do you know how to exploit the power of Google Finance inside Google Sheets? No? Well, this guide will help you.
Summary
What is Google Sheets?
What is Google Finance?
Let’s create our first spreadsheet
A little stock watchlist
Conclusions and suggestions
It’s free, like many other Google apps.
We will use Google Finance as a financial database through Google Sheets to build our personalized technical analysis.
In an empty cell, type “=GOOGLEFINANCE”. It’s a prebuild function that requires some information inside the parenthesis, like the ticker of a financial instrument (for example “GOOG”), the attribute (for example “price”), a start and end date, preceded by “DATE” and followed with the numerical date in parenthesis (for example DATE(2020,1,1), DATE(2022,10,31) ), the timeframe (for example “DAILY” or “WEEKLY”).
I suggest to you the reading of the help about this function.
In conclusion, if you type into a blank cell (like A1):
Select the two columns (select the first two cells, A1 and B1, then use this combination of keys: Ctrl + Shift + Down arrow).
Now select the button “Insert Chart” or use the menu “Insert” and choose “Chart”, and you will see the line chart of the data:
It’s easy to add some simple technical indicators to this chart.
In this example I added a new column with the sum of the previous ten values and another column where I have divided this sum by 10, obtaining the average (10):
I suggest a little trick: select the cell with the formula, then double-click with the mouse on the little square in the right-down corner; The formula will be extended to the end of the column.
After these operations, you can select the previous graph and choose “Setup” on the right, then “Add Series”
In add series choose “Select a data range”.
I indicated the range of the average (10), from cell D1 to cell D149 (from cell D1 to cell D11 I have put all zero):
This is the final graph (I changed the name of the graph, too):
We will use this power to build a little stock watchlist.
First of all, open a new blank spreadsheet and call it “Stock watchlist” (or something similar).
In the first row type some names for the columns (look at the image below):
In the Ticker column put the ticker of some popular stocks, for example:
Remember: a ticker is a unique series of letters assigned to an asset for trading purposes. Stocks listed on the New York Stock Exchange (NYSE) can have four or fewer letters.
We the help of the function GOOGLEFINANCE() we can obtain the name of the company owner of the ticket. Write these words in the A2 cell:
This is the result:
In the C2 cell write:
and copy it down.
In the D2 and E2 cells write:
to have the 52-week (one year) high price and the 52-week low price.
You can add other different information in the columns, but I want to propose just one.
In the F1 cell write “100 Day Chart”.
In the F2 cell write:
In the end, this is the final situation:
If you want to understand the meaning of the functions I used to create these little graphs, please read the help of Google Sheets.
A sincere wish of good work to all!
Written by F. GRAMOLA (*).
(*) Member of S.I.A.T., the Italian Society of Technical Analysis (member society of I.F.T.A. – International Federation of Technical Analysts).
Warning
We merely cite our personal opinions for educational purposes only and we are independent of Alphabet Inc.
All trademarks are the property of their respective owners.
Investing and trading are risky. Don't invest or trade money that you cannot afford to lose.
What is Google Finance?
Let’s create our first spreadsheet
A little stock watchlist
Conclusions and suggestions
What is Google Sheets?
Google Sheets (https://sheets.google.com/) is an online spreadsheet application that lets you create spreadsheets.It’s free, like many other Google apps.
What is Google Finance?
It’s a website (https://www.google.com/finance/) on business news and financial information created by Google in 2006. Google Finance is free, too.We will use Google Finance as a financial database through Google Sheets to build our personalized technical analysis.
Let’s create our first spreadsheet
Open a blank spreadsheet and call it “Our first spreadsheet” (or something similar).In an empty cell, type “=GOOGLEFINANCE”. It’s a prebuild function that requires some information inside the parenthesis, like the ticker of a financial instrument (for example “GOOG”), the attribute (for example “price”), a start and end date, preceded by “DATE” and followed with the numerical date in parenthesis (for example DATE(2020,1,1), DATE(2022,10,31) ), the timeframe (for example “DAILY” or “WEEKLY”).
I suggest to you the reading of the help about this function.
In conclusion, if you type into a blank cell (like A1):
=GOOGLEFINANCE("GOOG", "price", DATE(2020,1,1), DATE(2022,10,31), "WEEKLY")
Screenshot by Author (Source: Google Sheets).
Select the two columns (select the first two cells, A1 and B1, then use this combination of keys: Ctrl + Shift + Down arrow).
Now select the button “Insert Chart” or use the menu “Insert” and choose “Chart”, and you will see the line chart of the data:
Screenshot by Author (Source: Google Sheets).
It’s easy to add some simple technical indicators to this chart.
In this example I added a new column with the sum of the previous ten values and another column where I have divided this sum by 10, obtaining the average (10):
Screenshot by Author (Source: Google Sheets).
I suggest a little trick: select the cell with the formula, then double-click with the mouse on the little square in the right-down corner; The formula will be extended to the end of the column.
Screenshot by Author (Source: Google Sheets).
After these operations, you can select the previous graph and choose “Setup” on the right, then “Add Series”
Screenshot by Author (Source: Google Sheets).
In add series choose “Select a data range”.
Screenshot by Author (Source: Google Sheets).
I indicated the range of the average (10), from cell D1 to cell D149 (from cell D1 to cell D11 I have put all zero):
Screenshot by Author (Source: Google Sheets).
This is the final graph (I changed the name of the graph, too):
Screenshot by Author (Source: Google Sheets).
A little stock watchlist
Now you know the power of Google Sheets connected with Google Finance.We will use this power to build a little stock watchlist.
First of all, open a new blank spreadsheet and call it “Stock watchlist” (or something similar).
In the first row type some names for the columns (look at the image below):
Screenshot by Author (Source: Google Sheets).
In the Ticker column put the ticker of some popular stocks, for example:
Screenshot by Author (Source: Google Sheets).
Remember: a ticker is a unique series of letters assigned to an asset for trading purposes. Stocks listed on the New York Stock Exchange (NYSE) can have four or fewer letters.
We the help of the function GOOGLEFINANCE() we can obtain the name of the company owner of the ticket. Write these words in the A2 cell:
=GOOGLEFINANCE(B2,"NAME")
This is the result:
Screenshot by Author (Source: Google Sheets).
In the C2 cell write:
=GOOGLEFINANCE(B2,"price")
and copy it down.
In the D2 and E2 cells write:
=GOOGLEFINANCE(B2,"high52")
and=GOOGLEFINANCE(B2,"low52")
to have the 52-week (one year) high price and the 52-week low price.
You can add other different information in the columns, but I want to propose just one.
In the F1 cell write “100 Day Chart”.
In the F2 cell write:
=SPARKLINE(INDEX(GOOGLEFINANCE(B2,"price",workday(today(),-100),today()),,2))
and drag it down.
In the end, this is the final situation:
Screenshot by Author (Source: Google Sheets).
If you want to understand the meaning of the functions I used to create these little graphs, please read the help of Google Sheets.
Conclusions and suggestions
Takeaways:- Google Finance is a big and powerful financial database that we can use through Google Sheets to build our personalized technical analysis and watchlists.
- Google gives us a lot of great and free instruments on the internet: learn to use them!
Written by F. GRAMOLA (*).
(*) Member of S.I.A.T., the Italian Society of Technical Analysis (member society of I.F.T.A. – International Federation of Technical Analysts).
Warning
We merely cite our personal opinions for educational purposes only and we are independent of Alphabet Inc.
All trademarks are the property of their respective owners.
Investing and trading are risky. Don't invest or trade money that you cannot afford to lose.
Initial Photo by RODNAE Productions on Pexels.
Post a Comment