Build a Stock Watchlist with Live Data in Google Sheets

Summary

The video demonstrates how to build a live stock watchlist in Google Sheets using GOOGLEFINANCE. The sheet updates when the user enters a valid, exchange-specific stock symbol.

Data fields

The watchlist includes:

• Open, daily high and low, previous close, and current price (LTP)
• Daily price and percentage change
• Trading volume, market capitalization, and P/E ratio
• 52-week high and low
• Performance over 5 and 30 days
• A configurable historical-price sparkline chart

Workflow and formulas

1. Enter an exchange-specific stock symbol. The tutorial cautions that entering only a company name may not retrieve data correctly.
2. Use GOOGLEFINANCE attributes to retrieve price data and other available metrics.
3. Calculate the daily price change and format gains and losses with plus or minus signs and green or red colors.
4. Retrieve a historical price for a selected lookback period, then calculate the return as the current price divided by the earlier price, minus one.
5. Use a historical price series with SPARKLINE to create an in-cell chart. The lookback period can be adjusted; examples include 100, 200, and 250 days.
6. Format market capitalization in crores, P/E as a multiple, and volume and 52-week values for readability. Apply alternating row colors to the completed table.

Assets and figures mentioned

• Stocks: IRFC, Bajaj Finance, and Tata Motors
• Lookback periods: 5 days and 30 days for performance; 100, 200, and 250 days for chart examples
• 52-week data: Highs and lows are included
• No specific stock prices, yields, valuation figures, or performance results are stated.

Cautions and disclosures

The tutorial recommends using an exchange-specific symbol because stock data may not populate if the symbol is entered incorrectly. No explicit financial-advice disclaimer is stated.

Presenter/source: The speaker is unnamed in the subtitles. The video is from Simple Tutorials.