Trying to get this to work, column K is the issue, is the date syntax right for YTD?
| Stock+Percent+Change+Example.xlsx | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 3 | Helper Data | ||||||||||||||
| 4 | Stock | Current | YTD | 1 Year | 5 Years | Begin Yr | 1 Yr Ago | 5 Yrs Ago | |||||||
| 5 | MICROSOFT CORPORATION (XNAS:MSFT) | $ 290.73 | #VALUE! | #VALUE! | (8%) | ▼ | 215% | ▲ | #VALUE! | $ 314.97 | $ 92.33 | ||||
| 6 | APPLE INC. (XNAS:AAPL) | $ 168.88 | #VALUE! | #VALUE! | (5%) | ▼ | 294% | ▲ | #VALUE! | $ 178.44 | $ 42.90 | ||||
| 7 | TESLA, INC. (XNAS:TSLA) | $ 876.35 | #VALUE! | #VALUE! | 130% | ▲ | 4,481% | ▲ | #VALUE! | $ 381.81 | $ 19.13 | ||||
| 8 | NETFLIX, INC. (XNAS:NFLX) | $ 386.67 | #VALUE! | #VALUE! | (1%) | ▼ | 34% | ▲ | #VALUE! | $ 391.50 | $ 288.94 | ||||
Stocks | |||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C5:C8 | C5 | =B5.Price |
| D5:D8 | D5 | =C5/K5-1 |
| E5:E8,G5:G8,I5:I8 | E5 | =IF(D5<0,"▼","▲") |
| F5:F8 | F5 | =C5/L5-1 |
| H5:H8 | H5 | =C5/M5-1 |
| K5:K8 | K5 | =INDEX(STOCKHISTORY($B5,"1/1/" & YEAR($C$2),"1/31/"& YEAR($C$2)),2,2) |
| L5:L8 | L5 | =INDEX(STOCKHISTORY($B5,DATE(YEAR($C$2)-1,MONTH($C$2),DAY($C$2)),DATE(YEAR($C$2)-1,MONTH($C$2),DAY($C$2)+30)),2,2) |
| M5:M8 | M5 | =INDEX(STOCKHISTORY($B5,DATE(YEAR($C$2)-5,MONTH($C$2),DAY($C$2)),DATE(YEAR($C$2)-5,MONTH($C$2),DAY($C$2)+30)),2,2) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| 1:1048576 | Expression | =A1="▲" | text | NO |
