Solved

# Find a value for a particular weekday (Friday) then calculateinformation  backwards from that date to the next date

Posted on 2011-02-14
181 Views
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.
0
Question by:azhutch1
• 7
• 6

Author Comment

ID: 34890667
Any help I can get will be greatly appreciated!
0

LVL 22

Expert Comment

ID: 34890683
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-WEEKDAY(A1)), WEEKDAY(A1)-5)

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.)
0

Author Comment

ID: 34890752
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.
0

LVL 22

Expert Comment

ID: 34890881
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.
0

Author Comment

ID: 34890930
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.
0

LVL 22

Expert Comment

ID: 34891019
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?
0

Author Comment

ID: 34891210
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!
0

LVL 22

Expert Comment

ID: 34891271
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.
0

Author Comment

ID: 34891300
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
0

LVL 22

Accepted Solution

rspahitz earned 250 total points
ID: 34892404
Well, here's the code I came up with, which you can stick into the code window for the sheet.  If you're not familiar with this, I'll give instructions in the next message.
Also note that you should run the procedure called CalculateWeeklyMinAndMax, and you may want to add a button to your form to run it.

``````Private miNextSummaryColumn As Integer

Sub CalculateWeeklyMinAndMax()
Dim iRow As Integer
Dim iColumn As Integer
Dim dtCalcDateCurrent As Date
Dim dtCalcDateNext As Date
Dim strCellValue As String
Dim dblMin As Double
Dim dblMax As Double

miNextSummaryColumn = 10

iRow = 2 ' skip header
dtCalcDateCurrent = getCalcDate(iRow)
dblMin = 99999
dblMax = 0
Do Until Cells(iRow, 1).Value = ""
' Find min and max for the row
For iColumn = 2 To 7
If iColumn <> 6 Then ' skip for "Volume" data
strCellValue = Cells(iRow, iColumn).Value
If Val(strCellValue) < dblMin Then
dblMin = Val(strCellValue)
End If
If Val(strCellValue) > dblMax Then
dblMax = Val(strCellValue)
End If
End If
Next iColumn

iRow = iRow + 1
dtCalcDateNext = getCalcDate(iRow)
If dtCalcDateNext <> dtCalcDateCurrent Then
' New date so show result in summary sheet
ShowResults dtCalcDateCurrent, dblMin, dblMax
dtCalcDateCurrent = dtCalcDateNext
dblMin = 99999
dblMax = 0
End If
Loop
End Sub

Private Function getCalcDate(RowNumber As Integer)
' assumes that column 7 has this formula: =IF(WEEKDAY(A2) <6,A2+(6-WEEKDAY(A2)), A2+7-MOD(WEEKDAY(A2),7)-1)
' if not, then check the date in the first column of the row and figure out the next Friday
getCalcDate = Cells(RowNumber, 8).Value
End Function

Private Sub ShowResults(CalcDate As Date, MinValue As Double, MaxValue As Double)
Sheets("Summary").Cells(1, miNextSummaryColumn).Value = CalcDate
Sheets("Summary").Cells(2, miNextSummaryColumn).Value = MinValue
Sheets("Summary").Cells(3, miNextSummaryColumn).Value = MaxValue
miNextSummaryColumn = miNextSummaryColumn + 1
End Sub
``````
0

LVL 22

Expert Comment

ID: 34892433
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, miNextSummaryColumn).Value = CalcDate
Sheets("Summary").Cells(11, miNextSummaryColumn).Value = MinValue
Sheets("Summary").Cells(9, miNextSummaryColumn).Value = MaxValue
miNextSummaryColumn = miNextSummaryColumn + 1
End Sub
0

LVL 22

Expert Comment

ID: 34892517
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.
0

Author Comment

ID: 34892545
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.
0

LVL 24

Expert Comment

ID: 35221194
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.
0

## Featured Post

### Suggested Solutions

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tabâ€¦
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directiâ€¦
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a â€¦