SQL and Investment Performance Measurement

Part II: Measuring Returns This blog is about how to process investment performance using SQL and a database. In the first part , we set up a test database with seven tables, and populated the tables with some test data. In this second part, I will build some views in the database to calculate portfolio returns with daily time series of market values and cash flows. These returns will be presented in both absolute (currency) and relative (percentage) terms, and on all portfolio structure levels for all structures defined. But first, I'll set up an indexed view on portfolio structures. All the code for the three views can be found in my  GitHub repository . As the SQL statements are at times quite long, they are not necessarily copied here in full, so I recommend GitHub for those interested. Indexed View Sometimes Helps The way I have set up the Structure table is the following: select * from PerfTest. Structure The table is simple; each structure has a ShortName , a LongName...

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 ...