Custom Watch List of Stock Lecture Notes

Overview

• Learn how to create a custom watch list for stocks.
• Track:
  • Opening price
  • Day's high and low
  • Previous closing price
  • Current price
  • Volume
  • Market cap
  • 52-week high and low
  • PE ratio
  • Percentage performance over 5 and 30 days
  • A customizable chart to visualize stock data over different time periods.

Steps to Create a Watch List

Heading & Stock Name

• Start with the stock name header (e.g. IRFC).
• Example: Type "IRFC" in the heading.

Retrieving Stock Prices

• Opening price formula: =GOOGLEFINANCE(stock name, "open")
• Day's high formula: =GOOGLEFINANCE(stock name, "high")
• Day's low formula: =GOOGLEFINANCE(stock name, "low")

Additional Stock Data

• Previous Close: =GOOGLEFINANCE(stock name, "closeyest")
• Last Trading Price (LTP)/Current Price: =GOOGLEFINANCE(stock name, "price")

Change and Change Percentage

• Change amount: =GOOGLEFINANCE(stock name, "change")
• Change percentage: =GOOGLEFINANCE(stock name, "changepct")

Volume, 52-week High/Low, Market Cap, PE Ratio

• Volume: =GOOGLEFINANCE(stock name, "volume")
• 52-Week High: =GOOGLEFINANCE(stock name, "high52")
• 52-Week Low: =GOOGLEFINANCE(stock name, "low52")
• Market Cap: =GOOGLEFINANCE(stock name, "marketcap")
• PE Ratio: =GOOGLEFINANCE(stock name, "pe")

Formatting

• Select all cells and apply center and middle alignment.
• Format Market Cap and PE Ratio for better readability:
  • Market Cap: Format as "Cr" for crores.
  • PE Ratio: Add rounding and multiplication sign.
• Format Change and Change Percentage with conditional formatting for easy identification:
  • Highlight positive values in green and negative in red.
• Apply formatting to Volume, 52-Week High, and 52-Week Low similar to other values.

Calculating Percentage Changes for 5 and 30 Days

• 5-Day Change: =GOOGLEFINANCE(stock name, "price", TODAY()-5, TODAY()))
  • Formula to get the price 5 days ago and calculate change percentage.
• 30-Day Change: =GOOGLEFINANCE(stock name, "price", TODAY()-30, TODAY()))
  • Scale the formula as needed for other ranges.

Chart Creation

• Insert a chart for stock prices over a period: =SPARKLINE(GOOGLEFINANCE(stock name, "price", WORKDAY(TODAY(),-100), TODAY()))
• Customize chart properties for color and type: =SPARKLINE(data, {"charttype":"column", "color":"green"})

Stock Ticker Entry

• Ensure correct entry of stock ticker (e.g., NSC:BAJAJFINSV for Bajaj Finance).

Final Formatting

• Apply alternate color formatting for better visual presentation.
• Finalize and adjust settings as required.

[Link to download the prepared sheet in the description]