Build a financial portfolio in Excel using linked data types and spill functions
This tutorial showcases some techniques of modern Excel: tables and structured references, financial linked data types and the STOCKHISTORY() function, the useful SUMIFS(), functions that spill including UNIQUE() and SORT(), sparklines and conditional formatting with icons. The case study demos how to construct a financial portfolio balance based and calculate profit / loss on a set of trades.
If you want to follow along, you can download the Excel workbook shown in the video from lbag-online/Portfolio at master · MarkWilcock/lbag-online (github.com)
Timings:
00:00 Introduction
00:25 Explaining the data
01:12 Create an Excel table of the trades
01:38 Use structured references in formulas within tables
01:58 Create a financial (Stocks) linked data types for the equities
03:10 Use UNIQUE() to get portfolio balance items
03:32 Use SORT()
03:42 Add attributes of the linked data type
04:08 Use SUMIFS() to calculate the net balance of each equity
05:05 Use the # operator to reference the results of a spilled array
06:00 Use arrow icon to show equity price movements
06:40 Add a Totals row
07:40 Use STOCKHISTORY() to get daily prices over a year
09:10 Insert a sparkline of the daily price history
09:40 Conclusion