Introducing our own tools: How to track and analyse stocks with GCS
GCS is a tool to import stock data directly into Google Spreadsheets. This brings an easy approach for a user to create their own spreadsheets and models for stocks they want to track and analyse.
GCS spreadsheet tool
One of the most challenging aspects of investing is tracking and analysing stocks. Google Sheet's own finance functions are limiting and it can be difficult to set up alternatives using APIs or rely on costly, inflexible financial products.
We built this tool so you could get hands-on with the data quickly and easily. GCS (v2.0) extends the financial functions you can use in Google Sheets for your own use.
What are the main benefits of GCS tool?
Don't miss any stock data - GCS (v2.0) can easily retrieve specific live data for US stocks. It can also retrieve key financials (10yrs), income statement (5yrs), balance sheet (5yrs), and cash flow statement (5yrs) for world stocks. This expands Google's inbuilt financial greatly, enabling you to do your own analysis on stock data.
Beginner friendly - Easy to set up (no API set up or coding knowledge needed). Packed with functions to get fundamental data (e.g. Industry, Debt/Eq, EPS, Gross Margin, Shares outstanding etc). GCS (v2.0) provides an easy but comprehensive way to access market data.
Fit your workflows - Since GCS (v2.0) works in Google Sheets, you can use it to supplement existing spreadsheets or use it as a base to create your own spreadsheet models to forecast things like future revenue, cash flow and EBITDA.
Can I see an example of it?
You can see a sample of it here: GCS Sample Spreadsheet (view only). To get the spreadsheet yourself or get the script to build it from scratch, subscribe to Giles Capital below and it will be included free in our welcome email.
Example features
Start retrieving specific values (US Stocks only)
A list of values you can retrieve can be viewed here.
Select any cell
Input the following formula to retrieve any desired values using the relevant key:
Retrieve specific value formula:
=ImportJSON("STOCK NAME", "KEY")
The value should appear. For example, in the case below where aapl is the stock name and the desired value (key) is ‘pe’ which is the P/E ratio.
Note: In the place of "STOCK NAME" and "KEY", you may reference other cells e.g. if the STOCK NAME is in the cell A3 you can use =ImportJSON(A3, “pe”).
Start retrieving larger datasets (Key financials, income statement, balance sheet, cash flow) (Works with global stocks)
Select any cell
Input any of the following formulas to retrieve any of the larger data sets:
Key financials formula:
=ImportStockHistoryKeyRatio("STOCK CODE", "EXCHANGE CODE)
Income statement formula:
=ImportStockHistory("STOCK CODE", "EXCHANGE CODE", "is")
Balance sheet formula:
=ImportStockHistory("STOCK CODE", "EXCHANGE CODE", "bs")
Cash flow formula:
=ImportStockHistory("STOCK CODE", "EXCHANGE CODE", "cf")
Note: To find the correct stock or exchange codes, you can search for the stock on morningstar.com and reference the URL.
Example:
Let’s use the ‘Key financials’ formula above. In the place of "STOCK CODE" and "EXCHANGE CODE", you may reference other cells e.g. if the STOCK NAME and EXCHANGE CODE are in cells B1 and D1 respectively, you can use =ImportStockHistory(A1, B1).
This would obtain the key financials starting from the cell in which you put the formula in:
What are some example uses of the script?
Set up a spreadsheet to scan stocks - Use a combination of Google Finance features and GCS (v2.0) to look to scan for stocks which have dropped the most from last year, or ones that have the biggest dividends.
Set up a spreadsheet to have all the financial data of on stock - Use GCS (v2.0) to retrieve the key financials so and start looking into whether the company is growing revenue, if it has retained earnings and so on.
Do your own calculations on the stock - Use GCS (v2.0) to retrieve the income statement, balance sheet and cash flow statement, to look at the cash flow of a company and compare the EV/EBITDA of it overtime.
Want to get access?
Subscribe to Giles Capital below and the GCS tool will be included free in our Welcome email. You’ll also get a free newsletter with our analysis & recommendations, but you are free to unsubscribe or only subscribe to updates about GCS script if you prefer.
Disclosure:
This is an experimental tool in beta. Access and accuracy is not guaranteed and should be used for educational purposes only.
Disclaimer:
This is not investment advice. Our content to be used for informational purposes only. It is important you do your own analysis before making any investment based on your own personal circumstances.
Stay invested and follow Giles Capital on Twitter at @GilesCapital.