Follow the steps mentioned below to access financial data using Excel Connect in Google Sheets:
1. Select Trendlyne-Excel Connect in the Data Downloader section and click OPEN IN GOOGLE SHEETS.
2. Next, click Make a Copy.
3. Copy the token from the website and paste it into the highlighted cell of the Google sheet.
4. Click Validate Token and Fetch Stocks Master List.
5. To run the configured script in Google Sheets, you’ll need to grant certain permissions. Simply click “OK” to authorize the script and proceed.
6. Select one of your Google accounts.
7. Click on Continue and Allow in subsequent screens to complete the authorization process.
8. After granting access, click on Validate Token and Fetch Stocks Master List to run the script. The script will run and populate the “Stock Master List” sheet with all listed stocks.
9. You can view the list of the latest stocks available, as highlighted in the image. Please don’t edit/ delete any of the cells present in this sheet.
10. From the dropdown in cell G5, search for the stock for which you want to fetch financial data.
11. Click Fetch Financials in cell B7 to retrieve the latest financial data for the selected stock.
12. The status of the financial data fetch will be displayed in the highlighted cells.
13. You can check the highlighted sheets to see the financial data.
Quarterly - Raw Data sheet contains Quarterly financial data
Annual - Raw Data contains Annual financial data including P&L, Cash Flow, and Balance Sheet parameters.
Please avoid editing or deleting any cells in these sheets, as they serve as data sources for the following sheets in the Google sheet
- Quarterly - P&L
- Annual - P&L
- Balance Sheet
- Cash Flow
14. Quarterly - Raw Data sheet has standalone parameters at the top and consolidated parameters from the 125th Row
Annual - Raw Data has Standalone parameters at the top and consolidated parameters from the 300th Row.
15. These four sheets must be used for stock analysis to view their respective Quarterly P&L, Annual P&L, Balance Sheet, and Cash Flow parameters.
16. Some parameters are pre-filled for you in all of these four sheets for your reference. The data source for the Quarterly - P&L sheet is Quarterly - Raw Data.
Annual - P&L, Balance sheet, and Cash Flow is Annual - Raw Data.
You can click on any of the cells to see the formula in the formula bar.
17. Some parameters are derived using a custom formula based on the fetched data. These parameters are highlighted for your convenience. Click on any highlighted cells to see the formula used.
In the image below, a new derived parameter named “Basic EPS Qtr change%” has been created using formulas on the parameter “Basic EPS Qtr” in the 17th row of the same sheet. This row is also color-coded using conditional formatting for better visualization.
18. Now, you can do the following:
- Fetch financials for any other stock through the dropdown present in the G5 cell of the Main Dashboard.
- Add a new parameter in the 4 mentioned sheets using parameters present in the Quarterly Raw data and Annual - Raw data sheet.
- To create a new custom parameter, simply add the desired formulas in the cell. For more details, please refer to the following FAQ: How can I make a new custom parameter?.