azhutch1
asked on
Find a value for a particular weekday (Friday) then calculateinformation backwards from that date to the next date
I have a list of values in a range sorted by date and I want to calculate the minimum and maximum values in separate columns in that list for the previous week (5 days) and then calculate the same for the week that precedes that until the list and so on until the end of list. The day of the week from which I wish to calculate is Friday. There will be times due to holidays that the previous week may not have five days. I am using EXCEL 2010 and would like to have a VBA routine to perform this task. I am a novice VBA programmer. Can anyone help.
I'm not quite sure of the question, but you can use the WEEKDAY function to check which day of the week the date is on. Friday will be 5, Saturday will be 6, sunday will be 7, Monday will be 1, etc.
Based on that, you should be able to determine an offset from Friday to, for example, find the previous and next friday:
=IF(WEEKDAY(A1)<5,A1+(5-WE
that should force the date in A1 to become a friday in the cell with the formula (like B1)
From there, you can subtract 7 for the previous Friday or add 7 for the next friday.
(And I'll need to check on the Sunday value...you may need to test for that separately or it may be a week off.)
Based on that, you should be able to determine an offset from Friday to, for example, find the previous and next friday:
=IF(WEEKDAY(A1)<5,A1+(5-WE
that should force the date in A1 to become a friday in the cell with the formula (like B1)
From there, you can subtract 7 for the previous Friday or add 7 for the next friday.
(And I'll need to check on the Sunday value...you may need to test for that separately or it may be a week off.)
ASKER
I have the Weekday formula already. I use this to generate the closest Friday from today lets say. I then import a list of daily information for the previous five weeks from that Friday's date in a table, sorted from oldest date to newest. What I want is to programatically calculate minimum and maximum values (which are in adjacent columns for each date for that particular week only and then do so for the remaining previous five weeks. I dont know if I am describing the issue well or not. Thank you though for the suggested formula.
So there are a lot of values to the right of each date, and you want min and max (there are the min and max functions for that.)
What I read so far is a column of dates, sorted. You already know the closest (or desired) Friday.
Where do the min and max parts come in? Is there data somewhere else on the sheet?
Is this an example?
1/1 12 17 23 14
1/2 18 25 16
1/5 22 17 8
1/8 42 1 16 23 18
...
And (assuming that 1/1-1/5 are all part of the same week), you want to show min=8 and max=25? Spanning multiple rows?
If so, I'll send you some VBA for that.
What I read so far is a column of dates, sorted. You already know the closest (or desired) Friday.
Where do the min and max parts come in? Is there data somewhere else on the sheet?
Is this an example?
1/1 12 17 23 14
1/2 18 25 16
1/5 22 17 8
1/8 42 1 16 23 18
...
And (assuming that 1/1-1/5 are all part of the same week), you want to show min=8 and max=25? Spanning multiple rows?
If so, I'll send you some VBA for that.
ASKER
The list with the data to the right of the date is exactly the problem. The issue that there may or may not be exactly five dates between one Friday and the next previous Friday compounds the request. Thank you for your understanding.
so did I describe it right? is there a varaible number of columns of values, or fixed?
and I guess you can have any number of rows for a given week?
and how do you want to show the min and max? In a cell on the same sheet? save in a separate file?
The general strategy I would use is:
* Start at row 1 and repeat until first date cell in a row is blank
* For each date in a given week:
* Check from next column until empty cell found
* If value is less than previous min, save it; if more than prev max, save it
* When new week found, or end of list, put the min and the max for the previous block into a designated location (e.g. column B, next to the last date in the group.)
Does that sounds right?
and I guess you can have any number of rows for a given week?
and how do you want to show the min and max? In a cell on the same sheet? save in a separate file?
The general strategy I would use is:
* Start at row 1 and repeat until first date cell in a row is blank
* For each date in a given week:
* Check from next column until empty cell found
* If value is less than previous min, save it; if more than prev max, save it
* When new week found, or end of list, put the min and the max for the previous block into a designated location (e.g. column B, next to the last date in the group.)
Does that sounds right?
ASKER
You did describe it correctly. I would like to return the result max and min for each week on a sheet named "Summary" in separate rows in the column headed by the date in question Row 9 for the Max and Row 11 for the Min.
There are seven different columns on the sheet with the data. These are labeled: "Date" "Open" "High" "Low" "Close", "Volume" and "Adj Close". Each and every row has data corresponding to the respective columns. The maximum high and the minimum low would be calculated saved and placed separately on the Summary sheet. Thank you!
There are seven different columns on the sheet with the data. These are labeled: "Date" "Open" "High" "Low" "Close", "Volume" and "Adj Close". Each and every row has data corresponding to the respective columns. The maximum high and the minimum low would be calculated saved and placed separately on the Summary sheet. Thank you!
Just a thought while I work on it...
Will anything ever be higher than the "High" column or lower than the "Low" column? And should Volume be skipped?
It seems that if you added one more column to calculate the Friday for the date, you could probably throw the data into a pivot table to get all of the information, summarized on the same sheet or a Summary sheet, just about the way you want it. Then you only need to change the data source when new data arrives.
Will anything ever be higher than the "High" column or lower than the "Low" column? And should Volume be skipped?
It seems that if you added one more column to calculate the Friday for the date, you could probably throw the data into a pivot table to get all of the information, summarized on the same sheet or a Summary sheet, just about the way you want it. Then you only need to change the data source when new data arrives.
ASKER
I was going to calculate the average daily volume for each respective period, but thought that asking would be a little too presumptuous. I am not really that familiar with pivot tables, but if you think it might work without too much maintenance, I would be willing to try it. Thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oh, sorry...change this subroutine to point to the correct rows for the high and low:
Private Sub ShowResults(CalcDate As Date, MinValue As Double, MaxValue As Double)
' Sheets("Summary").Cells(1,
Sheets("Summary").Cells(11
Sheets("Summary").Cells(9,
miNextSummaryColumn = miNextSummaryColumn + 1
End Sub
Private Sub ShowResults(CalcDate As Date, MinValue As Double, MaxValue As Double)
' Sheets("Summary").Cells(1,
Sheets("Summary").Cells(11
Sheets("Summary").Cells(9,
miNextSummaryColumn = miNextSummaryColumn + 1
End Sub
As for the pivot tables, it's all a matter of selecting all of the pieces for the pivot table box.
If you want to try, you'll probably want one for each Min and Max
* menu tab Insert | Tables group | Pivot Table
* Select the data block (probably something like A1: G999
* Select a destination where the pivot table box will appear (usually to the right of all data or on a different sheet
* Click OK and a set of boxes will appear on the sheet, along with a set of fields to choose to drop into the boxes.
* Drag "Date" from the list and drop along the top
* Drag "High" and drop into the Values section
* Change to show max by going to the top-left corner of the box (Sum of High) and right-click then pick "Summarize Values By" then select Max.
* Finally, go to the Date dropdown (near the top-left) and right-click and select Group
* Change the grouping to Days then the number to 7.
You should now have your Max summary.
Do the same for Min.
when done, you should be able to copy these and paste them into your summary page if all the date groups match.
If the data changes, right-click on the pivot table and select Refresh.
To delete the pivot table, select a range of cells that surrounds the entire table and delete the cells.
If you want to try, you'll probably want one for each Min and Max
* menu tab Insert | Tables group | Pivot Table
* Select the data block (probably something like A1: G999
* Select a destination where the pivot table box will appear (usually to the right of all data or on a different sheet
* Click OK and a set of boxes will appear on the sheet, along with a set of fields to choose to drop into the boxes.
* Drag "Date" from the list and drop along the top
* Drag "High" and drop into the Values section
* Change to show max by going to the top-left corner of the box (Sum of High) and right-click then pick "Summarize Values By" then select Max.
* Finally, go to the Date dropdown (near the top-left) and right-click and select Group
* Change the grouping to Days then the number to 7.
You should now have your Max summary.
Do the same for Min.
when done, you should be able to copy these and paste them into your summary page if all the date groups match.
If the data changes, right-click on the pivot table and select Refresh.
To delete the pivot table, select a range of cells that surrounds the entire table and delete the cells.
ASKER
Wow. Thank you very much! Great work and I will definitely work on the pivot tables as this might be a needed addition to the workbook.
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
ASKER