Creating a Custom Stock Watchlist

Overview of Stock Information

• *Stock details included:

  • Open price
  • Day's high and low prices
  • Previous closing price
  • Current price
  • Change in stock price (amount and percentage)
  • Trading volume
  • Market capitalization
  • 52-week high and low prices
  • PE ratio
  • Performance over 5 days and 30 days
  • Chart for stock trends (adjustable for 10, 60, 100 days, etc.)

• Automatic updates: Only the stock name needs to be updated; data populates through automatic updates.

Steps to Create the Watchlist

Adding the Stock Name

1. Write the stock name in the heading (e.g., IRFC).

Stock Information Formulas

2. Open, High, and Low Prices:

   • Use =GOOGLEFINANCE(stock_name, "priceopen")
   • Use =GOOGLEFINANCE(stock_name, "high")
   • Use =GOOGLEFINANCE(stock_name, "low")

3. Previous Close and Current Price:

   • Previous Close: =GOOGLEFINANCE(stock_name, "closeyest")
   • Current Price: =GOOGLEFINANCE(stock_name, "price")

4. Price Change (amount and percentage):

   • Change: =GOOGLEFINANCE(stock_name, "changepct")
   • Change %: Calculate manually with the appropriate formula.

Volume, Market Cap, 52-Week Range, and PE Ratio

5. Volume: =GOOGLEFINANCE(stock_name, "volume")
6. 52-Week High: =GOOGLEFINANCE(stock_name, "high52")
7. 52-Week Low: =GOOGLEFINANCE(stock_name, "low52")
8. Market Cap: =GOOGLEFINANCE(stock_name, "marketcap")
9. PE Ratio: =GOOGLEFINANCE(stock_name, "pe")

Formatting and Custom Number Formats

10. Cell Formatting:
    • Use custom number formats for readability (e.g., in crores or lakhs)
    • Adjust market cap for better visibility using custom format
    • Round off PE ratios and add custom signs/symbols if needed
11. Change and Change Percentage Formatting:
    • Show positive values in green and negative values in red
    • Custom format: plus;minus approach for gains/losses

Performance Over Time

12. Performance Over 5 and 30 Days:
    • Calculate past prices using date offsets and stock data
    • Combine functions to retrieve and calculate performance percentages.
    • Use custom formats like 5-day % or 30-day %

Stock Chart

13. *Creating the Chart:
    • Use =SPARKLINE to create the stock trend chart over custom periods (e.g. 100 days)
    • Incorporate dynamic date ranges and color formatting

Final Adjustments

14. Updating Formatting:
    • Apply alternating colors or other visual styles to enhance readability

Example Flow

1. Enter Stock Name: Check the correct stock symbol (e.g., BAJAJFINSV.NS for Bajaj Finance Limited)
2. Data Population: Formulas will fetch the relevant stock data
3. Chart Updates: Adjust the date ranges to see trend changes