Logic Problem: Convert Daily Data to Unique Weekly
Author Message
Logic Problem: Convert Daily Data to Unique Weekly

I've got a logic problem that is causing my gray's to get grayer.

Coverting DAILY price data of stocks (Date, Open, High, Low, Close)
into WEEKLY price data is not difficult. Each week ends on Friday, so
I check for the first day following Friday to start my week, where I
will assign the OPEN price of that first day to the week's OPEN, then
check for the Highest High and Lowest Low price. When I come to Friday
again, I know that the prior week has ended and I then save the HH and
LL price, as well as the last price CLOSE and start my new week.

Where I am confusing myself is when I want to create WEEKLY data that
has an End-of-Week day other than Friday.

For example, say I have a SUB routine called ConvertToWeekly.

Public Sub ConvertToWeekly(ByVal DayOfWeek as Long)

The DayOfWeek will be a value from 1 to 5, where 1 = Mon, 2 = Tue, 3 =
Wed, with 5 being the highest for Friday.

Enum DAYS
Mon = 1
Tue
Wed
Thu
Fri
End Enum

ConvertToWeekly(DAYS.Wed)

The routine is to take DAILY data that is in an array called
DataArray() and convert it to WEEKLY data, where each week ends on
Wednesday (because of DAYS.Wed).

The daily data contains no weekends or holidays.

DataArray() elements are:

.sDate as String
.fOpen as Single
.fHigh as Single
.fLow as Single
.fClose as Single

So what I started out doing is looping through DataArray(zero-based)
until I came to the first day of my first week. This was determined by
doing the following:

Public Sub ConvertToWeekly(ByVal DayOfWeek as Long)

'Default to Friday is DayOfWeek out of range
If DayOfWeek < 1 or DayOfWeek > 5 then DayOfWeek = 5

'This gets the day number of the first day of week

lFirstDay = DayOfWeek + 1
If lFirstDay > 5 Then lFirstDay = 1

*** At this point, I know that each week starts with lFirstDay and
ends with DayOfWeek. Now I need to loop through DataArray and separate
the data into my unique week data sets. These are stored into another
array called WeeklyData(). Same structure as DataArray().

If my DayOfWeek = 3 for Wednesday, for example, that means my weeks
will be made up of the following:

4-5-1-2-3

Therefore, I cannot simply use tests like "If WeekDay is >= 4 and <= 3
then..."

So how might I test for the start of each new week and the end of a
previous week?

Should I somehow convert my DayOfWeek value within the routine to
reflect the first day of the week to be 1 using the WeekDay()
function?

lDayNum = WeekDay(DataArray(n).sDate, vbTuesday)

Okay, my head hurts now and I'm going to take a break and watch Top
Chef. Then keep trying to pan it out. If someone has a suggestion when
I get back, great.

Webbiz

(PS: I'm sure there is a simple way to do this. Whenever I ask these
kinds of questions, someone pops up and says "hey, you know there is a
function for that, don't you? Thing is, I haven't found it yet. :-)

Wed, 14 Mar 2012 03:32:08 GMT
Logic Problem: Convert Daily Data to Unique Weekly
How stupid of me!

It just dawned on me that if I'm going to determine whether the data
has just ended for the current week and is now entering the next week,
all I need to do is add 7 to my start date (making sure that my start
date is correct of course to begin with) and then keep doing this
throughout the loop.

It's amazing what watching 3 episodes of Top Chef in a row can do for
the mind.

Think I got this covered.

:-)
Webbiz

Quote:
>I've got a logic problem that is causing my gray's to get grayer.

>Coverting DAILY price data of stocks (Date, Open, High, Low, Close)
>into WEEKLY price data is not difficult. Each week ends on Friday, so
>I check for the first day following Friday to start my week, where I
>will assign the OPEN price of that first day to the week's OPEN, then
>check for the Highest High and Lowest Low price. When I come to Friday
>again, I know that the prior week has ended and I then save the HH and
>LL price, as well as the last price CLOSE and start my new week.

>Where I am confusing myself is when I want to create WEEKLY data that
>has an End-of-Week day other than Friday.

>For example, say I have a SUB routine called ConvertToWeekly.

>Public Sub ConvertToWeekly(ByVal DayOfWeek as Long)

>The DayOfWeek will be a value from 1 to 5, where 1 = Mon, 2 = Tue, 3 =
>Wed, with 5 being the highest for Friday.

>Enum DAYS
>   Mon = 1
>   Tue
>   Wed
>   Thu
>   Fri
>End Enum

>    ConvertToWeekly(DAYS.Wed)

>The routine is to take DAILY data that is in an array called
>DataArray() and convert it to WEEKLY data, where each week ends on
>Wednesday (because of DAYS.Wed).

>The daily data contains no weekends or holidays.

>DataArray() elements are:

>.sDate as String
>.fOpen as Single
>.fHigh as Single
>.fLow as Single
>.fClose as Single

>So what I started out doing is looping through DataArray(zero-based)
>until I came to the first day of my first week. This was determined by
>doing the following:

>Public Sub ConvertToWeekly(ByVal DayOfWeek as Long)

>    'Default to Friday is DayOfWeek out of range
>    If DayOfWeek < 1 or DayOfWeek > 5 then DayOfWeek = 5

>    'This gets the day number of the first day of week

>    lFirstDay = DayOfWeek + 1
>    If lFirstDay > 5 Then lFirstDay = 1

>   *** At this point, I know that each week starts with lFirstDay and
>ends with DayOfWeek. Now I need to loop through DataArray and separate
>the data into my unique week data sets. These are stored into another
>array called WeeklyData(). Same structure as DataArray().

>If my DayOfWeek = 3 for Wednesday, for example, that means my weeks
>will be made up of the following:

>  4-5-1-2-3

>Therefore, I cannot simply use tests like "If WeekDay is >= 4 and <= 3
>then..."

>So how might I test for the start of each new week and the end of a
>previous week?

>Should I somehow convert my DayOfWeek value within the routine to
>reflect the first day of the week to be 1 using the WeekDay()
>function?

>lDayNum = WeekDay(DataArray(n).sDate, vbTuesday)

>Okay, my head hurts now and I'm going to take a break and watch Top
>Chef. Then keep trying to pan it out. If someone has a suggestion when
>I get back, great.

>Webbiz

>(PS: I'm sure there is a simple way to do this. Whenever I ask these
>kinds of questions, someone pops up and says "hey, you know there is a
>function for that, don't you? Thing is, I haven't found it yet. :-)

Wed, 14 Mar 2012 09:27:28 GMT
Logic Problem: Convert Daily Data to Unique Weekly
get you your start date). You can calculate nth such and such day for a
given month and year (that is, for example, the 2nd Thursday in October,
2009) using this one-liner statement...

NthDayOfMonth= DateSerial(Y, M, 1 + 7 * Nth) - Weekday( _
DateSerial(Y, M, 8 - DOW), vbMonday)

where Y and M are the year and month, DOW is the day of the week (using your
Monday=1, Tuesday=2, etc. schedule) and Nth is which day of the week in the
month you are interested in (Nth = 1 for first such and such day, Nth=2 for
second such and such day, etc.); for example, if you wanted the 2nd Thursday
in October 2009...

Y = 2009
M = 10
Nth = 2
DOW = 4
NthDayOfMonth= DateSerial(Y, M, 1 + 7 * Nth) - Weekday( _
DateSerial(Y, M, 8 - DOW), vbMonday)

--
Rick (MVP - Excel)

Quote:
> How stupid of me!

> It just dawned on me that if I'm going to determine whether the data
> has just ended for the current week and is now entering the next week,
> all I need to do is add 7 to my start date (making sure that my start
> date is correct of course to begin with) and then keep doing this
> throughout the loop.

> It's amazing what watching 3 episodes of Top Chef in a row can do for
> the mind.

> Think I got this covered.

> :-)
> Webbiz

>>I've got a logic problem that is causing my gray's to get grayer.

>>Coverting DAILY price data of stocks (Date, Open, High, Low, Close)
>>into WEEKLY price data is not difficult. Each week ends on Friday, so
>>I check for the first day following Friday to start my week, where I
>>will assign the OPEN price of that first day to the week's OPEN, then
>>check for the Highest High and Lowest Low price. When I come to Friday
>>again, I know that the prior week has ended and I then save the HH and
>>LL price, as well as the last price CLOSE and start my new week.

>>Where I am confusing myself is when I want to create WEEKLY data that
>>has an End-of-Week day other than Friday.

>>For example, say I have a SUB routine called ConvertToWeekly.

>>Public Sub ConvertToWeekly(ByVal DayOfWeek as Long)

>>The DayOfWeek will be a value from 1 to 5, where 1 = Mon, 2 = Tue, 3 =
>>Wed, with 5 being the highest for Friday.

>>Enum DAYS
>>   Mon = 1
>>   Tue
>>   Wed
>>   Thu
>>   Fri
>>End Enum

>> ConvertToWeekly(DAYS.Wed)

>>The routine is to take DAILY data that is in an array called
>>DataArray() and convert it to WEEKLY data, where each week ends on
>>Wednesday (because of DAYS.Wed).

>>The daily data contains no weekends or holidays.

>>DataArray() elements are:

>>.sDate as String
>>.fOpen as Single
>>.fHigh as Single
>>.fLow as Single
>>.fClose as Single

>>So what I started out doing is looping through DataArray(zero-based)
>>until I came to the first day of my first week. This was determined by
>>doing the following:

>>Public Sub ConvertToWeekly(ByVal DayOfWeek as Long)

>>    'Default to Friday is DayOfWeek out of range
>>    If DayOfWeek < 1 or DayOfWeek > 5 then DayOfWeek = 5

>>    'This gets the day number of the first day of week

>>    lFirstDay = DayOfWeek + 1
>>    If lFirstDay > 5 Then lFirstDay = 1

>>   *** At this point, I know that each week starts with lFirstDay and
>>ends with DayOfWeek. Now I need to loop through DataArray and separate
>>the data into my unique week data sets. These are stored into another
>>array called WeeklyData(). Same structure as DataArray().

>>If my DayOfWeek = 3 for Wednesday, for example, that means my weeks
>>will be made up of the following:

>>  4-5-1-2-3

>>Therefore, I cannot simply use tests like "If WeekDay is >= 4 and <= 3
>>then..."

>>So how might I test for the start of each new week and the end of a
>>previous week?

>>Should I somehow convert my DayOfWeek value within the routine to
>>reflect the first day of the week to be 1 using the WeekDay()
>>function?

>>lDayNum = WeekDay(DataArray(n).sDate, vbTuesday)

>>Okay, my head hurts now and I'm going to take a break and watch Top
>>Chef. Then keep trying to pan it out. If someone has a suggestion when
>>I get back, great.

>>Webbiz

>>(PS: I'm sure there is a simple way to do this. Whenever I ask these
>>kinds of questions, someone pops up and says "hey, you know there is a
>>function for that, don't you? Thing is, I haven't found it yet. :-)

Thu, 15 Mar 2012 00:31:30 GMT
Logic Problem: Convert Daily Data to Unique Weekly
Hi Rick.

I appreciate the example code. I'll have to save it for perhaps
something in the future. Unfortunately, it wouldn't apply to this
particular logic problem. The one-liner requires you specify what you
want (2nd Thursday of October), rather than 'give me the first Monday
in my dataset, without knowing which Monday of whatever month it is.'

To get the start date, what I've done is simply loop through the array
from the beginning and test for Monday (or whatever day I'm looking to
be the first day of each week in my resultant dataset).

Here is how the routine ended up.

======== START CODE ===========

Private Sub ConvertToWeekly(ByVal DayOfWeek As Long)
'This routine will convert the DAILY data found in DataArray()
'and covert it to WEEKLY data. DayOfWeek will determine what
'day each week will end. Monday = 1, Tuesday = 2, etc.
'The final results will be stored into the strPrices() array.

Dim lStartIndex As Long
Dim lEndIndex As Long
Dim fHighestHigh As Single
Dim fLowestLow As Single
Dim fLastClose As Single
Dim lFirstDay As Long
Dim i As Long
Dim lCnt As Long
Dim dRefDate As Date

'Make sure DayOfWeek is within proper range. Default to 5 'Friday'
if not.

If DayOfWeek < 1 Or DayOfWeek > 5 Then DayOfWeek = 5

lFirstDay = DayOfWeek + 1
If lFirstDay > 5 Then lFirstDay = 1

'Locate the first day of the first week to start the loop
For i = 0 To UBound(DataArray)
If Weekday(DataArray(i).dDate, vbMonday) = lFirstDay Then
lStartIndex = i
Exit For
End If
Next i

'Locate the last day of the last complete week
For i = UBound(DataArray) To 0 Step -1
If Weekday(DataArray(i).dDate, vbMonday) = DayOfWeek Then
lEndIndex = i
Exit For
End If
Next i

'Now, starting from the first day of the first week, start
creating
'the new weeks

'Initialize starting values
lCnt = 0
ReDim strPrices(lCnt)
fHighestHigh = DataArray(lStartIndex).fHigh
If DataArray(lStartIndex).fLow <> 0 Then
fLowestLow = DataArray(lStartIndex).fLow
fLastClose = DataArray(lStartIndex).fClose
Else
fLowestLow = 99999
End If

dRefDate = ForwardDate(DataArray(lStartIndex).dDate, 4)

For i = lStartIndex To lEndIndex

If DataArray(i).dDate > dRefDate Or i = lEndIndex Then 'moved
into following week

'Now time to save the info
ReDim Preserve strPrices(lCnt)

strPrices(lCnt).dDate = dRefDate 'store end of week date
strPrices(lCnt).fHigh = fHighestHigh
strPrices(lCnt).fLow = fLowestLow
strPrices(lCnt).fClose = fLastClose

'Now reset variables for the new week
dRefDate = dRefDate + 7 'increment it to the end of new
week
fHighestHigh = DataArray(i).fHigh 'start value
If DataArray(i).fLow <> 0 Then
fLowestLow = DataArray(i).fLow 'start value
Else
fLowestLow = 99999
End If
lCnt = lCnt + 1 'records stored

End If

If DataArray(i).fHigh > fHighestHigh Then fHighestHigh =
DataArray(i).fHigh
If DataArray(i).fLow < fLowestLow Then fLowestLow =
DataArray(i).fLow
If DataArray(i).fClose <> 0 Then fLastClose =
DataArray(i).fClose
Next i

End Sub

============= END CODE ===============

Thanks.

Webbiz

On Sat, 26 Sep 2009 12:31:30 -0400, "Rick Rothstein"

Quote:

>get you your start date). You can calculate nth such and such day for a
>given month and year (that is, for example, the 2nd Thursday in October,
>2009) using this one-liner statement...

>NthDayOfMonth= DateSerial(Y, M, 1 + 7 * Nth) - Weekday( _
>                    DateSerial(Y, M, 8 - DOW), vbMonday)

>where Y and M are the year and month, DOW is the day of the week (using your
>Monday=1, Tuesday=2, etc. schedule) and Nth is which day of the week in the
>month you are interested in (Nth = 1 for first such and such day, Nth=2 for
>second such and such day, etc.); for example, if you wanted the 2nd Thursday
>in October 2009...

>Y = 2009
>M = 10
>Nth = 2
>DOW = 4
>NthDayOfMonth= DateSerial(Y, M, 1 + 7 * Nth) - Weekday( _
>                    DateSerial(Y, M, 8 - DOW), vbMonday)

Fri, 16 Mar 2012 14:32:38 GMT
Logic Problem: Convert Daily Data to Unique Weekly

I'd mention that you can know ahead of time how many
weeks are in the data array, and can therefore dimension
an array is time consuming,  you might as well avoid it, since you
can.

Here is another approach.  If you know that your data is contigious
(Mon-Fri) without any gaps, you can use loop counters to track
the days of the week.  Again if  your data is contigious then you
know there is always 5 days in every week so you can loop like:

For w = 1 to weeks
For d = 1 to 5
' Process week data
Next
' Save week hi's and Lo's
Next

All you need to know is which index of the data array is the first
day in the first week and can simply increment that counter in the
1 to 5 loop as you go through all of the data array.

Something like the following would also work, but I don't have your
data and so did not test it, but see if you can gleen some use out of it
in your own code...  (Also note I used VB's own day constants)

LFS

Private Sub CWeekly(ByVal WeekDayEnd As VBA.VbDayOfWeek)
Dim DataIndex As Long, Lo As Long, Hi As Long, Ls As Long, Wk As Long, Dy As Long
Dim OutIndex As Long, OutMax As Long, Start As Long

If (WeekDayEnd < vbMonday) Or (WeekDayEnd > vbFriday) Then
WeekDayEnd = vbFriday
End If

' Get data start date
Start = CLng(DataArray(0).Date)
Start = Start - (Start \ 7) * 7  ' (Pseudo Weekday function)
' Assign start index
If Start <= WeekDayEnd Then
DataIndex = (WeekDayEnd - Start) + 1
Else
DataIndex = (WeekDayEnd - Start) + 4
End If

' Verifications
Debug.Print
Debug.Print "Week ends on: "; Format(WeekDayEnd, "ddd")
Debug.Print "Data(0) starts on: "; Format(DataArray(0).Date, "ddd")
Debug.Print "First day at index: "; DataIndex

OutMax = ((UBound(DataArray) - DataIndex) \ 5) - 1  ' Not tested (but can work)

Debug.Print "Data Array ("; UBound(DataArray); ") has "; OutMax + 1; " weeks"

' Allocate space
ReDim Out(0 To OutMax) As UDT

For Wk = 0 To OutMax
' Init weekly hi's & lo's
Hi = -1
Lo = 99999
Ls = 0
' Process week
For Dy = 1 To 5
If DataArray(DataIndex).Hi > Hi Then
Hi = DataArray(DataIndex).Hi
End If
If DataArray(DataIndex).Last > 0 Then
Ls = DataArray(DataIndex).Last
End If
If DataArray(DataIndex).Lo > 0 Then
If DataArray(DataIndex).Lo < Lo Then
Lo = DataArray(DataIndex).Lo
End If
End If
DataIndex = DataIndex + 1
Next  ' Dy
' Save hi's & lo's
Out(OutIndex).Hi = Hi
Out(OutIndex).Lo = Lo
Out(OutIndex).Last = Ls
Out(OutIndex).Date = DataArray(DataIndex - 1).Date
OutIndex = OutIndex + 1
Next    ' Wk

End Sub

Fri, 16 Mar 2012 22:02:43 GMT
Logic Problem: Convert Daily Data to Unique Weekly
On Mon, 28 Sep 2009 09:02:43 -0500, "Larry Serflaten"

Quote:

>I'd mention that you can know ahead of time how many
>weeks are in the data array, and can therefore dimension
>an array is time consuming,  you might as well avoid it, since you
>can.

Didn't think of that. Thanks.

Quote:

>Here is another approach.  If you know that your data is contigious
>(Mon-Fri) without any gaps, you can use loop counters to track
>the days of the week.  Again if  your data is contigious then you
>know there is always 5 days in every week so you can loop like:

>For w = 1 to weeks
>  For d = 1 to 5
>     ' Process week data
>  Next
>  ' Save week hi's and Lo's
>Next

Nope. Just checked. It's got gaps. Holidays are not in the data set.

That would have been pretty cool though.

:-)

Quote:

>All you need to know is which index of the data array is the first
>day in the first week and can simply increment that counter in the
>1 to 5 loop as you go through all of the data array.

>Something like the following would also work, but I don't have your
>data and so did not test it, but see if you can gleen some use out of it
>in your own code...  (Also note I used VB's own day constants)

>LFS

>Private Sub CWeekly(ByVal WeekDayEnd As VBA.VbDayOfWeek)
>Dim DataIndex As Long, Lo As Long, Hi As Long, Ls As Long, Wk As Long, Dy As Long
>Dim OutIndex As Long, OutMax As Long, Start As Long

>  If (WeekDayEnd < vbMonday) Or (WeekDayEnd > vbFriday) Then
>    WeekDayEnd = vbFriday
>  End If

>  ' Get data start date
>  Start = CLng(DataArray(0).Date)
>  Start = Start - (Start \ 7) * 7  ' (Pseudo Weekday function)
>  ' Assign start index
>  If Start <= WeekDayEnd Then
>     DataIndex = (WeekDayEnd - Start) + 1
>  Else
>     DataIndex = (WeekDayEnd - Start) + 4
>  End If

>' Verifications
>Debug.Print
>Debug.Print "Week ends on: "; Format(WeekDayEnd, "ddd")
>Debug.Print "Data(0) starts on: "; Format(DataArray(0).Date, "ddd")
>Debug.Print "First day at index: "; DataIndex

>  OutMax = ((UBound(DataArray) - DataIndex) \ 5) - 1  ' Not tested (but can work)

>Debug.Print "Data Array ("; UBound(DataArray); ") has "; OutMax + 1; " weeks"

>  ' Allocate space
>  ReDim Out(0 To OutMax) As UDT

>  For Wk = 0 To OutMax
>    ' Init weekly hi's & lo's
>    Hi = -1
>    Lo = 99999
>    Ls = 0
>    ' Process week
>    For Dy = 1 To 5
>      If DataArray(DataIndex).Hi > Hi Then
>        Hi = DataArray(DataIndex).Hi
>      End If
>      If DataArray(DataIndex).Last > 0 Then
>         Ls = DataArray(DataIndex).Last
>      End If
>      If DataArray(DataIndex).Lo > 0 Then
>        If DataArray(DataIndex).Lo < Lo Then
>          Lo = DataArray(DataIndex).Lo
>        End If
>      End If
>      DataIndex = DataIndex + 1
>    Next  ' Dy
>    ' Save hi's & lo's
>    Out(OutIndex).Hi = Hi
>    Out(OutIndex).Lo = Lo
>    Out(OutIndex).Last = Ls
>    Out(OutIndex).Date = DataArray(DataIndex - 1).Date
>    OutIndex = OutIndex + 1
>  Next    ' Wk

>End Sub

Sat, 17 Mar 2012 05:51:09 GMT
Logic Problem: Convert Daily Data to Unique Weekly
Obviously, I misread what you were intending to do.

--
Rick (MVP - Excel)

Quote:
> Hi Rick.

> I appreciate the example code. I'll have to save it for perhaps
> something in the future. Unfortunately, it wouldn't apply to this
> particular logic problem. The one-liner requires you specify what you
> want (2nd Thursday of October), rather than 'give me the first Monday
> in my dataset, without knowing which Monday of whatever month it is.'

> To get the start date, what I've done is simply loop through the array
> from the beginning and test for Monday (or whatever day I'm looking to
> be the first day of each week in my resultant dataset).

> Here is how the routine ended up.

> ======== START CODE ===========

> Private Sub ConvertToWeekly(ByVal DayOfWeek As Long)
> 'This routine will convert the DAILY data found in DataArray()
> 'and covert it to WEEKLY data. DayOfWeek will determine what
> 'day each week will end. Monday = 1, Tuesday = 2, etc.
> 'The final results will be stored into the strPrices() array.

> Dim lStartIndex As Long
> Dim lEndIndex As Long
> Dim fHighestHigh As Single
> Dim fLowestLow As Single
> Dim fLastClose As Single
> Dim lFirstDay As Long
> Dim i As Long
> Dim lCnt As Long
> Dim dRefDate As Date

>    'Make sure DayOfWeek is within proper range. Default to 5 'Friday'
> if not.

>    If DayOfWeek < 1 Or DayOfWeek > 5 Then DayOfWeek = 5

>    lFirstDay = DayOfWeek + 1
>    If lFirstDay > 5 Then lFirstDay = 1

>    'Locate the first day of the first week to start the loop
>    For i = 0 To UBound(DataArray)
>        If Weekday(DataArray(i).dDate, vbMonday) = lFirstDay Then
>            lStartIndex = i
>            Exit For
>        End If
>    Next i

>    'Locate the last day of the last complete week
>    For i = UBound(DataArray) To 0 Step -1
>        If Weekday(DataArray(i).dDate, vbMonday) = DayOfWeek Then
>            lEndIndex = i
>            Exit For
>        End If
>    Next i

>    'Now, starting from the first day of the first week, start
> creating
>    'the new weeks

>    'Initialize starting values
>    lCnt = 0
>    ReDim strPrices(lCnt)
>    fHighestHigh = DataArray(lStartIndex).fHigh
>    If DataArray(lStartIndex).fLow <> 0 Then
>        fLowestLow = DataArray(lStartIndex).fLow
>        fLastClose = DataArray(lStartIndex).fClose
>    Else
>        fLowestLow = 99999
>    End If

>    dRefDate = ForwardDate(DataArray(lStartIndex).dDate, 4)

>    For i = lStartIndex To lEndIndex

>        If DataArray(i).dDate > dRefDate Or i = lEndIndex Then 'moved
> into following week

>            'Now time to save the info
>            ReDim Preserve strPrices(lCnt)

>            strPrices(lCnt).dDate = dRefDate 'store end of week date
>            strPrices(lCnt).fHigh = fHighestHigh
>            strPrices(lCnt).fLow = fLowestLow
>            strPrices(lCnt).fClose = fLastClose

>            'Now reset variables for the new week
>            dRefDate = dRefDate + 7 'increment it to the end of new
> week
>            fHighestHigh = DataArray(i).fHigh 'start value
>            If DataArray(i).fLow <> 0 Then
>                fLowestLow = DataArray(i).fLow 'start value
>            Else
>                fLowestLow = 99999
>            End If
>            lCnt = lCnt + 1 'records stored

>        End If

>        If DataArray(i).fHigh > fHighestHigh Then fHighestHigh =
> DataArray(i).fHigh
>        If DataArray(i).fLow < fLowestLow Then fLowestLow =
> DataArray(i).fLow
>        If DataArray(i).fClose <> 0 Then fLastClose =
> DataArray(i).fClose
>    Next i

> End Sub

> ============= END CODE ===============

> Thanks.

> Webbiz

> On Sat, 26 Sep 2009 12:31:30 -0400, "Rick Rothstein"

>>get you your start date). You can calculate nth such and such day for a
>>given month and year (that is, for example, the 2nd Thursday in October,
>>2009) using this one-liner statement...

>>NthDayOfMonth= DateSerial(Y, M, 1 + 7 * Nth) - Weekday( _
>>                    DateSerial(Y, M, 8 - DOW), vbMonday)

>>where Y and M are the year and month, DOW is the day of the week (using
>>your
>>Monday=1, Tuesday=2, etc. schedule) and Nth is which day of the week in
>>the
>>month you are interested in (Nth = 1 for first such and such day, Nth=2
>>for
>>second such and such day, etc.); for example, if you wanted the 2nd
>>Thursday
>>in October 2009...

>>Y = 2009
>>M = 10
>>Nth = 2
>>DOW = 4
>>NthDayOfMonth= DateSerial(Y, M, 1 + 7 * Nth) - Weekday( _
>>                    DateSerial(Y, M, 8 - DOW), vbMonday)

Sun, 18 Mar 2012 01:42:37 GMT

 Page 1 of 1 [ 7 post ]

Relevant Pages