Hi everyone,
I don't speak english very well so sorry for my all mistake :).
So i recorded macro for Jun 6, 2013:
And i want to fill all data in "var" sheet automatic from Jun 5, 2013
Please Login or Register to view this content.
to May 8, 2013. Can anyone help me?
It will be first simulation. And next i want to change something for example cell C16 and C17 and again make simulation for date from Jun 5, 2013 to May 8, 2013.
Last edited by pulkownik; 06-15-2013 at 08:23 AM.
Don’t worry your English is much better than my Polish so don’t be sorry for that.I don't speak english very well so sorry for my all mistake
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.
You also wish to run solver in a loop. That should not be too difficult. What cells do you wish solver to loop through?Please Login or Register to view this content.
Alf
Great macro works
So i am not sure that i understand your question in 100%. Yes i want use solver for every single day. Maybe i give example how macro should look like.
In cells B38:B59 i have date and i want simulation for every single day. For example excel take cell B49 (May 22, 2013) and make simulation:
1) AVERAGE for XLU from May21 to Apr24 and same for single stock.
2) C7:AG7 take Adj Close* from sheets for date May21 and make formula
3) C8:AG8 take open price from sheets for May 22 << same for cell H26
4) C9:AG9 take close price from sheets for May 22 << same for cell H27
5) next step is using solver and copy result from J30 to B49 (May 22, 2013)
So i think the best way to do that is to use loop, and excel will stop using loop when meet empty cell in my example B60, but i dont know how to make it.
Edit:
I added something to your code
Do While ActiveCell.Value <> ""
(...)
ActiveCell.Offset(1, 0).Range("B3").Select
Loop
But it does not help, it works only one time and stoped.
Last edited by pulkownik; 06-09-2013 at 03:24 PM.
Not sure I understands your problem totaly. Running solver in a loop as I said is no problem:
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.Please Login or Register to view this content.
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
will loop through all values in coumn B so if you extend your formulaPlease Login or Register to view this content.
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.Please Login or Register to view this content.
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
So could you show me all macro? how it looks like? beacuse i have no idea. How to do it with your tips.
Edit:
I use your code with solver loop, everything is ok but all values are the same for all data. Loop should take data from cells B38 and make seperate calculation for every date.
Last edited by pulkownik; 06-10-2013 at 11:14 AM.
Yes of course as there is no change of the solver data. As I said beforePlease Login or Register to view this content.and as the solver input stays the same the solver result will not change.what data should solver be given for each change of date in the range B38 to B59?
This main reason for this macro was to show you one way of how you can make solver run in a loop.
As I do not understand the "mechanism" behind this problem I'm not capable of writing a macro that does what you wish.So could you show me all macro? how it looks like?
Why don't you have a go at it and when you know what you wish to do but can't get the macro to work the way you wish post again in this forum.
Alf
Ps This forum now opens up a paid service where the real forum experts will solve all kinds of problems. Perhaps you should check if this service could be of use to you?
Last edited by Alf; 06-10-2013 at 01:25 PM.
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:
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.Please Login or Register to view this content.
I really would like to set date difference in days but I can't use your date format in calculating number of days.
Alf
Sory but i don't know why your macro ruin my formula for example in C7 C8 C9, and why you use cell G37
Macro can not use cell G37, Cell G37 is only change average. If Cell G37=20 then average for each stock is for 20 last day so that cell can not be changed. Maybe this way:Please Login or Register to view this content.
1) Macro copy cell B39: to the end,
2) next paste to G37
3) Macro run solver
4) result from J31 copy to the cell B39
5) loop (cell B40, B41 etc) loop stop when meet empty cell.
Something like that but with loop. New file beacuse i added new row.Please Login or Register to view this content.
I modifed your codeWORKS!Please Login or Register to view this content.BIG THANKS ALF. I am very very happy and appreciate your help and support big thanks again
Last edited by pulkownik; 06-14-2013 at 04:33 PM.
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks