0

I'm currently trying to set up a PSQL database for a portfolio backtest and was wondering if anyone had a more automated solution for future changes.

Say I have a portfolio with stocks A, B, and C. I change the weights on the portfolio each month. Let's say for January 2015 my weights are A-25%, B-25%, C-40%. To calculate the number of shares I need for a $1000 portfolio I just do weight of A * 1000/share price of A, and so on for B and C. Each day, I check the prices of A, B, and C and multiply by the number of shares to get the value of total portfolio.

In February, if I change the value of the portfolio to A-25%, B-35%, C-40%, then I need to rebalance by taking the value of the portfolio on last day of January and multiply by the February weights, and dividing by the share prices of the stock.

The issue is that I would like to test what would happen if I added in a rebalance in the middle of a month. I don't want to just store the total value of the portfolio, since it could change if I add in a rebalance.

I currently have the percentage of the portfolio for each month inputted into the database. What would be the easiest way to calculate the daily value of the portfolio, while adjusting for extra rebalances?

Is it better to insert rows for each day in the month equal to the number of shares from the last rebalance? For example, should I only have the number of shares for each rebalance, so Jan 2015 - # of shares of each, Feb 2015 - # of shares of each. Or should I have Jan 1,2015 - # of shares of each, Jan 2, 2015 - same as before, Jan 3, 2015- same as before ... Feb 2015 - new # of shares of each. If doing it in the first example, how would I set the conditions in SQL so that I use the last rebalance, but stop at the next rebalance? Would I just need to get a list of dates for the rebalancing and then loop through two at a time with one as the beginning and one as the ending date?

Any help would be greatly appreciated

user3628240
  • 877
  • 1
  • 23
  • 41
  • What is your exact question? Are you asking about **what** is the best scheduling for rebalance? Are you asking about **how** to calculate the new allocation of each stock? –  Jun 19 '17 at 21:20

1 Answers1

1

There are backtrading tools out there such as Quantconnect/Lean or Quantopian /Zipline even using advanced backtesting tools, it is really hard to trust and get reliable testing doing it on a DB without modelling market impact/ slippage / commission is probably wasting time.

Rebalancing on my experience can have a big effect on the algorithm. Specially if you model slippage and commission.