Need help portfolio simulation

I don't speak english very well so sorry for my all mistake

Don’t worry your English is much better than my Polish so don’t be sorry for that.

Recording a macro is one of the best ways to earn to write macros but Excel records everything so usually a recorded macro can be “trimmed” quite a bit.

I think your macro 8 could be written like this and still work. You better test this of course.

Sub macro8()

 Sheets("dzienne st zwrotu").Activate

Range("A73").FormulaR1C1 = "=AVERAGE(R[-66]C:R[-47]C)"
Range("C73").FormulaR1C1 = "=AVERAGE(R[-66]C:R[-47]C)"
Range("C73").AutoFill Destination:=Range("C73:AG73"), Type:=xlFillDefault

Sheets("VAR").Activate
Range("C7").FormulaR1C1 = "=INT(R[4]C/INDIRECT(R4C&""!G4""))"
Range("C7").AutoFill Destination:=Range("C7:AG7"), Type:=xlFillDefault

Range("H24").FormulaR1C1 = "=INT(R[1]C/XLU!R[-20]C[-1])"
Range("C8").FormulaR1C1 = "=INDIRECT(R4C&""!B3"")"
Range("C8").AutoFill Destination:=Range("C8:AG8"), Type:=xlFillDefault

Range("C9").FormulaR1C1 = "=INDIRECT(R4C&""!E3"")"
Range("C9").AutoFill Destination:=Range("C9:AG9"), Type:=xlFillDefault
Range("H26").FormulaR1C1 = "=XLU!R[-23]C[-6]"
Range("H27").FormulaR1C1 = "=XLU!R[-24]C[-3]"

SolverOk SetCell:="$C$19", MaxMinVal:=1, ValueOf:="0", ByChange:="$C$11:$AG$11"
SolverSolve

Range("J30").Copy
Range("C39").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

End Sub

You also wish to run solver in a loop. That should not be too difficult. What cells do you wish solver to loop through?

Alf

Not sure I understands your problem totaly. Running solver in a loop as I said is no problem:

Sub TestLoop()

Dim cell As Range

For Each cell In Range("B38:B" & Range("B" & Rows.Count).End(xlUp).Row)

SolverOk SetCell:="$C$19", MaxMinVal:=1, ValueOf:="0", ByChange:="$C$11:$AG$11"
 SolverSolve UserFinish:=True
 
Range("J30").Copy
 cell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues
 
Next

Application.CutCopyMode = False

End Sub

This macro will run solver in a loop using range B38 to B59 as the counter and placing the solver result in a cell with an offset of (0, 1). That is loop starts at B38 and the solver result will be placed in a cell on the same row (i.e. row 38) but in a column one position to the right of B38 i.e. C38. The next loop will then use B39 as a counter and result will be placed in C39 and so on.

What I do not understand is the rules for how you change input data to solver. In your example you select May 22 and then you take average from May 21 to April 24??

So what data should solver be given for each change of date in the range B38 to B59?

Alf

So maybe another way:

When i run macro with loop, loop will be take every date from:


Jun 7, 2013
Jun 6, 2013
Jun 5, 2013
Jun 4, 2013
Jun 3, 2013
May 31, 2013
May 30, 2013
May 29, 2013
May 28, 2013
May 24, 2013
May 23, 2013
May 22, 2013
May 21, 2013
May 20, 2013
May 17, 2013
May 16, 2013
May 15, 2013
May 14, 2013
May 13, 2013
May 10, 2013
May 9, 2013
May 8, 2013

when meet empty cells loop will stop. And now the most important thing. For example loop take cell B44 ''30 MAY'' so i want to:

1) In sheet "dzienne st zwrotu" macro will calculate 20 average for day -1 from cell B44 ''30 MAY'' (look above) so average for 20 last days -1 = prices from May 29, 2013 to May 1, 2013 < for all stokcs xlu and rest of all. All results will calculate in cells C75 ( i think it is not necessary to explain that) you see formula.
2) excel in sheet VAR take calculate formula see cells C13 C14
3) next step is a to calculate number of shares cells C7 etc for all stocks. Excel take C11 / Adj Close*(again day -1, so 29MAY) from (in this example) DUK sheet same for rest stock and XLU in cell H24.
4) Now open and close price cells C8 and C9, excel take from stocks sheet for example DUK. Take open and close price for current day, so in this example it will be 30 MAY
5) Now solver: when i click "licz" solver dividend cpital from C1 for each stock, Cells C11 etc are the changing cells by solver to max cell C19 (=SUM(C15:AG15).
6) and i see the result in cell J30

I hope u understand what i mean.

The line

For Each cell In Range("B38:B" & Range("B" & Rows.Count).End(xlUp).Row)

will loop through all values in coumn B so if you extend your formula

='dzienne st zwrotu'!B5

down to B102 on the "var" sheet you will get all the date values found in sheet "dzienne st zwrotu" so you have all the dates and can calculate the average for the last 20 days based on what particular date you got in the B column.

At the moment the loop runs from B38 i.e. "Jun 7, 2013" to "May 8, 2013" (B59) and you can certainly use a command like "cell.Value" to "capture" the date and fiddle around with that to get average and the other values you need for the solver setup.

Alf

Hi Alf

I modified excel file and now i think it will be much easier to make macro loop.
So i would like to macro look like:
1) Macro will change date in cell G36
2) Macro will start solver
3) Macro copy the result from J30 to table in column C

and loop, macro again change date (next date) in list and loop stop when the list is over and have no more date. Is it possible to do?

Thanks for help

Try this modefied macro:

Sub macro8()

Dim cell As Range
Dim i As Integer

 Sheets("dzienne st zwrotu").Activate

Range("A73").FormulaR1C1 = "=AVERAGE(R[-66]C:R[-47]C)"
Range("C73").FormulaR1C1 = "=AVERAGE(R[-66]C:R[-47]C)"
Range("C73").AutoFill Destination:=Range("C73:AG73"), Type:=xlFillDefault

Sheets("VAR").Activate
Range("C7").FormulaR1C1 = "=INT(R[4]C/INDIRECT(R4C&""!G4""))"
Range("C7").AutoFill Destination:=Range("C7:AG7"), Type:=xlFillDefault

Range("H24").FormulaR1C1 = "=INT(R[1]C/XLU!R[-20]C[-1])"
Range("C8").FormulaR1C1 = "=INDIRECT(R4C&""!B3"")"
Range("C8").AutoFill Destination:=Range("C8:AG8"), Type:=xlFillDefault

Range("C9").FormulaR1C1 = "=INDIRECT(R4C&""!E3"")"
Range("C9").AutoFill Destination:=Range("C9:AG9"), Type:=xlFillDefault
Range("H26").FormulaR1C1 = "=XLU!R[-23]C[-6]"
Range("H27").FormulaR1C1 = "=XLU!R[-24]C[-3]"

i = Range("B" & Rows.Count).End(xlUp).Row

For Each cell In Range("B38:B" & i)

cell.Copy
Range("G36").PasteSpecial Paste:=xlPasteValues

If i - cell.Row >= 20 Then
    Range("G37") = 20
Else
    Range("G37") = i - cell.Row
End If

SolverOk SetCell:="$C$19", MaxMinVal:=1, ValueOf:="0", ByChange:="$C$11:$AG$11"
SolverSolve UserFinish:=True

Range("J30").Copy
 cell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues
 
Next

Application.CutCopyMode = False

End Sub

As your date format i.e. Jun 12, 2013 is rather strange I have used copy and PasteSpecial to get it transferred in the right format to G36. The value in G37 is set to 20 if row difference (last row - actual row) is equal to or greater than 20. If less the actual difference is used.

I really would like to set date difference in days but I can't use your date format in calculating number of days.

Alf

Glad your problem is solved and thanks for feedback and rep!

Alf

Ps Since you problem is solved could you please mark your thread "Solved"

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save