Yesterday morning, after the usual morning trading routine, I have literally no work from 9AM to around 11AM. In the past week I have cleaned up my notes from the activity, but seeing that I'm going into the end of my second week, most of my notes are more than elaborate. So I went in to speak with my director, and told him, for the second time, that I had free hours between these hours. He racked his brains for something to preoccupy me.
He came up with the monthly error report that they have had issues with at the beginning of each month. The finances department sends a detailed report of all the profits and gains from the previous month, and asks each department to justify the numbers. My director had decided to find out from where all the numbers came, and had not had the time to do the grunt of the work. He asked me to see if one column added up to the number found in the summary, at the end.
He was mainly concerned with the losses to the company, and after some hundreds of monetary transactions, the numbers matched. Good, we were done. Wrong. The conclusion only solved one of the problems, the larger problem were a couple of "unrealized profit and losses". The numbers seemed entirely random, and were not easily detectable on the detailed report. My instructions were simply "play around with these numbers and see if you can get this number".
The report, for all fund companies' transactions in a month, included columns: buy/sell, quantity (units), price, average price, position, and profit/loss, with rows: opening balance, the dates of transactions, and closing balance. At the end of each fund, was the market price, unrealized p&l, and the realized p&l.
He came up with the monthly error report that they have had issues with at the beginning of each month. The finances department sends a detailed report of all the profits and gains from the previous month, and asks each department to justify the numbers. My director had decided to find out from where all the numbers came, and had not had the time to do the grunt of the work. He asked me to see if one column added up to the number found in the summary, at the end.
He was mainly concerned with the losses to the company, and after some hundreds of monetary transactions, the numbers matched. Good, we were done. Wrong. The conclusion only solved one of the problems, the larger problem were a couple of "unrealized profit and losses". The numbers seemed entirely random, and were not easily detectable on the detailed report. My instructions were simply "play around with these numbers and see if you can get this number".
The report, for all fund companies' transactions in a month, included columns: buy/sell, quantity (units), price, average price, position, and profit/loss, with rows: opening balance, the dates of transactions, and closing balance. At the end of each fund, was the market price, unrealized p&l, and the realized p&l.
The realized P&L was easy to calculate, they were simply the difference between the day's transactions at the previous price before correction multiplied by the day's quantity, and the day's transactions at the corrected price multiplied by the new day's quantity.
The uneralized P&L just didn't make sense. Especially when I looked at the numbers, unrealized didn't jump out at me, as realized had. I played around with some numbers, and came to the sad conclusion that my director had played me a fool... had decided to give me an unsolvable problem (that potentially no one before me, outside of the finances department, or the creator of the macro).
It was at this point that I decided that there had to be a way to calculate the numbers. I had three funds with the same problem, most of the other transactions were not flagged. By simply going over the ones with problems, I couldn't see what was wrong, so I glanced over the hundreds of pages of other transactions, and noted the difference between the two sets. The most notable part was that the ones without issues had the same beginning and end balance, usually equivalent to zero, while the ones with issues arose from having different balances. At this point, it became clear to me that the thing to consider had to seem out of balance compared to all the others, so I started to single out transaction days with no apparent matching opposite. I manipulated the numbers that looked like they could potentially equal the unrealized.
Still the problem didn't get easier to solve. It was only after noticing that the positions also looked to play a role, did the solution became apparent. An unrealized profit and loss had to come from something that isn't quite apparent, first I thought, well the units are different in the "position" so maybe it's the difference between the two multiplied by the price. That didn't work. Maybe it's the difference multiplied by the average price, still nothing. Or it could be the difference between the open balance and the closing balance multiplied by the price/average price. Nope, not it. After some more of this sort of logic, it came to me!
The market price value had remained untouched in the entirety of the previous calculations, surely they had some impact on the unrealized... for what reason? Not sure. Anyhow it turns out, the market price minus the corrected market price multiplied by the difference between quantity and the current position equals to the unrealized p&l to the penny! SOLVED! Not quite.
When I worked out the other errors in the report, my director came up with another issue... the market price. What impact did it play in the overall picture? Where was the finances department getting this number?
So he called up someone in finances, and walked them through the issue, and the result? Nothing extraordinary. What we concluded after the hour and a half meeting was that the report is pulling phantom units from somewhere in the past, and those numbers roll into each month's report, creating the same problem at the end of each month. Transactions on the last couple of days of the month also roll over into the next month, and do not post onto the current month's report, and then, of course, there's the problem of the market price. We still do not know why the number is being used, when the values in the price column are the prices that the client and the broker use.
It was decided that the report should be scraped, but still finances will need a justification, and an explanation for all the losses and profits. What they need to do is find out where the market price is coming from, what the unrealized p&l signifies, and who owns the report/macro that sends out the report each month.
Easy enough, for now.
The uneralized P&L just didn't make sense. Especially when I looked at the numbers, unrealized didn't jump out at me, as realized had. I played around with some numbers, and came to the sad conclusion that my director had played me a fool... had decided to give me an unsolvable problem (that potentially no one before me, outside of the finances department, or the creator of the macro).
It was at this point that I decided that there had to be a way to calculate the numbers. I had three funds with the same problem, most of the other transactions were not flagged. By simply going over the ones with problems, I couldn't see what was wrong, so I glanced over the hundreds of pages of other transactions, and noted the difference between the two sets. The most notable part was that the ones without issues had the same beginning and end balance, usually equivalent to zero, while the ones with issues arose from having different balances. At this point, it became clear to me that the thing to consider had to seem out of balance compared to all the others, so I started to single out transaction days with no apparent matching opposite. I manipulated the numbers that looked like they could potentially equal the unrealized.
Still the problem didn't get easier to solve. It was only after noticing that the positions also looked to play a role, did the solution became apparent. An unrealized profit and loss had to come from something that isn't quite apparent, first I thought, well the units are different in the "position" so maybe it's the difference between the two multiplied by the price. That didn't work. Maybe it's the difference multiplied by the average price, still nothing. Or it could be the difference between the open balance and the closing balance multiplied by the price/average price. Nope, not it. After some more of this sort of logic, it came to me!
The market price value had remained untouched in the entirety of the previous calculations, surely they had some impact on the unrealized... for what reason? Not sure. Anyhow it turns out, the market price minus the corrected market price multiplied by the difference between quantity and the current position equals to the unrealized p&l to the penny! SOLVED! Not quite.
When I worked out the other errors in the report, my director came up with another issue... the market price. What impact did it play in the overall picture? Where was the finances department getting this number?
So he called up someone in finances, and walked them through the issue, and the result? Nothing extraordinary. What we concluded after the hour and a half meeting was that the report is pulling phantom units from somewhere in the past, and those numbers roll into each month's report, creating the same problem at the end of each month. Transactions on the last couple of days of the month also roll over into the next month, and do not post onto the current month's report, and then, of course, there's the problem of the market price. We still do not know why the number is being used, when the values in the price column are the prices that the client and the broker use.
It was decided that the report should be scraped, but still finances will need a justification, and an explanation for all the losses and profits. What they need to do is find out where the market price is coming from, what the unrealized p&l signifies, and who owns the report/macro that sends out the report each month.
Easy enough, for now.
No comments:
Post a Comment