stock market days list
Posted by Tan Le on October 01, 2001 8:58 AM
Hello all,
I know Excel can do this:
1. Type in cell A1: Monday, January 01, 2001
2. Fill it down to cell A20
3. Observe the weekend days (Saturday and Sunday)
4. Manually delete those week-end days (because the stock market is not open on the week-end)
5. Re-arrange column A so it looks neatly.
Problem and question 1:
-Too much manual work if you want to fill the whole year in 2001 -- How would you shorten these steps using VBA or Excel?
Problem and question 2:
-Some holidays occur on weekdays and stock market is also not open on those days -- How to include those special off-workdays in the solution of problem 1?
Best regards,
Tan Le
Posted by Juan Pablo on October 01, 2001 9:19 AM
Hey Tan, here's a thing that might help you
You need to activate the Analysis Tool Pack.
First create a list of your holidays, select it and name it Holidays (Using the upper left box near the formula bar).
Now use this macro.
i is the counter of the dates, don't change it, f is the STARTING row you want to put the list (I assumed row 1), and c is the COLUMN you want to put the list, assumed column A.
37256 represents Dec 31st, 2001.
Sub Dates()
Dim i As Long, f As Long, c as Integer
c = 1
f = 1
i = 0
Do
Cells(f, c).formula = "=WORKDAY(DATE(2001,1,1)," & i & ",Holidays)"
i = i + 1
f = f + 1
Loop Until Cells(f - 1, c) >= 37256
End Sub
Juan Pablo
---------------
Posted by Tan Le on October 01, 2001 8:02 PM
Hi Juan,
Hey hey, thanks for taking this one. I did what you tell me.
36892=WORKDAY(DATE(2001,1,1),0,Holidays) in cell A1
#NAME?=WORKDAY(DATE(2001,1,1),1,Holidays) in cell A2
The bug is flagged at the Loop statement.
Any idea, Juan?
Tan Le
Posted by Richard S on October 01, 2001 10:25 PM
Tan,
It worked OK for me. Are you sure you named the range where your holidays are listed EXACTLY the same as the Holidays in the code?
Richard Hi Juan, Hey hey, thanks for taking this one. I did what you tell me. 36892=WORKDAY(DATE(2001,1,1),0,Holidays) in cell A1
: Hey Tan, here's a thing that might help you : You need to activate the Analysis Tool Pack. : First create a list of your holidays, select it and name it Holidays (Using the upper left box near the formula bar). : Now use this macro. : i is the counter of the dates, don't change it, f is the STARTING row you want to put the list (I assumed row 1), and c is the COLUMN you want to put the list, assumed column A. : 37256 represents Dec 31st, 2001. : Sub Dates() : Dim i As Long, f As Long, c as Integer : c = 1 : f = 1 : i = 0 : Do : Cells(f, c).formula = "=WORKDAY(DATE(2001,1,1)," & i & ",Holidays)" : i = i + 1 : f = f + 1 : Loop Until Cells(f - 1, c) >= 37256 : End Sub : Juan Pablo : --------------- :
Posted by Juan Pablo on October 02, 2001 9:21 AM
Tan,
Remember to have the Analysis Tool Pack Add-in installed.
Juan Pablo Tan, It worked OK for me. Are you sure you named the range where your holidays are listed EXACTLY the same as the Holidays in the code? Richard : Hi Juan, : Hey hey, thanks for taking this one. I did what you tell me. : 36892=WORKDAY(DATE(2001,1,1),0,Holidays) in cell A1 : #NAME?=WORKDAY(DATE(2001,1,1),1,Holidays) in cell A2 : The bug is flagged at the Loop statement. : Any idea, Juan? : Tan Le :
Posted by Tan Le on October 02, 2001 9:51 AM
Juan,
The "Data Analysis" in shown in the Tool Menu Bar. I installed it before trying out the Macro.
I am not sure what to do next. Below is what I copied from your post.
Sub Dates()
Dim i As Long, f As Long, c As Integer
c = 1
f = 1
i = 0
Do
Cells(f, c).Formula = "=WORKDAY(DATE(2001,1,1)," & i & ",Holidays)"
i = i + 1
f = f + 1
Loop Until Cells(f - 1, c) >= 37256
End Sub
Tan Le Tan, Remember to have the Analysis Tool Pack Add-in installed. : Tan, : It worked OK for me. Are you sure you named the range where your holidays are listed EXACTLY the same as the Holidays in the code? : Richard
Posted by Juan Pablo on October 02, 2001 11:47 AM
If you use this formula in your worksheet
=WORKDAY(TODAY(),ROW())
Does it work ? Juan, The "Data Analysis" in shown in the Tool Menu Bar. I installed it before trying out the Macro. I am not sure what to do next. Below is what I copied from your post. Dim i As Long, f As Long, c As Integer Cells(f, c).Formula = "=WORKDAY(DATE(2001,1,1)," & i & ",Holidays)" i = i + 1 f = f + 1 End Sub Tan Le : Tan, : Remember to have the Analysis Tool Pack Add-in installed. : Juan Pablo
