SQL and Investment Performance Measurement

Part V: Visualization The previous part finalized the database function that produces cumulative Cariño scaled Brinson-Fachler performance attribution time series. It is quite natural to visualize these results using charts, and that is what I am developing in this part. For wider access, and easier interpretation, I am using MS Excel to get the data and draw the charts. As this blog is really about SQL, I am not going to go into detail on how to build such an Excel workbook - this is intended more as a tool to actually look at the results provided by the database. Perhaps a more modern tool for visualization would be MS Power BI (or some other BI tool). The Excel file can be found in my GitHub repository , alongside the SQL scripts used in the preceding parts. Obviously, you need a relatively recent version of MS Excel to use it. Making the Connection The Excel workbook already has the required data queries, but it needs to connect to the correct database. To modify the connection,...

Part IV: Cumulative Time Series of Attribution Effects In parts  I  and  II , we set up a test database with performance calculations, and in part III , calculated the daily Brinson-Fachler performance attribution results. Now, I will add another inline user-defined table function to calculate cumulative time series of all the contribution and attribution effects using Cariño scaling . These can then be used alongside a chart of portfolio and benchmark cumulative returns to explain the difference between them. And, of course, taking just the last item of the cumulative time series gives you the full period results only. The function is quite long, some 250 lines of code, so the full version will only be available in my  GitHub repository . However, much of the length is just simple repetition, as the statement must use subqueries to calculate values in phases, and there are a lot of descriptive fields to "carry over" from one subquery to the next. Scaling required T...

Part III: Brinson-Fachler Performance Attribution, Daily In parts I and II , we set up a test database with some data and three views producing performance time series data for different portfolio structures. In this part, I will add two user-defined functions to give us the following: Performance time series of a portfolio and a benchmark combined Daily performance attribution and performance contribution results The functions are rather long, approximately 50 and 160 lines of code, respectively, so the full version of the latter will only be available in my GitHub repository . Benchmarking Let's start by adding benchmark data to our portfolio time series. The view  vPerformanceTimeSeries was developed in Part II and computes portfolio market values, cash flows, and returns in the structures defined in table Structure . In this test environment, any portfolio can act as a benchmark - there are no specialized tables for benchmark data. Thus, we want to combine, for each day and ...

Part I: Setting Up a Database This blog is about how to process investment performance using SQL and a database. For professional use, there are a number of systems available for calculating the numbers, but even then understanding the mechanics may be interesting and/or useful. Also, having total control over the process may enable choices that are not available in the system used, for example on how to handle derivatives, how to set up the portfolio structure, or how and which risk or other external data to use if calculating risk based statistics. It is obviously possible to just download the data, as it is, from the database to Excel or a Python application or similar, and perform the calculations there. While that does have some advantages, it is often more efficient to process the results from the underlying large amount of data in the database server, and only bring the required results over to the client application. At the time writing, I intend to do the following, with pure ...