C McHale

I am running a loop that calculates information on stocks. It is set up a little bit like this:

- Run sql to read a starting row for stock data depending on what he input date is

- the program then loops for a certain number of days performing calculations and comparisons compared to that starting date. When the desired information is found, it starts on the next date and continues to run.

The problem i am having is that i am using a variable, say LoopDate, which has 1 day added to it every time it loops so it is LoopDate.AddDays(1).

When this loop runs, since it just adds 1 to the date, it tries to pull information from weekends or holidays, even though they do not exist in the database. This causes two problems. The first is that it attempts to run calculations on that day, but since there is no information in the database, it copies the most recent data (usually friday's of that week) and then calculates it. This throws off the number of days that the calculation is running for (which is important) as well as the caclulated total.

I know this may sound confusing, i can clarify further if needed, but does anyone have any idea how i can get the calculator to only run for the days in the database while still being able to speicify how long it loops for



Re: Visual Basic Language Looping Problem

John Oliver (UK)MSP, VSIP

Hi,

Try something like this and please read my code comments.

Regards,

John

Code Block

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

'Set a DATE.

Dim myDate As Date = Now

Dim loopCount_NumberOfDays As Integer = 0

Dim outputString As String = ""

Do

Select Case myDate.DayOfWeek

Case DayOfWeek.Monday To DayOfWeek.Friday

outputString &= myDate.ToShortDateString & vbCrLf

'Do your calculations here.>>

'Add one to the current date.

myDate = myDate.AddDays(1)

loopCount_NumberOfDays += 1

Case DayOfWeek.Saturday

'Add 1 day if it is a Saturday to show the Sunday message!!

myDate = myDate.AddDays(1)

loopCount_NumberOfDays += 1

outputString &= "**** Saturday skipped!! ****" & vbCrLf

Case DayOfWeek.Sunday

'Add 1 day if it is a Sunday.

myDate = myDate.AddDays(1)

loopCount_NumberOfDays += 1

outputString &= "**** Sunday skipped!! ****" & vbCrLf

End Select

'Loop for 14 days in total.

Loop Until loopCount_NumberOfDays = 14

MessageBox.Show(outputString)

MessageBox.Show("Loop ran for " & loopCount_NumberOfDays.ToString & " days.")

End Sub

End Class






Re: Visual Basic Language Looping Problem

KeetClassic

You can easily adjust for weekends, but since you also want to skip holidays then somewhere at the start of your program you will need to hardcode all the holidays that you know of (the days which do not have your Stock data). Some holidays are always on the same day like Christmas for example, but others like Easter need to be calculated.

If you have a way to test for a holiday by checking the database (you said holiday data does not exist in the database, so this could be one condition to test before adding a day) ...then you can skip that day.

Either way you will need to add more than one to your LoopDate to skip those days you don't want, as John Oliver suggested.





Re: Visual Basic Language Looping Problem

John Oliver (UK)MSP, VSIP

Hi,

Updated code to skip Christmas Day and New Years Day as well as weekends to show how to skip holiday dates.

Regards,

John.

Code Block

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

'Set a DATE.

Dim myDate As Date = Now

Dim loopCount_NumberOfDays As Integer = 0

Dim outputString As String = ""

Do

'To skip a specific holiday like Christmas Day and New Years day.>>

If myDate.Month = 12 And myDate.Day = 25 Then

myDate = myDate.AddDays(1)

loopCount_NumberOfDays += 1

outputString &= "**** Christmas Day skipped!! ****" & vbCrLf

End If

If myDate.Month = 1 And myDate.Day = 1 Then

myDate = myDate.AddDays(1)

loopCount_NumberOfDays += 1

outputString &= "**** New Years Day skipped!! ****" & vbCrLf

End If

Select Case myDate.DayOfWeek

Case DayOfWeek.Monday To DayOfWeek.Friday

outputString &= myDate.ToShortDateString & vbCrLf

'Do your calculations here.>>

'Add one to the current date.

myDate = myDate.AddDays(1)

loopCount_NumberOfDays += 1

Case DayOfWeek.Saturday

'Add 1 day if it is a Saturday to show the Sunday message!!

myDate = myDate.AddDays(1)

loopCount_NumberOfDays += 1

outputString &= "**** Saturday skipped!! ****" & vbCrLf

Case DayOfWeek.Sunday

'Add 1 day if it is a Sunday.

myDate = myDate.AddDays(1)

loopCount_NumberOfDays += 1

outputString &= "**** Sunday skipped!! ****" & vbCrLf

End Select

'Loop for 14 days in total.

Loop Until loopCount_NumberOfDays = 14

MessageBox.Show(outputString)

MessageBox.Show("Loop ran for " & loopCount_NumberOfDays.ToString & " days.")

End Sub

End Class






Re: Visual Basic Language Looping Problem

C McHale

Thanks Guys,

John: In your first reply you show how to skip weekends and run for 14 days total. What do i need to do to skip weekends and run for say 30 weekdays. Please also keep in mind that when these calculations are running, they may possibly be calculating data for upto 6 years.

Thanks





Re: Visual Basic Language Looping Problem

John Oliver (UK)MSP, VSIP

C McHale wrote:

Thanks Guys,

John: In your first reply you show how to skip weekends and run for 14 days total. What do i need to do to skip weekends and run for say 30 weekdays. Please also keep in mind that when these calculations are running, they may possibly be calculating data for upto 6 years.

Thanks

Hi,

30 weekdays is 6 weeks so change the 14 to 42 at the bottom of the Do Loop for 6 whole weeks.

It won't matter if the day it starts on isn't a Monday, you will still get 30 weekdays of calculations.

You may want to show a progress bar to the end user to show something is happening if you are going to run calculations for up to 6 years.

Regards,

John

P.S. Please mark my post(s) As Answer if they have answered your questions.

Code Block

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

'Set a DATE.

Dim myDate As Date = Now

Dim loopCount_NumberOfDays As Integer = 0

Dim outputString As String = ""

Do

'To skip a specific holiday like Christmas Day and New Years day.>>

If myDate.Month = 12 And myDate.Day = 25 Then

myDate = myDate.AddDays(1)

loopCount_NumberOfDays += 1

outputString &= "**** Christmas Day skipped!! ****" & vbCrLf

End If

If myDate.Month = 1 And myDate.Day = 1 Then

myDate = myDate.AddDays(1)

loopCount_NumberOfDays += 1

outputString &= "**** New Years Day skipped!! ****" & vbCrLf

End If

Select Case myDate.DayOfWeek

Case DayOfWeek.Monday To DayOfWeek.Friday

outputString &= myDate.ToShortDateString & vbCrLf

'Do your calculations here.>>

'Add one to the current date.

myDate = myDate.AddDays(1)

loopCount_NumberOfDays += 1

Case DayOfWeek.Saturday

'Add 1 day if it is a Saturday to show the Sunday message!!

myDate = myDate.AddDays(1)

loopCount_NumberOfDays += 1

outputString &= "**** Saturday skipped!! ****" & vbCrLf

Case DayOfWeek.Sunday

'Add 1 day if it is a Sunday.

myDate = myDate.AddDays(1)

loopCount_NumberOfDays += 1

outputString &= "**** Sunday skipped!! ****" & vbCrLf

End Select

'Loop for 42 days in total.

Loop Until loopCount_NumberOfDays = 42

MessageBox.Show(outputString)

MessageBox.Show("Loop ran for " & loopCount_NumberOfDays.ToString & " days.")

End Sub

End Class






Re: Visual Basic Language Looping Problem

C McHale

Thanks i'll give it a shot and let you know. How do i create a status bar It only takes maybe 2 mins tops to run the calculations





Re: Visual Basic Language Looping Problem

John Oliver (UK)MSP, VSIP

C McHale wrote:

Thanks i'll give it a shot and let you know. How do i create a status bar It only takes maybe 2 mins tops to run the calculations

Hi,

Drop a ProgressBar on your FORM and add the following highlighted code.

In fact I have changed and added to the entire code.

You could even add two dateTimePicker controls to your FORM to select the START and the END date and use the value property in your code.

E.G.

Date1 = DateTimePicker1.Value

2 minutes is a fair time if the user does not see something happening.

Note I made the maximum value 42 which is the same as the number of days in the DO LOOP.

6 years will be 2192 days or 2191 days depending on whether 2 leap years are covered or not.

date1 = Convert.ToDateTime("01/01/2009 00:00:00") 'January 1st, 2009.

date2 = Convert.ToDateTime("12/31/2014 00:00:00") 'December 31st,2014.

'is 2191 days.

date1 = Convert.ToDateTime("01/01/2008 00:00:00") 'January 1st, 2008.

date2 = Convert.ToDateTime("12/31/2013 00:00:00") 'December 31st,2013.

'is 2192 days.

Regards,

John

Code Block

Public Class Form1

Private numberOfDays As TimeSpan

Private date1 As Date

Private date2 As Date

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

My.Application.ChangeCulture("en-Us")

'6 years from 2008 to 2013 inclusive.

date1 = Convert.ToDateTime("01/01/2009 00:00:00") 'January 1st, 2008.

date2 = Convert.ToDateTime("12/31/2014 00:00:00") 'December 31st,2013.

numberOfDays = date2.Subtract(date1)

ProgressBar1.Minimum = 0

ProgressBar1.Maximum = numberOfDays.Days + 1

MessageBox.Show("Running for " & (numberOfDays.Days + 1).ToString & " days.")

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

'Set a DATE.

Dim myDate As Date = date1

Dim loopCount_NumberOfDays As Integer = 0

Dim outputString As String = ""

Do

'To skip a specific holiday like Christmas Day and New Years day.>>

If myDate.Month = 12 And myDate.Day = 25 Then

myDate = myDate.AddDays(1)

loopCount_NumberOfDays += 1

outputString &= "**** Christmas Day skipped!! ****" & vbCrLf

End If

If myDate.Month = 1 And myDate.Day = 1 Then

myDate = myDate.AddDays(1)

loopCount_NumberOfDays += 1

outputString &= "**** New Years Day skipped!! ****" & vbCrLf

End If

Select Case myDate.DayOfWeek

Case DayOfWeek.Monday To DayOfWeek.Friday

outputString &= myDate.ToShortDateString & vbCrLf

'Do your calculations here.>>

'Add one to the current date.

myDate = myDate.AddDays(1)

loopCount_NumberOfDays += 1

Case DayOfWeek.Saturday

'Add 1 day if it is a Saturday to show the Sunday message!!

myDate = myDate.AddDays(1)

loopCount_NumberOfDays += 1

outputString &= "**** Saturday skipped!! ****" & vbCrLf

Case DayOfWeek.Sunday

'Add 1 day if it is a Sunday.

myDate = myDate.AddDays(1)

loopCount_NumberOfDays += 1

outputString &= "**** Sunday skipped!! ****" & vbCrLf

End Select

ProgressBar1.Increment(1)

'Loop for 42 days in total.

Loop Until loopCount_NumberOfDays = numberOfDays.Days + 1

MessageBox.Show(outputString)

MessageBox.Show("Loop ran for " & loopCount_NumberOfDays.ToString & " days.")

End Sub

End Class






Re: Visual Basic Language Looping Problem

C McHale

Well this is probably going to sound like a stupid question, how do i add a progress bar. I do not see it in my toolbox





Re: Visual Basic Language Looping Problem

John Oliver (UK)MSP, VSIP

Hi,

Which version of Visual Studio or which Express Edition are you using please

Regards,

John






Re: Visual Basic Language Looping Problem

C McHale

Visual Web Developer Express 2005





Re: Visual Basic Language Looping Problem

John Oliver (UK)MSP, VSIP

Hi,

Are you trying to make this a web application

If not you will need this Express Edition.>>

http://download.microsoft.com/download/f/e/3/fe35b954-f6b1-4af5-a5ba-9ff45eae69fd/ENU/vbsetup.exe

I am off to work now for my 1st night shift.

I will catch up with this thread later, bye for now.

Regards,

John






Re: Visual Basic Language Looping Problem

C McHale

I just tried adding the case to my code and i turned it into an if statemetn

lstdays.Items.Add("Saturday")

If DayOfWeek.Saturday Then

lstdays.Items.Add("Saturday")

End If

If DayOfWeek.Sunday Then

lstdays.Items.Add("Sunday")

End If

When the code runs, every line starts with Saturday

Do i need to create somesort of a calendar function so that the program knows what day of the week each date is

These are my currnent DIM statements:

Protected Sub btnCalculate_Click(ByVal sender As Object, ByVal e As System.EventArgs)

Dim decPercentage As Decimal = txtPercentage.Text

lstdays.Items.Clear()

lblerror.Text = ""

Dim StartDate As DateTime = txtDateFrom.Text

Dim LoopDate As DateTime = StartDate

Select Case LoopDate.DayOfWeek

Case DayOfWeek.Monday To DayOfWeek.Friday

End Select

Dim LoopEndDate As DateTime = LoopDate.AddDays(88)

Dim EndDate As DateTime

Dim StockDate As DateTime = StartDate

If rbEnd.Checked Then

EndDate = txtDateTo.Text

ElseIf rbAll.Checked Then

EndDate = New Date(2006, 12, 29)

End If

----

Connection strings

Specific dims for calculations

----

While (LoopDate >= StartDate and LoopDate <= EndDate)

dbcommands

If LoopDate <= EndDate Then

Try

dbConnect.Open()

dbReader = dbCommand.ExecuteReader

If dbReader.Read() Then

'adds stuff to strings and performs calculations

intdailycounter +=1

Loop

Endif

....





Re: Visual Basic Language Looping Problem

C McHale

Its not a web application, just some sql and a loop that calculates and adds to a list. The after that i at some point need to figure out how to export this list to excel so that further calculations and stuff can be done to it.

Thanks for all your help thusfar





Re: Visual Basic Language Looping Problem

John Oliver (UK)MSP, VSIP

C McHale wrote:

I just tried adding the case to my code and i turned it into an if statemetn

lstdays.Items.Add("Saturday")

If DayOfWeek.Saturday Then

lstdays.Items.Add("Saturday")

End If

If DayOfWeek.Sunday Then

lstdays.Items.Add("Sunday")

End If

When the code runs, every line starts with Saturday

Do i need to create somesort of a calendar function so that the program knows what day of the week each date is

No you don't, there is a built-in function to do this.

Code Block

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

'This will tell that the day NOW is a SUNDAY!!

Dim myDate As Date = Now

Dim dayOfTheWeek As String = ""

dayOfTheWeek = Now.DayOfWeek.ToString

MessageBox.Show(dayOfTheWeek)

End Sub

End Class

Hi,

Can I ask what you are wanting to calculate in the btnCalculate SUB please

Are you trying to add the days Saturday through to Friday as text to a listbox If so why

I would tend to use two dateTimePicker controls for the startDate and endDate respectively.

I could do a fair bit of the code for you, but I don't think I can do the database interaction part, sorry.

Delete this bit.

Code Block

Public Class Form1

End Class

Now PASTE this lot in.

Do not worry how it works.

I am creating the controls and adding them to the FORM at run-time,

although you do not have to do this and your code will look shorter.

It is my suggested starting point however.

If you start with a similiar layout though, Label1 is the top label, Label2 the lower label.

I have a btnCalculate and two DateTimePicker controls;

  • DateTimePicker1 and
  • DateTimePicker2

Code Block

Public Class Form1

Private startDate, endDate As Date

Friend WithEvents DateTimePicker1 As System.Windows.Forms.DateTimePicker

Friend WithEvents DateTimePicker2 As System.Windows.Forms.DateTimePicker

Friend WithEvents btnCalculate As System.Windows.Forms.Button

Friend WithEvents Label1 As System.Windows.Forms.Label

Friend WithEvents Label2 As System.Windows.Forms.Label

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Me.DateTimePicker1 = New System.Windows.Forms.DateTimePicker

Me.DateTimePicker2 = New System.Windows.Forms.DateTimePicker

Me.btnCalculate = New System.Windows.Forms.Button

Me.Label1 = New System.Windows.Forms.Label

Me.Label2 = New System.Windows.Forms.Label

Me.SuspendLayout()

'

'DateTimePicker1

'

Me.DateTimePicker1.Location = New System.Drawing.Point(70, 68)

Me.DateTimePicker1.Name = "DateTimePicker1"

Me.DateTimePicker1.Size = New System.Drawing.Size(200, 20)

Me.DateTimePicker1.TabIndex = 0

'

'DateTimePicker2

'

Me.DateTimePicker2.Location = New System.Drawing.Point(70, 151)

Me.DateTimePicker2.Name = "DateTimePicker2"

Me.DateTimePicker2.Size = New System.Drawing.Size(200, 20)

Me.DateTimePicker2.TabIndex = 1

'

'btnCalculate

'

Me.btnCalculate.Location = New System.Drawing.Point(195, 231)

Me.btnCalculate.Name = "btnCalculate"

Me.btnCalculate.Size = New System.Drawing.Size(75, 23)

Me.btnCalculate.TabIndex = 2

Me.btnCalculate.Text = "Calculate"

Me.btnCalculate.UseVisualStyleBackColor = True

'

'Label1

'

Me.Label1.AutoSize = True

Me.Label1.BackColor = System.Drawing.Color.White

Me.Label1.Location = New System.Drawing.Point(67, 27)

Me.Label1.Name = "Label1"

Me.Label1.Size = New System.Drawing.Size(164, 13)

Me.Label1.TabIndex = 3

Me.Label1.Text = "Select the StartDate here please."

'

'Label2

'

Me.Label2.AutoSize = True

Me.Label2.BackColor = System.Drawing.Color.White

Me.Label2.Location = New System.Drawing.Point(67, 114)

Me.Label2.Name = "Label2"

Me.Label2.Size = New System.Drawing.Size(161, 13)

Me.Label2.TabIndex = 4

Me.Label2.Text = "Select the EndDate here please."

'

'Form1

'

Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)

Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font

Me.ClientSize = New System.Drawing.Size(292, 266)

Me.Controls.Add(Me.Label2)

Me.Controls.Add(Me.Label1)

Me.Controls.Add(Me.btnCalculate)

Me.Controls.Add(Me.DateTimePicker2)

Me.Controls.Add(Me.DateTimePicker1)

Me.Name = "Form1"

Me.Text = "Form1"

Me.ResumeLayout(False)

Me.PerformLayout()

Label2.Hide()

DateTimePicker2.Hide()

End Sub

Private Sub DateTimePicker1_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker1.ValueChanged

startDate = DateTimePicker1.Value

DateTimePicker2.MinDate = startDate

Label2.Show()

DateTimePicker2.Show()

MessageBox.Show("Now choose the endDate please.")

End Sub

Private Sub DateTimePicker2_ValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DateTimePicker2.ValueChanged

endDate = DateTimePicker2.Value

End Sub

Private Sub btnCalculate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCalculate.Click

'Now what is the calculation(s) you wish to perform

End Sub

End Class

Regards,

John