📊

Creating a Custom Stock Watchlist

Jun 22, 2024

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

  1. Open, High, and Low Prices:

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

  2. Previous Close and Current Price:

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

  3. 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

  1. Volume: =GOOGLEFINANCE(stock_name, "volume")
  2. 52-Week High: =GOOGLEFINANCE(stock_name, "high52")
  3. 52-Week Low: =GOOGLEFINANCE(stock_name, "low52")
  4. Market Cap: =GOOGLEFINANCE(stock_name, "marketcap")
  5. PE Ratio: =GOOGLEFINANCE(stock_name, "pe")

Formatting and Custom Number Formats

  1. 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
  2. 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

  1. 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

  1. *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

  1. 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

Full transcript