Thursday 16 January 2014

Portfolio performance measurement in the SQL Server

For the last few weeks I was working on a project of measuring a (asset management) portfolio return and implementing it in the SQL server.

It was an interesting project from both finance and IT points of view. I thought I would share it - it might be relevant to others' as there isn't that much documentation (on the actual implementation) as I found out when I was doing research for the project. I would also welcome any feedback.

Finance background: There are 3 common methods of measuring portfolio performance: Time Weighted Rate of Return (TWRR), Money Weighted Rate of Return (MWRR) and the Modified Dietz method (MDietz).

The TWRR method is the one that is recommended by the Global Investment Performance Standards (GIPS) as it allows direct comparison of performance results between different asset managers. It isn't influenced by the amount of money invested in the portfolio (or fund) on any given moment. The period returns in this methodology are geometrically chain-linked as if the monetary amount invested in each of the periods was 1. This measure thus makes the most sense for evaluation of the asset manager's performance irrespective of the fluctuation of the amount of money under his/ her management (of which he/she usually has no control).

On the other hand, the MWRR (which is the same concept as the Internal Rate of Return, IRR - or the XIRR function as implemented in Excel) is influenced by the amount of money at any given period. Imagine that we had $10 invested in the first period and only $1 in the second one. Using MWRR, the fund's return will be influenced 10x more by the first period return than the 2nd period's return. This is a suitable measure if you want to evaluate performance of an asset manager that has full discretion of the amount of money invested at any time (or to evaluate your own, investor's, performance when trying to time the markets).

The MDietz method is an approximation method of the MWRR. In the formula, the numerator is the amount of money earned on the investments. The denominator is the weighted average amount of money for the the given period.

Implementation: The pre-requisites for the computation of the 3 performance measures are as follows:
  1. Available valuations of the portfolios on any day of in/out flows in the portfolio (TWRR). This technically means that you need to have daily valuations of the portfolio (at the level of granularity you're using for your calculations, e.g. per asset class etc) available to feed your computations. For the MWRR and MDietz methods you only need the begining and ending market values for the whole period. N.B. that the market values of the portfolio on any given day need to include accrued interest for coupon paying fixed income securities.
  2. Dated list of all transactions that occurred in the period.
  3. Dated list of all income (dividends, coupons) received and any expenditures (fees, taxes...) paid in the period.
  4. If your transactions do not include any bond redemptions (due to their maturing, calls, puts), you will need to include them as well.

I implemented the functions directly inside the SQL server. As said before, you can use Excel for MWRR -- using the XIRR function -- however be aware that Excel's implementation of XIRR is notoriously unreliable.

In order not to reinvent the wheel (and write all the functions from scratch), I used the fantastic package of CLR functions for the SQL server - the XLeratorDB/financial (http://westclintech.com) - which comes with the 3 above mentioned functions as standard.

In order to facilitate the usage of their functions, I wrapped the XLeratorDB functions in my UDF (user defined functions) like so (This is a simplified version of my actual queries):

TWRR:

ALTER FUNCTION [TWRR]
( @Portfolio AS char(100),
  @From_date AS date,
  @To_date AS date
)
RETURNS float
AS
BEGIN

RETURN
(

SELECT wct.TWRR(c,d,mv) as TWRR
    FROM (
                --transactions
                SELECT [Date], sum(Amount), 'False'
                FROM Transactions
                WHERE Portfolio = @Portfolio
                AND [Date] BETWEEN @From_date AND @To_date
                GROUP BY [Date]

                UNION ALL

                 -- Income (dividends, interest...)- they are effectively outflows from the portfolio (fees and taxes are treated as inflows)
                SELECT [Date], -sum(Amount), 'False'
                FROM Income
                WHERE Portfolio = @Portfolio
                AND [Date] BETWEEN @From_date AND @To_date
                GROUP BY [Date]

                UNION ALL

                 -- Bond redemptions (not included in the transactions)
                SELECT [Date], -sum(Amount), 'False'
                FROM Redemptions
                WHERE Portfolio = @Portfolio
                AND [Date] BETWEEN @From_date AND @To_date
                GROUP BY [Date]
                               
                UNION ALL

                --market values
                SELECT [Date], -sum(MV), 'True'
                FROM Market_values
                WHERE port.Portfolio_group_name = @Portfolio_group
                AND [Date] BETWEEN dateadd(day, -1, @From_date) AND @To_date --the first market value needs to be 1 day before the period start
                GROUP BY [Date]

                               
            ) n(d, c, mv)

)
END


MWRR:

ALTER FUNCTION [MWRR]
( @Portfolio AS char(100),
  @From_date AS date,
  @To_date AS date
)
RETURNS float
AS
BEGIN

RETURN
(

SELECT POWER(1+wct.XIRR(c,d,NULL), wct.YEARFRAC(@From_date, @To_date ,1))-1 as MWRR
    FROM (
                --transactions
                SELECT [Date], -sum(Amount)
                FROM Transactions
                WHERE Portfolio = @Portfolio
                AND [Date] BETWEEN @From_date AND @To_date
                GROUP BY [Date]

                UNION ALL

                 -- Income (dividends, interest...)- they are effectively outflows from the portfolio (fees and taxes are treated as inflows)
                SELECT [Date], sum(Amount)
                FROM Income
                WHERE Portfolio = @Portfolio
                AND [Date] BETWEEN @From_date AND @To_date
                GROUP BY [Date]

                UNION ALL

                 -- Bond redemptions (not included in the transactions). Note that the amount values have positive signs
                SELECT [Date], sum(Amount)
                FROM Redemptions
                WHERE Portfolio = @Portfolio
                AND [Date] BETWEEN @From_date AND @To_date
                GROUP BY [Date]
                               
                UNION ALL

                --opening market value for the period
                SELECT [Date], -sum(MV)
                FROM Market_values
                WHERE port.Portfolio_group_name = @Portfolio_group
                AND [Date] = dateadd(day, -1, @From_date) --the opening market value needs to be 1 day before the period start
                GROUP BY [Date]

                UNION ALL

                --closing market value for the period
                SELECT [Date], sum(MV)
                FROM Market_values
                WHERE port.Portfolio_group_name = @Portfolio_group
                AND [Date] = @To_date
                GROUP BY [Date]

                               
            ) n(d, c)

)
END


MDietz:

ALTER FUNCTION [MDietz]
( @Portfolio AS char(100),
  @From_date AS date,
  @To_date AS date
)
RETURNS float
AS
BEGIN

RETURN
(

SELECT wct.EMDIETZ(c,d) as Modified_Dietz
    FROM (
                --transactions
                SELECT [Date], sum(Amount)
                FROM Transactions
                WHERE Portfolio = @Portfolio
                AND [Date] BETWEEN @From_date AND @To_date
                GROUP BY [Date]

                UNION ALL

                 -- Income (dividends, interest...)- they are effectively outflows from the portfolio (fees and taxes are treated as inflows)
                SELECT [Date], -sum(Amount)
                FROM Income
                WHERE Portfolio = @Portfolio
                AND [Date] BETWEEN @From_date AND @To_date
                GROUP BY [Date]

                UNION ALL

                 -- Bond redemptions (not included in the transactions)
                SELECT [Date], -sum(Amount)
                FROM Redemptions
                WHERE Portfolio = @Portfolio
                AND [Date] BETWEEN @From_date AND @To_date
                GROUP BY [Date]
                               
                UNION ALL

                --opening market value for the period
                SELECT [Date], sum(MV)
                FROM Market_values
                WHERE port.Portfolio_group_name = @Portfolio_group
                AND [Date] = dateadd(day, -1, @From_date) --the opening market value needs to be 1 day before the period start
                GROUP BY [Date]

                UNION ALL

                --closing market value for the period
                SELECT [Date], -sum(MV)
                FROM Market_values
                WHERE port.Portfolio_group_name = @Portfolio_group
                AND [Date] = @To_date
                GROUP BY [Date]

                               
            ) n(d, c)

)
END


Notes: 
  • For the functions syntax, refer to the functions' documentation (Doc) or here (Example).
  • Note that this implementation calculates performance of segregated portfolios as opposed to individual positions or the aggregate portfolio as a whole.
  • The XIRR function returns an annualized rate of return. That rate is de-annualized using the POWER function.
  • The usage of the final functions is as simple as: 
    • SELECT TWRR('Equities', '2013-01-01', '2013-12-31') 
  • I'm currently trying to figure out the proper handling of net short positions - since the GIPS formula nor the implementation seem to properly account for them. If anyone has an idea, please share.

Wednesday 30 January 2013

CFA certification

The other day I was asked to give any advice or recommendation regarding the CFA (Chartered Financial Analyst) certification. It's been a few years that I did this certification (between 2006 and 2009) but I guess my experience should be still relevant.

Looking back, I definitely think it's a very useful course, both in terms of the learning experience and  the "CV enhancement" - even though I currently refocused my career to the one of a Business analyst. Depending on your background it should give you a solid understanding of finance from different angles (accounting, portfolio management, economics etc.)

The first advice I would give is that if you want to start with this certification, you really need to commit to it. I see a lot of people who only pass the 1st level and then don't carry on. I don't think the first level would do much for your CV or your career since you have so many people with it already. And when I say it is a commitment, I mean it - I would estimate that you need about 5 months of study time per level, assuming you're working full-time and studying in your "spare time". Of course, depending on your background and experience, the necessary length of preparation will vary. I actually did the 1st level in about 2.5 months since I was fresh out of my Uni at that time and there was a lot of overlap.

In terms of study materials, I would recommend purchasing the Schweser study notes, including their 'Secret sauce' book - a 100 pager summary that is very useful as a revision before the exams. I also signed up to the Analyst notes website. It cost cca. $100 per level but it was definitely very useful for the sample exams, their study notes summaries, forums etc. The official CFA books are quite heavy so you spend much more time with them than with the Schweser materials.

I didn't take any in-class courses. Personally I think they are a waste of time and money - but then again it depends on your learning style and on the fact whether or not you can commit and motivate yourself alone without the little 'encouragements' of a live tutor.

So there you go, hope this helps a little in your preparation. Good luck anyhow - you'll need it :) Let me know if you have any specific questions.

Wednesday 19 December 2012

CBIP - Last exam done - IS Core

Last Friday I passed the last of my CBIP exams - the IS Core one.
I got 71%, just above the "Mastery" threshold - great! :)

I was quite dreading this exam; however in the end I found it relatively easy. Right after the exam I thought I would get about 80% of the answers right but nevertheless I was happy with my result.

In terms of preparation material, this exam is a tricky one. It is really large in scope so you can't really prepare in-depth in all of the examination areas.

My strategy was to 1) rely on my experience, 2) make sure I knew the examination topics as per the CBIP exam guide. For the areas I didn't know, I looked up the topics on Wikipedia. I saved a bunch of exam-relevant articles as a .pdf file. You can download it here: Link


I also read one book - Laudon: Management Information Systems. The book wasn't bad but it is more of a course book for college students. The book is very broad in scope and doesn't dig too much into detail. Some chapters on the topics I haven't much touched on before were useful to me but if you have practical experience in most of them, the book will not teach you much. I went through most of it and at the end, I have to admit, I was quite glad it was over.

So there it is. I'm very happy that i did this certification. I honestly learned a lot from it and it should look nice on my CV :).
Good luck to you if you're in the process of preparing for the exams. Let me know if you have any specific questions, I'd be happy to help.

Friday 31 August 2012

Raspberry Pi - RaspBMC - my experience

Hi all,

I wanted to share that I recently received the little gem called Raspberry Pi and had time to play with it a little.

For those of you that don't know it, have a look here: http://www.raspberrypi.org/



It is a small (desk of cards small!) ARM processor based computer, that is capable of running various Linux distros, play full HD movies and much much more. The best thing? It's really cheap. The model B which I got costs $35. Add to it an SD card and I have a full computer (minus the externals) for 50 bucks!

I ordered mine from Farnell - and it came very quick, in less than 2 weeks! (It used to take a lot longer before due to the high demand.)

As I said, the Pi has many uses. However one of the best uses for it - which is actually how I use it - is to turn this little computer into a fully functional media center.

What do you need for a media center setup:

  • Raspberry Pi Model B - obviously
  • An SD card. I would recommend at least 8 GB, class 10 card. I got a 16 GB class 10 Sandisk one and it is working like charm. I tested the system with a 'no name' class 4 SD card but the performance was not really acceptable.
  • A micro USB (phone) charger - best at least 1,000mA (or 1A)
  • A HDMI cable to plug it into your HD TV
  • An Ethernet cable for internet
  • A keyboard - optional
  • A USB disk - must be either externally powered or connected to an externally powered USB hub. The Pi doesn't supply enough juice to power a USB disk alone
  • An IR TV remote
  • A 3.5mm audio jack cable - if you want to connect external speakers (as opposed to the audio from the HDMI - TV).
As for the operating system, I went with the RaspBMC: http://www.raspbmc.com/
It is a complete port of the XBMC system for the Raspberry Pi and it is based on Ubuntu (CrystalBuntu if I recall well). It is still currently in a Beta version but it is very usable (although not without an occasional bug).
To install the operating system on the card, just follow the instruction on the site. It is a very easy process (if you have a card reader on your PC). The whole install takes about 15 minutes.

There's another system you can try - OpenElec. I will try it if I find some time (and another SD card to spare).

Once the system is installed, you can start navigating your new media center with the TV remote. The menu response with a TV remote is a little bit laggy so if it feels sluggish you can just plug in an external USB keyboard into the 2 USB slots on the Pi and it will feel a lot zippier.

Next I would recommend adjusting the screen settings (System menu). By default it is set on Automatic detection (or something like that). It is better to set your TV resolution manually as sometimes the system doesn't set it correctly.

Next you can change the Audio setting, in case you want to use the external sound system. By default the sound is set to HDMI.

So that's basically it. Now you can plug in your external disk and start enjoying those movies (or music, photos...).

There are also plenty of applications to extend the use of your media center. To install them, you can either download them to your PC, connect to the PI via a SSH (default user: pi, default password: raspberry), copy it there (to /home/pi) and then install them via System -> Add-ons -> Install from a zip file. Alternatively you can just stick the SD card to your PC and copy the files directly.

There is a handy tool called Repository installer which helps you install any unofficial repository (the add-ons are in various repos).

One of the best add-ons I found so far must by the Navi-X. It allows you to stream movies and series if you wish so (careful with those copy rights). I use it to stream live news TV - mainly BBC World News, since we don't get that in Switzerland.

Have fun! :)

Friday 27 July 2012

CBIP - BI & Analytics exam - done!

Hi,

yesterday I sat the BI & analytics specialty exam - and passed with 71%. I was really quite pleased with the result since it is still above the 70% mark and I must admit a was struggling a little during the exam.

I was quite surprised by some of the questions - it sometimes felt like they were on topics I haven't seen before (and I've done the test exams as well as went through the topics outline and was sure I covered it all). Also I found the wording on some question not always clear - with e.g. some of the double negative questions being quite tricky, especially after you answer your 100th question. English isn't my 1st language but I usually don't have any problems with tests etc. Anyhow.

All the more I was glad I passed the exam above 70%.

A few tips if you're taking the online version of the exam - as I was since I live in Switzerland and the exams aren't administered here:

1) make sure you test your PC with the test link some time before the exam and make sure everything is working fine (connection, audio, video). For my first exam it was not the case and it was quite a stress to get everything up and running (and I was also a bit late as a result - thanks Cindy for the patience:). I run Ubuntu on my PC and for some reason I wasn't able to connect to the test site. I then launched the Windows virtual machine - I connected but couldn't get my web cam to work. I finally had to borrow a notebook from a neighbour - good times! :) I tested my Ubuntu PC before the 2nd exam and everything was working fine that time.

2) try to be rested before the exam - it is quite demanding to stay 1.5hrs 100% focused in front of the PC screen

3) make sure there aren't some small things that annoy you during the exam - like for example our dog starting snoring during my first exam - I found that pretty funny but yea, doesn't help the concentration :)


Now to the last exam, IS core - see you in a couple of months!

Monday 23 July 2012

CBIP certification

A few months ago I enrolled in the CBIP (Certified Business Intelligence Professional) certification program administered by the TDWI institute. I thought I'd share my experience as it might be useful to someone else doing this certification.

I have a finance background (MSc in Finance/ management and I am a CFA charterholder); however I enjoy various IT subjects and the data analysis in particular so I said why not giving the CBIP a go. I have already read a relatively large number of technical books on the business intelligence subject so I thought that the CBIP certification will give me at least a general guidance and a framework - in addition to being able to learn new things as well as putting nice 4 new letters on my CV :)

So far the course study was very interesting and I can say I have learned a lot. I have sat the Data Warehousing core exam - and passed with 77%. This week (Friday) I'm taking the speciality Business Intelligence & Analytics exam, fingers crossed. I'm already dreading a bit the last mandatory exam- IS Core, since I don't have an IT background it might be a bit of a challenge from what I hear. But hey, a good challenge ahead!

My strategy was and is to read books from the recommended reading list + some Wikipedia articles. Even though the exam guide book is expensive (cca. $100 if I recall well), it is very useful. It guides you through all of the exams with the topics outlines, gives you the list of the recommended readings and it has some sample exams as well. So my first advice would be - buy it.

As I already mentioned, my strategy is mainly reading some of the recommended books.Here is the list of what I have read so far:

For the Data Warehousing exam:
- Kimball & Co: The Data Warehouse Lifecycle Toolkit : VERY good book on data warehousing and very useful for the exam. It is quite a heavy read, it is stated to have cca. 500 pages but on my Kindle DX it looked more like 1500 pages. It took me a while to get through it and I read it from cover to cover but it is definitely worth it.
- Kimball & Co: The Kimball Group reader: this one is actually a formatted collection of articles that the Kimball group published over the years. I didn't read everything from it because some articles are a repetition from the above book - but it was still useful and I learned quite a few new things.
- Larissa Moss & Co: Business Intelligence Roadmap: The Complete Project Lifecycle : I read some parts of this book for the DW exam and some parts for the Business Analytics one. There is some overlap with this book and the Kimball ones. However this book doesn't only cover the Data warehousing side of things but also other aspects of the BI program/ project. Consequently it is not as complete as the 2 above books but it gives you a good overview and summary.

- I also read a few Wiki articles on subjects not covered by these books. Prior to enrolling on the CBIP I read a book by Roland Bauman: Pentaho Solutions: Business Intelligence and Data... I bought this mainly because of my work but found that it had several chapters that were very useful for the exam as well.

For the Business Analytics exam:
- David Loshin: Business Intelligence: The Savvy Manager's Guide: I think this was the most useful book from the ones I read for the Business Analytics exam in terms of a match towards the topics outlines. It was good overall; the only problem I had with it was that I had the impression that the writing style of the author was switching several times between what I would call an easy reading and then quite a sophisticated one. Or maybe I was just tired after work when I read some more technical parts of it :)
- Thomas Davenport: Analytics at Work...: very good book. The author is a business analytics and management consultant and at least for me it was quite refreshing to read a book from a business perspective for a change. This one was the least technical from all books I have read so far but it was a very interesting read. And it was really funny at times as well.
- Larissa Moss & Co: Business Intelligence Roadmap: The Complete Project Lifecycle: as already mentioned before
- Carlo Vercellis: Business Intelligence: Data Mining...: this one was more of a stats and maths book and their application to business analytics. Despite the subject it was still quite readable. I haven't read everything (maybe 50% of the whole book) because some of the topics were more advances than what I needed. The book has 2 main parts - qualitative introduction to BI (data, warehousing...) and then statistical models and their application to BA problems.

So there we go, that's all for now. As I said I'll be sitting the Business Analytics exam this Friday so I will see whether my strategy continues to be successful or not.

You might say that I'm reading an unnecessary amount of materials for these exams. As I explained, I actually enjoy reading material on these subjects and my primary motivation is to learn while doing the exam, not just have the certification - although that will be nice :) I spent roughly 1.5 months preparing for each of the exams.

You might also say that I'm overly positive in my review of the above books. Maybe but the reason might also partially be that I'm only buying books on Amazon (with my Kindle) that already have very good reviews. The only times I found I didn't really like books I bought on these topics was when I bought books that were actually not what I was looking for or were too technical.

Till the next time!


About me


To keep things short and sweet, you can see my professional profile on LinkedIn: http://www.linkedin.com/profile/view?id=6913406&trk=tab_pro

Don't hesitate to get in touch if you have any questions.