Stock Portfolio Tracker Formulas

Stock Portfolio Tracker Formulas
2020.12.30 by Michael Jay

For use with this video: https://youtu.be/GqB4Oe0SkHU

Formulas for Copy and Paste

Note: You must set up your worksheet the same as in the video for these formulas to work properly. Paste these formulas in their respective columns in row 8. The first formula shown is the more advanced one I use, but I have also provided a simpler alternative formula you can use as well.

Rank - Column B
=IF(ISBLANK(C8),"", IFERROR(RANK(E8,$E$6:$E$25),""))
=RANK(E8,$E$6:$E$25)
**You may need to expand the $E$6:$E$25 range as you add more stocks

Allocation (%) - Column E
=IFERROR(IF(OR(P8=0,ISBLANK(C8)),"",P8/SUMIFS(P:P,$C:$C,"<>")),"")
=P8/SUMIFS(P:P,$C:$C,"<>")

Cost Basis (%) - Column F
=IFERROR(IF(OR(R8=0,ISBLANK(C8)),"",R8/SUMIFS(R:R,$C:$C,"<>")),"")
=R8/SUMIFS(R:R,$C:$C,"<>")

Size - Column J
=IFERROR(VLOOKUP(K8, 'Lookup Tables'!$B$4:$C$7,2,TRUE),"")
=VLOOKUP(K8, 'Lookup Tables'!$B$4:$C$7,2,TRUE)
**You must create the lookup table exactly as in the video or update the table to the correct reference.

Market Cap ($B) - Column K
=IFERROR(IF(G8="Stock",GOOGLEFINANCE(C8,"marketcap")/10^9,""),"")
=GOOGLEFINANCE(C8,"marketcap")/10^9

Unit Basis ($) - Column N
=IF(ISBLANK(C8),"",R8/M8)
=R8/M8
**Use as a formula if you are inputting the amount paid as your cost basis input.

Price ($) - Column O
=IF(ISBLANK(C8),"",GOOGLEFINANCE(C8))
=GOOGLEFINANCE(C8)

Position Value ($) - Column P
=IF(ISBLANK(C8),"",M8*O8)
=M8*O8

Amount Paid ($) - Column R
=IF(ISBLANK(C8),"",M8*N8)
=M8*N8
**Use as a formula if you are inputting the unit basis as your cost basis input.

Total Return ($) - Column S
=IF(ISBLANK(C8),"",P8-R8)
=P8-R8

Total Return (%) - Column T
=IF(ISBLANK(C8),"",S8/R8)
=S8/R8

Portfolio Return (%) - Column U
=IF(ISBLANK(C8),"",S8/SUMIFS(R:R,$C:$C,"<>"))
=S8/SUMIFS(R:R,$C:$C,"<>")


Custom Number Formats for Return Columns

$ Return: 0.0%;[Red](0.0%)
% Return: #,##0.00_);[Red](#,##0.00)