Need help with copying data based on dates and values of cells

Need Help with copying value of cell based on conditions.

I would really appreciate some help with the following problem i have.

Attached excel sheet is a weekly stocks price (Date, Open, High, Low and Close).

I need to do the following

a) Find the row the corresponds to Monday after 3rd Friday of the month (Those of you that are familiar with options, know that I am referring to a the session after the expiry of Option for the month).

b) Copy the Open price from that row over the open price of next months row that corresponds to the week that is a week before we found in (a)

c) Find the HIGH VALUE between the PREVIOUS MONTH and Next Month and copy that value over the HIGH PRICE of the next months high.

d) Do the same with Low Value.

e) Delete the rows below the Next months row that we modified to the row of the previous month from which we found the open price.

f) Keep iterating this though the sheet till you reach the end.

Let me give you an example from the attached sheet.

For clarity take I highlighted the row RED to fulfill my first step. These rows corresponds to the session after 3rd first of the month. This would be (a).

What I need to happen now is as follows

b) Copy B7 over B4
c) Find the High price between Row4 ND Row7 which is C6. Now copy that value from C6 over C4.
d) Find the Low price between Row4 and Row7 which is C7, now copy that over B4.
e) Delete Rows 5,6 and 7

The resutling row 4 will look like Highlighted in Green

Continue the process starting with Row8 and Row12....

Go down the sheet till you reach end.

Hi

There seem to be some inconsistencies in your dates. Quite a few of them are Tuesdays, not Mondays. Examples A8, A10, A11... This will have quite some impact on this. Look at A55. It is a Tuesday that would have to be actioned, but wouldn't meet your criteria.

Has this data been created manually? If not, then how do you process for the inconsistencies?

rylo

Hi Rylo,

Thanks for your time in looking at the data. Yes you are right, those inconsistencies are part of the holidays that fall on Monday's and if the market was closed that monday, the week starts on Tuesday, hence you will see it few times each year. I am not sure how to do this, maybe back that row by a day to make it consistent. It would need to be automated as well, as it will be quiet a few rows. I will have 100s of worksheets on which i need to apply this.

Any thoughts/Ideas?

Hi Marvin,
Thanks for your time input, let me explain you what i am trying to do, I am trying to build a options trading system that will look at the Monthly fluctuation of stock from its Open price on the Monday after 3rd friday, the fluctuation would be in both direction (up and down). To do that i need to capture the following

1) The open price for any given options month. For example for January-2012 options month, the open date is Dec-19-2011 (monday after 3rd friday) and Close date is Jan-17-2012 (3rd friday of the month).
2) Capture the HIGH STOCK price between Dec 19th and Jan 17th
3) Capture the Low price between Dec 19th and Jan 17th.

Once these three values are capture i will then need to do a simple calculation to calculate the % up and down

%up = (High-Open)/Open*100
%down = (Low-Open)/Open*100

Hope this helps.

Looking forward to you input.

Thanks

Hi Dilip,

I agree this would be a automation project that will have several operations, to help you understand it better, please read my post to Marvin below. Maybe you can start by showing me how i can copy over the Open price from monday after 3rd friday and then we can go from there.

Thanks a lot for your input.

Thanks

Hi

OK see how this goes.

Please Login or Register  to view this content.

Credit to daddylonglegs for the formula to work out the 3rd Friday in a month from this post.

rylo

Hi

here goes.

Please Login or Register  to view this content.

rylo