Portfolio Generator

So I’m working on this fairly simple model where I’ve calculated the monthly attribution for each stock on an index. I am using MS Excel and its basically on three sheets (not including the source and working sheets) one carries the weight for the scrips the other has the returns and the third is the attribution i.e. weight*return.

Now, what I want to do is that I want to calculate the weight for each day, assuming that the returns remain the same, at which my portfolio would have generated the highest return.

Could somebody please guide me as to how I should go about doing it.

Thanks!

Note: I plan on adding other criteria to this, so it can’t really be highlight the maximum return for each day and invest everything in that although that would be a start. I plan on adding other factors e.g. amount of diversification, scrip holding limits etc.

I think I would start by deciding on the factors you’d like to include. A simple way would be to have a table where you could play around with the factors (ex: how many stocks you’d like to include and at which weight: 5 at 5%, 25 at 2%, 10 at 1%, x at 0%…)

Then to get the weight of each stock you could use one of the rank functions to rank the daily returns and use a nested if function linked to your factors to say if rank is between x and y use z%, etc.

I suspect you are not going to be able to do this without VBA, and most likely you are going to have to loop through your data and run solver (and possibly intermediate calculations that will go into your conditions) for every day of data that you have. That can be a slow process by the time you’re done, but it is doable.

This is fine as an exercise, but rebalancing every day like that is likely to kick up transaction costs to intolerable levels in real life.

FTFY

Ah, sounds like you’re being more precise [sic]. :wink:

^Sooo accurate.

offer 2000 AF points as a reward, one of the guys will sure take it on

hahaha ofcourse I won’t be rebalancing my model on a daily basis.

I’m actually doing this exercise to back test some other models that I have. thank you everyone and if someone with VBA knowledge could guide me towards a good place where I could learn VBA I would be very thankful.

Agree with bchad; you will need a little VBA and the Excel Solver to do this, unless you want to manually run the Solver add-in for each of the days in your data history. Specifically, automating the Solver inside a loop, where you are looping through each individual day row by row and performing an optimization at each row. For each optimization, you will have a dedicated range for weights (this can be the same cells/range for each optimization), which will be designated for the “by changing variable cells” range selection window within the Solver dialog box. Constraints will be such that this weight range must sum to 1.0 (i.e., 100% – I assume that your portfolio is long only with no shorting). You will indicate this in the “subject to the constraints” section of the dialog box in Solver.

At the top of the dialog box, you want to “Set Objective” as the thing you want to optimize. In this instance, it will be maximizing the return of your portfolio. So you’ll want to keep a daily tabulation of the SUMPRODUCT of each asset’s weighting times the return/factor of each asset. Whichever cell this total is kept in for each day, this will be the objective you want to maximize.

If you don’t know either the Solver or VBA, my highest recommendation is that you invest a little time in understanding how to do this. There are numerous online forums and informational sources which can walk you through. If you can automate what you’re talking about in this way, you will find that this approach can be very powerful and will enable you to solve numerous problems in investments very efficiently.

One thing I would say is that based on my understanding of what you want to do; unfortunately, you will almost certainly end up with all of the weight being placed on the highest-returning asset for any given day. This is the nature of optimization. With no other constraints being imposed, if I have 3 assets, each returning -1%, 0% and 2% on the day, and I am telling an optimizer to maximize the return for the day, there’s no magical mathematics which would cause any other outcome than loading the third asset to 100% weight. It’s only when you add other constraints or objective types where you get nuanced weightings at each run. This approach we are talking about here really works better when you want to see the optimal weights over multiple observations with some other objective (say, optimizing for risk-adjusted return over a period of X days/weeks/months, etc.). But as you said, you may be adding some criteria later which I don’t know about, so maybe this single-observation approach will work as a starting point for what you want to do. Hope this helps.

EDIT: see http://peltiertech.com/Excel/SolverVBA.html for a good starting point on automating the Solver in Excel. I also recommend just finding forums with specific words in your search about what you want to do; a bunch of different forum conversations will surface. This is how everybody I know learned their VBA.

exactly

I also had this point of view, but the OP does say he’s planning to add other constraints later on. So the first version is just going to load up on the highest recently performing asset, but if you start to add other constraints (like max weights etc), then you will get other kinds of results.

You say you are optimizing for max return, however, it seems like you really ought to be trying to maximize a risk adjusted return, otherwise you are likely to be loading up on a lot more risk than you think. Plus, performance chasing is a boring strategy which doesn’t tend to work well, because you are tipically not exposed when the largest gains happen and are exposed for the largest losses.

power programmin with VBA is a really good book for learning VBA

Thank you so much for the guidance! Honestly this is a lot of help. I had already started with the solver but the loop is something I’m going to start working on soon. I’ll defintely read through the tutorial link you sent. Thanks again man!

When I referenced constraints, risk was going to be one of them. I’m actually working as a risk manager in a local AMC so yeah lol. Thats pretty much the first constraint I’ll be considering.

Secondly, would love to hear any other strategies you feel I should apply when building an optimization model.

I’ve also developed a micro attribution model (pure sec, within sec, allocation/interaction) but for some reason my total value added (pure sec + within sec + allocation/interaction) does not sum to the alpha generated. I’m using x-1 day end weights and x day returns. (x being the day I’m using to backtest my model).

I’m using the Boehler model I believe which is the one covered in level 3.

Has anyone ever faced a similar problem with this?