Hi,
Please see attached files at the following links for more detailed info and a sample sheet.
sample sheet
word document
I would like the following things to be done using a macro in real-time. (It is impossible to do with just regular excel functions) So if the workbook is open I would like the macro to be constantly running.
Please go to Sheet3 in the workbook…
I would like the macro to populate the values in the highlighted region in (blue and green)
I have an external data feed that populates column A, B, C, D and depending on the day the dates in column B and the values in column C and D may go down different numbers of rows.
I would like to replicate Sheet3 on many other sheets which will have dates in column B and the values in column C and D that go down different numbers of rows.
So the macro will need to determine how many rows have values in them starting in row 5 and going down to row X
Then it needs to calculate the values for columns E to P for the appropriate rows using the formulas and directions below
Not all sheets will have same number if rows with info, so excel needs to check column B, C, and D to check the last row with a value
However, the format of other sheets will be the same (i.e. the first date and values will start on row 5)
I have included the formulas in the highlighted cells, obviously since this would be coded in a macro the finished sheet will not have formulas embedded in the sheet rather coded in a macro
Here’s what I would like the macro to do…
If cell B5 date is a weekend or US bank holiday, populate cell E5 with next business date, if cell B5 is not a weekend or US bank holiday populate the same date in cell E5 as in cell B5
Do this for all dates in column E starting at cell E5 and going down
Check the date in cell E5 with the dates in column J of Sheet1
Determine the two dates (from column J of Sheet1) that the date in cell E5 of Sheet3 falls in between
Populate cell I5 with the date (from column J of Sheet1) that falls right before the date in cell E5 of Sheet3
Do this for all of column I; using dates in column E starting at cell E5 and going down
Populate cell J5 on Sheet3 with the date (from column J of Sheet1) that falls right after the date in cell E5 of Sheet3
Do this for all of column J; using dates in column E starting at cell E5 and going down
Populate cell K5 on Sheet3 with the value in column L from Sheet1 that corresponds to the date in cell I5
Do this for all of column K; using dates in column I starting at cell I5 and going down
Populate cell L5 on Sheet3 with the value in column L from Sheet1 that corresponds to the date in cell J5
Do this for all of column L; using dates in column J starting at cell J5 and going down
Column M is cell E#-I#
Column N is cell J#-E#
Column O is cell J#-I#
Cell F5 should show the result of the following formula
=(K5^(N5/O5))*(L5^(M5/O5))
Do this for all of column F; using values from appropriate rows
Cell G5 is the result of the following formula
=(C5/10000)+(D5/10000)
Do this for all of column G; using values from appropriate rows
Cell H5 is the result of =G5/F5
Do this for all of column H; using values from appropriate rows
Cell P5 is the result of =((F5/K5)-1)/(M5/360)
Do this for all of column P; using values from appropriate rows
Cell S8 has a formula which is is long and it depends on the number of rows that have information in Sheet3
Formula is
=((G5)/((1+((Sheet1!I2-$R$8)*(Sheet1!K2/360)))*(1+((Sheet1!I3-$R$8)*(Sheet1!K3/360)))*(1+((P5-$R$8)*(M5/360)))))+((G6)/((1+((Sheet1!I2-$R$8)*(Sheet1!K2/360)))*(1+((Sheet1!I3-$R$8)*(Sheet1!K3/360)))*(1+((Sheet1!I4-$R$8)*(Sheet1!K4/360)))*(1+((Sheet1!I5-$R$8)*(Sheet1!K5/360)))*(1+((P6-$R$8)*(M6/360)))))+((G7)/((1+((Sheet1!I2-$R$8)*(Sheet1!K2/360)))*(1+((Sheet1!I3-$R$8)*(Sheet1!K3/360)))*(1+((Sheet1!I4-$R$8)*(Sheet1!K4/360)))*(1+((Sheet1!I5-$R$8)*(Sheet1!K5/360)))*(1+((Sheet1!I6-$R$8)*(Sheet1!K6/360)))*(1+((Sheet1!I7-$R$8)*(Sheet1!K7/360)))*(1+((P7-$R$8)*(M7/360)))))+((G8)/((1+((Sheet1!I2-$R$8)*(Sheet1!K2/360)))*(1+((Sheet1!I3-$R$8)*(Sheet1!K3/360)))*(1+((Sheet1!I4-$R$8)*(Sheet1!K4/360)))*(1+((Sheet1!I5-$R$8)*(Sheet1!K5/360)))*(1+((Sheet1!I6-$R$8)*(Sheet1!K6/360)))*(1+((Sheet1!I7-$R$8)*(Sheet1!K7/360)))*(1+((Sheet1!I8-$R$8)*(Sheet1!K8/360)))*(1+((Sheet1!I9-$R$8)*(Sheet1!K9/360)))*(1+((P8-$R$8)*(M8/360)))))
For clarity, each color represents a separate term of the entire expression. There are four terms in this case because we had data in four rows (5-9), in other cases I will have more or less
So if date in cell E5 is 8/31/2011 use values from column I of Sheet1 for dates in column J that fall before the date in cell E5 without going past the date in cell E5
This is represented by =((G5)/((1+((Sheet1!I2-$R$8)*(Sheet1!K2/360)))*(1+((Sheet1!I3-$R$8)*(Sheet1!K3/360)))* portion of the formula
The final part of the formula *(1+((P5-$R$8)*(M5/360)))))
Cell S9 is the difference between the given value in cell R7 and the value arrived at from the above calculation
Cell R8 is being populated by using excel solver to solve for the value of R8 given that the value in cell S9 needs to equal zero
The macro including the solver macro would have to be run every time there is any value in the workbook that changes or basically it would need to be constantly running in real-time
I would need to do this for multiple sheets similar to Sheet3, the difference being that each sheet will have a different number of dates and values in column B, C, and D.
Column B, C, and D will always start at row 5, what I don’t know (it is dynamic) is the number of cells down that dates and values in column B, C, and D will be populated
If you could please include information on how to replicate this code for other sheets just like Sheet3, that would be great.
Thanks a lot,
A
Please see attached files at the following links for more detailed info and a sample sheet.
sample sheet
word document
I would like the following things to be done using a macro in real-time. (It is impossible to do with just regular excel functions) So if the workbook is open I would like the macro to be constantly running.
Please go to Sheet3 in the workbook…
I would like the macro to populate the values in the highlighted region in (blue and green)
I have an external data feed that populates column A, B, C, D and depending on the day the dates in column B and the values in column C and D may go down different numbers of rows.
I would like to replicate Sheet3 on many other sheets which will have dates in column B and the values in column C and D that go down different numbers of rows.
So the macro will need to determine how many rows have values in them starting in row 5 and going down to row X
Then it needs to calculate the values for columns E to P for the appropriate rows using the formulas and directions below
Not all sheets will have same number if rows with info, so excel needs to check column B, C, and D to check the last row with a value
However, the format of other sheets will be the same (i.e. the first date and values will start on row 5)
I have included the formulas in the highlighted cells, obviously since this would be coded in a macro the finished sheet will not have formulas embedded in the sheet rather coded in a macro
Here’s what I would like the macro to do…
If cell B5 date is a weekend or US bank holiday, populate cell E5 with next business date, if cell B5 is not a weekend or US bank holiday populate the same date in cell E5 as in cell B5
Do this for all dates in column E starting at cell E5 and going down
Check the date in cell E5 with the dates in column J of Sheet1
Determine the two dates (from column J of Sheet1) that the date in cell E5 of Sheet3 falls in between
Populate cell I5 with the date (from column J of Sheet1) that falls right before the date in cell E5 of Sheet3
Do this for all of column I; using dates in column E starting at cell E5 and going down
Populate cell J5 on Sheet3 with the date (from column J of Sheet1) that falls right after the date in cell E5 of Sheet3
Do this for all of column J; using dates in column E starting at cell E5 and going down
Populate cell K5 on Sheet3 with the value in column L from Sheet1 that corresponds to the date in cell I5
Do this for all of column K; using dates in column I starting at cell I5 and going down
Populate cell L5 on Sheet3 with the value in column L from Sheet1 that corresponds to the date in cell J5
Do this for all of column L; using dates in column J starting at cell J5 and going down
Column M is cell E#-I#
Column N is cell J#-E#
Column O is cell J#-I#
Cell F5 should show the result of the following formula
=(K5^(N5/O5))*(L5^(M5/O5))
Do this for all of column F; using values from appropriate rows
Cell G5 is the result of the following formula
=(C5/10000)+(D5/10000)
Do this for all of column G; using values from appropriate rows
Cell H5 is the result of =G5/F5
Do this for all of column H; using values from appropriate rows
Cell P5 is the result of =((F5/K5)-1)/(M5/360)
Do this for all of column P; using values from appropriate rows
Cell S8 has a formula which is is long and it depends on the number of rows that have information in Sheet3
Formula is
=((G5)/((1+((Sheet1!I2-$R$8)*(Sheet1!K2/360)))*(1+((Sheet1!I3-$R$8)*(Sheet1!K3/360)))*(1+((P5-$R$8)*(M5/360)))))+((G6)/((1+((Sheet1!I2-$R$8)*(Sheet1!K2/360)))*(1+((Sheet1!I3-$R$8)*(Sheet1!K3/360)))*(1+((Sheet1!I4-$R$8)*(Sheet1!K4/360)))*(1+((Sheet1!I5-$R$8)*(Sheet1!K5/360)))*(1+((P6-$R$8)*(M6/360)))))+((G7)/((1+((Sheet1!I2-$R$8)*(Sheet1!K2/360)))*(1+((Sheet1!I3-$R$8)*(Sheet1!K3/360)))*(1+((Sheet1!I4-$R$8)*(Sheet1!K4/360)))*(1+((Sheet1!I5-$R$8)*(Sheet1!K5/360)))*(1+((Sheet1!I6-$R$8)*(Sheet1!K6/360)))*(1+((Sheet1!I7-$R$8)*(Sheet1!K7/360)))*(1+((P7-$R$8)*(M7/360)))))+((G8)/((1+((Sheet1!I2-$R$8)*(Sheet1!K2/360)))*(1+((Sheet1!I3-$R$8)*(Sheet1!K3/360)))*(1+((Sheet1!I4-$R$8)*(Sheet1!K4/360)))*(1+((Sheet1!I5-$R$8)*(Sheet1!K5/360)))*(1+((Sheet1!I6-$R$8)*(Sheet1!K6/360)))*(1+((Sheet1!I7-$R$8)*(Sheet1!K7/360)))*(1+((Sheet1!I8-$R$8)*(Sheet1!K8/360)))*(1+((Sheet1!I9-$R$8)*(Sheet1!K9/360)))*(1+((P8-$R$8)*(M8/360)))))
For clarity, each color represents a separate term of the entire expression. There are four terms in this case because we had data in four rows (5-9), in other cases I will have more or less
So if date in cell E5 is 8/31/2011 use values from column I of Sheet1 for dates in column J that fall before the date in cell E5 without going past the date in cell E5
This is represented by =((G5)/((1+((Sheet1!I2-$R$8)*(Sheet1!K2/360)))*(1+((Sheet1!I3-$R$8)*(Sheet1!K3/360)))* portion of the formula
The final part of the formula *(1+((P5-$R$8)*(M5/360)))))
Cell S9 is the difference between the given value in cell R7 and the value arrived at from the above calculation
Cell R8 is being populated by using excel solver to solve for the value of R8 given that the value in cell S9 needs to equal zero
The macro including the solver macro would have to be run every time there is any value in the workbook that changes or basically it would need to be constantly running in real-time
I would need to do this for multiple sheets similar to Sheet3, the difference being that each sheet will have a different number of dates and values in column B, C, and D.
Column B, C, and D will always start at row 5, what I don’t know (it is dynamic) is the number of cells down that dates and values in column B, C, and D will be populated
If you could please include information on how to replicate this code for other sheets just like Sheet3, that would be great.
Thanks a lot,
A
