I have an access database with records of securities in my portfolio as well as my trades for each security.
My aim is to calculate the "Daily Time Weighted Return" then down the line, export it and with a second program, geometrically chain-link for a desired period.
In VBA I replicated this in the following way:
Day_Return = (Day_PNL + Day_Dividends) / (Previous_Days_Price * Previous_Days_Holding_Qty)
What I've noticed is that should I trade a stock during the day, the performance calculation will be incorrect even though the actual P&L is fine.
Could someone help me understand where i'm going wrong, or how I can change my calculation/logic so that performance reflects not only the static performance from one day to the next but also the performance on buys/sells?
Any help will be greatly appreciated, including tips on reengineering this question or clarifying anything. Thank you.