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.

Thanks in Advance!

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.

>Thanks in Advance!

>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
I think this may help you (if not to calculate all your days, at least to
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.

>>Thanks in Advance!

>>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:

>I think this may help you (if not to calculate all your days, at least to
>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
your output array before doing your looping.  ReDimming
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
>your output array before doing your looping.  ReDimming
>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"

>>I think this may help you (if not to calculate all your days, at least to
>>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  
 
 [ 7 post ] 

 Relevant Pages 

1. dates, conversion of data from daily to weekly

2. REQ Daily Weekly Monthly Calander functions ?

3. Help need formula or logic for computing avg daily balance

4. logic problem / have data and situation / need help

5. logic problem / have data and situation / need help

6. Unique Problem, needing Unique Solution.

7. Convert from string to unique identifier

8. Data Access vs Business Logic

9. Need advise on data manipulation - Logic

10. Newbie, with problem converting data

11. ACCESS - Converting - problem with DATA

12. ACCESS - Converting - problem with DATA

 

 
Powered by phpBB® Forum Software