Spartan Trader 3 (H15) - last individual homework
Financial metrics
Goal
By completing this homework you will learn to compute financial metrics using data stored in remote RDBMS and visualize them in an Excel dashboard.
Context
“You have done a MARVELOUS job!" Tess Tickertracker, your CFO, seems pleased with your work after watching
your demo showing the robotrader data retrieval capabilities. Yet, she has a way to raise her voice when she says things
like 'amazing!' or 'brilliant!' that make you a little uncomfortable - it sounds like excessive praise. Maybe it is just
the way she talks.
"Next, we need to focus on financial metrics. To manage portfolios, the trader needs metrics... metrics... any
ideas?”
"Well, Ms. Tickertracker, I think that the most basic metric for a portfolio is its value... we could assess it as the
sum of our positions, each evaluated at MTM"
"SPECTACULAR! Let's also have the components of the portfolio disaggregated: the capital account, the value of the
positions we need to hedge, and then the hedge."
“Of course: Capital Account, IPs and APs.”
“Other ideas?"
"We might assess our short positions..."
"AMAZING! The margins are very important! We need to know them because we must maintain at least 30% of our total
liabilities in cash or we might get a margin call, and additionally we have a maximum amount that we can borrow.
BRILLIANT! Any suggestion for assessing the performance of our operations?"
"I gave it some thought: we could compute a target amount. That would be the amount we should have if our portfolio grew
at a steady rate."
"Ah! A function that computes the value of our portfolio if it grew at the risk-free rate... that would be a good
measuring stick for our actual performance. Let's do it!"
"I am on it, Boss." says you while messaging your friends to let them know that you will likely be late for the party
tonight...
Requirements
The vLab contains a demo and shows examples, but not the whole homework. The video is longer than the past ones, so
give yourselves plenty of time. Dataset procedures and Portfolio
management contain some code, so that you do not have to type the whole thing. However, do
not just copy and paste. Figure out what that code does or you will be VERY lost later.
In class we discussed how to check each financial metric. Take the time to do it.
Some tables may be empty or contain different data. That is OK.
- All homework in the 'Spartan Trader' series is cumulative, which means that it needs to do everything that all previous homework did, plus the new functionality.
- Assume that for the time being you are team 30, even if you already have a teamID assigned.
- Compute and visualize: CAccount, Margin, Max. Margin, IP, AP (hedge), TPVatStart (Total Portfolio Value at Start), and TaTPV (Target Total Portfolio Value). They were reviewed in class.
- Make sure that all the values on the dashboard are professionally visualized. 75249221.7224 is hard to read: $ 75,249,221 is much easier.
Feel free to change the look and feel of your app, provided that the interface is reasonably polished, easily understandable by the grader, and retrieves all the needed financial information. You are the developer and the user. You build it the way you like it. You will use it in the Hedge Tournament.
Bug fixes:
[1] Sometimes the LOs in the dashboard do not fully recolor, for example when you change DB and in other conditions.
Easy fix: just insert at the end of ClearAllLO() these two lines
Globals.Dashboard.InitialPositionsLO.ListRows(1).Range.Interior.Color = System.Drawing.Color.Black
Globals.Dashboard.AcquiredPositionsLO.ListRows(1).Range.Interior.Color = System.Drawing.Color.Black
If your dashboard background is not black, substitute with the right color.
[2] Check the bug fix [2] in H13 and apply it IF the dates in the Stock, Option, and SP500 list objects are not formatted uniformly.
[3] TPVatStart = CalcTPVAtStart() should NOT be inside CalcFinancialMetrics. It should be placed as the last line in SetFinancialConstants().