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

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

--
Message posted from http://www.ExcelForum.com

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

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)

"Pierre " wrote in message
...
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 in
common (meaning, by removing the holidays specific to a country for
exemple).
I am a little bit stuck because doing a basic double loop is far to
long:rolleyes: :confused: :confused:

Tx a lot


---
Message posted from http://www.ExcelForum.com/

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

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:


---
Message posted from http://www.ExcelForum.com/

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

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

---
Regards,
Norman

"Pierre " wrote in message
...
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 in
common (meaning, by removing the holidays specific to a country for
exemple).
I am a little bit stuck because doing a basic double loop is far to
long:rolleyes: :confused: :confused:

Tx a lot


---
Message posted from http://www.ExcelForum.com/

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

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 !!):( ;) :

--
Message posted from http://www.ExcelForum.com

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

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

---
Regards,
Norman

"Pierre " wrote in message
...
Many Tx, Norman, but my problem is a little bit different... Let me try
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 remove
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 this
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 consistent
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 of
time on a problem, a lot of assumptions seem obvious... but they are
not !!):( ;) :)


---
Message posted from http://www.ExcelForum.com/