Spartan Trader 3

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

source:memegenerator.net

“You have done a MARVELOUS job!"  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 'faaantastic!' 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... 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"
"Faaaaantastic! Let's also have its components disaggregated: the capital account, the positions we need to hedge, and then the hedge."
“Of course.”
“Other ideas?"
"We might assess our short positions..."
"Yes! The margins are very important! We need to know the because we must maintain at least 30% of that 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. 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.

  • All homework in the 'Spartan Trader' series is cumulative, which means that it needs to do everything that the 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, and TaTPV. They are defined in the rulebook and were reviewed in class.
  • Make sure that all the values on the dashboard are professionally visualized.  75249221.7224 is hard to read, and $ 75,249,221 is much easier.
  • Retrieve and visualize the current system date. This is the current HT date, not the real time date.

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)  Cut the line TPVatStart = CalcTPVAtStart()  from CalcFinancialMetrics(targetDate As Date) and paste it as the last line in SetFinancialConstants()
2)  Move the line lastPriceDownloadDate = "1/1/1" in MainProgram() to just under ClearAllLO()

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

4) Sometimes the LO does not format the dates in the first row correctly.  Just force the cell that misbehaves to assume the right format by adding at the end of the button code the following:
Globals.Markets.Range("B3").NumberFormat = "m/d/yyyy"
where B3 is the address of the misbehaving cell.