Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 Tx a lo -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 Tx a lot --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 Tx a lot --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to clean one book using data from another? | New Users to Excel | |||
Data Clean Up | Excel Discussion (Misc queries) | |||
clean up data | Excel Discussion (Misc queries) | |||
Clean Up Data | Excel Discussion (Misc queries) | |||
Get out clean XML data | Excel Discussion (Misc queries) |