Clean big arrays of data.... HELP !!

P

Pierre

Hi all,

Let's say I have daily historical data (each has 2 col : date/price)
from 50 stocks on the past 3 years.
How can I "clean" this huge array to keep only the dates that are i
common (meaning, by removing the holidays specific to a country fo
exemple).
I am a little bit stuck because doing a basic double loop is far t
long:rolleyes: :confused: :confused:

Tx a lo
 
B

Bob Phillips

What are the rules for the common dates, that is how would they be
recognised?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
P

Pierre

basically, I want only the dates that are in all the historical data.
(Like that, each line always corresponds to the same day.).

Each "incorrect" date is removed. So by doing that, all the stock's
history should have the same size.


:cool:
 
N

Norman Jones

Hi Pierre,

Your requirements are not clear but here is my best guess.

Presumably you have (at least) thee populated columns: Date/Price/StockName.

Assume :
(1)Your data is on Sheet1 with your dates in columnA
(2)Your holiday dates are listed on Sheet2 in column A with a header in
cell A1
(2)You wish to delete all data rows where the date corresponds to a holiday
date.

If these assumptions work for you, then (on a copy workbook!) try:

Dim rng As Range, rng1 As Range
Dim myDate As Date
Dim res As Variant
Dim i As Long
Dim RngDel As Range
Dim RngHoliday As Range
Dim LCell As Range

With ActiveWorkbook.Sheets("Sheet2")
Set LCell = .Cells(Cells.Rows.Count, "A").End(xlUp)
Set RngHoliday = Range(.Cells(2, 1), LCell)
End With
For i = 1 To RngHoliday.Cells.Count
myDate = RngHoliday(i).Value
With Worksheets("Sheet1")
Set rng1 = .Range(.Cells(1, 1), _
.Cells(Cells.Rows.Count, "A").End(xlUp))
End With

res = Application.Match(CLng(myDate), rng1, 0)

Do While Not IsError(res)
On Error Resume Next
rng1(res).EntireRow.Delete
On Error GoTo 0
res = Application.Match(CLng(myDate), rng1, 0)
Loop
Next i

End Sub
 
P

Pierre

Many Tx, Norman, but my problem is a little bit different... Let me tr
to be more explcit :

I have 10 stocks.
Each stocks has 2 columns : dates and prices
What I want is to keep the date that are in the 10 stocks and remov
the ones that are not common to all stocks.

Let's say I have a price of $34 on the 05/23/03 for stock One but thi
day is not in stock Two, then remove the 05/23/03 from stock One

The goal is not to remove all the holidays but to generate a consisten
matrix ie a matrix with a price for every stocks at every dates

I hope It is clearer... (the problem is that when you spend a lot o
time on a problem, a lot of assumptions seem obvious... but they ar
not !!):( ;) :
 
N

Norman Jones

Hi Pierre,

Assumed that your dates are in column A of sheet1.

Working on a COPY of your workbook , try:

Sub Tester()
'//////////////////////////////////////////////////
' Note: Amend Const NumOfstocks required number!
' Note: Amend the WS (sheet name) to your worksheet!
'//////////////////////////////////////////////////

Dim rng1 As Range, rng2 As Range, rng3 As Range
Dim WS As Worksheet
Const NumOfstocks = 10 '<<< CHANGE!

Set WS = ActiveWorkbook.Sheets("Sheet1")
'<< Change to your sheet name!

With WS
Set rng1 = .Range("A1").CurrentRegion
Set rng2 = rng1.Offset(1).Resize(rng1.Rows.Count - 1)

rng1.Resize(, 1).Insert Shift:=xlToRight
rng2.Columns(1).AutoFit
Set rng3 = rng2.Resize(, 1).Offset(, -1)
rng3.FormulaR1C1 = _
"=COUNTIF(C[1]:C[1],RC[1])<" & NumOfstocks

rng3(1).Offset(-1).Value = "Condition"
.Range("A1").AutoFilter

rng1.AutoFilter Field:=1, Criteria1:="TRUE"
On Error Resume Next ' in case no rows to delete
rng3.SpecialCells(xlCellTypeVisible). _
EntireRow.Delete
On Error GoTo 0
rng1.AutoFilter
.Columns(1).Delete
End With

End Sub
 
Top