Google Sheets Guide

No coding required. Free. Takes about 10 minutes.

Build a live stock tracking sheet that pulls real-time data automatically — the same data powering this site, in your own spreadsheet.

---

GOAL What You'll Build

A personal live stock tracking sheet that:

• Pulls real-time price data automatically via GOOGLEFINANCE()
• Shows price, change %, change $, volume, 52-week high
• Highlights gainers green and losers red automatically
• Filters to only stocks with options available
• Is yours to customize however you want — no API key, no cost

---

01 Get the Data

1. Go to stock-porn.loyal9.app/pages/data.html
2. Download stocks_optionable.csv
3. Save it to your computer

This CSV contains every NYSE listed stock with options available — ticker symbol, company name, and sector. Updated monthly.

---

02 Open Google Sheets

1. Go to sheets.google.com
2. Click + to create a new blank spreadsheet
3. Name it something like My Stock Sheet

---

03 Import the CSV

1. Click File → Import
2. Click Upload → select the stocks_optionable.csv you downloaded
3. Import location: Replace current sheet
4. Separator type: Comma
5. Click Import data

You should now see columns:

A: Symbol  |  B: Name  |  C: Sector  |  D: options_available

---

04 Add Live Price Columns

Click on cell E1 and add these headers:

Cell	Header
E1	Price
F1	Change $
G1	Change %
H1	Volume
I1	52W High

Now enter these formulas starting in row 2:

=IFERROR(GOOGLEFINANCE(A2,"price"),"—")         ← E2: current price
=IFERROR(GOOGLEFINANCE(A2,"change"),"—")         ← F2: dollar change
=IFERROR(GOOGLEFINANCE(A2,"changepct")/100,"—")  ← G2: percent change
=IFERROR(GOOGLEFINANCE(A2,"volume"),"—")         ← H2: today's volume
=IFERROR(GOOGLEFINANCE(A2,"high52"),"—")         ← I2: 52-week high

---

05 Apply Formulas to All Rows

1. Select cells E2:I2
2. Copy them (Ctrl+C)
3. Select from E3 all the way down to the last row of your data
4. Paste (Ctrl+V)

All rows will now pull live data automatically.

---

06 Format the Sheet

Format Change % as percentage:

1. Select column G
2. Click Format → Number → Percent

Format Price and Change $ as currency:

1. Select columns E and F
2. Click Format → Number → Currency

Freeze the header row:

1. Click View → Freeze → 1 row

---

07 Add Color

Make gainers green and losers red automatically:

1. Select column G (Change %)
2. Click Format → Conditional formatting
3. Add rule: Greater than 0 → fill color green
4. Add rule: Less than 0 → fill color red

Repeat for column F (Change $) if you want both colored.

---

08 Filter to Your Watchlist (Optional)

To track only specific stocks:

1. Click Data → Create a filter
2. Click the filter arrow on column A (Symbol)
3. Search for and select only the tickers you want to watch

Or create a second sheet tab called Watchlist, manually enter your tickers in column A, and use the same GOOGLEFINANCE formulas in columns B onward.

---

REF GOOGLEFINANCE Formula Reference

Formula	Returns
=GOOGLEFINANCE(A2,"price")	Current price
=GOOGLEFINANCE(A2,"change")	Dollar change today
=GOOGLEFINANCE(A2,"changepct")	Percent change today
=GOOGLEFINANCE(A2,"volume")	Today's volume
=GOOGLEFINANCE(A2,"high52")	52-week high
=GOOGLEFINANCE(A2,"low52")	52-week low
=GOOGLEFINANCE(A2,"pe")	Price to earnings ratio
=GOOGLEFINANCE(A2,"marketcap")	Market cap
=GOOGLEFINANCE(A2,"shares")	Shares outstanding

---

PRO Pro Tips

Check if a stock is near its 52-week high:

=IFERROR(E2/GOOGLEFINANCE(A2,"high52"),"—")

A value close to 1.0 = near 52-week high. Close to 0.5 = middle of its range.

Pull 30 days of price history:

=GOOGLEFINANCE(A2,"close",TODAY()-30,TODAY())

Paste into a separate area and chart it. Poor man's TOS.

Sort by biggest movers:

1. Click Data → Sort range
2. Sort by column G (Change %) descending
3. Biggest gainers float to the top

---

Not a financial advisor. Built this to earn beer money.
🍺 Buy the dev a beer  ·  Legal