VBA to clean and audit data

Hi,

I have attached a spreadsheet which i normally use to clean and audit some price data. The steps are as follows:

1) download data (date, open, high, low, close) into excel. each worksheet corresponds to a different security

2) Phase 1 clean - takes out all data that is rendered "#N/A N/A" and replaces it with the best previous usable data value

3) Using a simple Z-score test looking backwards and forwards to get rid of any bad data points (e.g. 40,42,43,75,42 --> would lead to taking out 75 and replacing with 43; same would occur with small numbers like 15,16,15.5,2,15.8,15.9 ---> would lead to 2 be replaced by 15.5

4) final cleaned data is re-constructed with date.

These elements are placed are viewable on attached excel sheet.

I would like assistance in creating a loop which will do all of the above on each worksheet tab (ws) from 0 to N.

I am having troubles creating the loop - so any direction or assistance would be greatly appreciated.

Many thanks!!

apologies. - yes total code to actually do the above. the code i do have (which i also got from here) creates individual CSV files from the excel spreadsheet http://http://www.excelforum.com/excel-programming-vba-macros/861933-vba-to-save-multiple-worksheets-as-separate-csv-files-using-name-of-worksheet.html?p=2939518#post2939518..

HI Mike,

Yes that works fine. I tried to add it to your code that saves as .CSV code. I assumed it must be in that loop section - but it gives an error. I assumed:

Option Explicit

Sub abc()
  
  Dim ColNum As Long, RowNum As Integer
  Dim Line As String
  Dim LineValues() As Variant
  Dim OutputFileNum As Integer
  Dim PathName As String
  Dim ws As Worksheet
  Dim lastrow As Long
   
    PathName = ActiveWorkbook.Path
    
    For Each ws In Worksheets
        With ws
        
              lastrow = Cells(Rows.CountLarge, "a").End(xlUp).Row
                Range("f3").Resize(lastrow - 2, 4) = "=IF(B3=""#N/A N/A"",B2,B3)"
                Range("j10").Resize(lastrow - 9, 4) = "=STANDARDIZE(F10,AVERAGE(F3:F9,F11:F12),STDEV(F3:F9,F11:F12))"
                Range("o10").Resize(lastrow - 9, 4) = "=IF(OR(J10>5,J10<-5),F9,F10)"
                Range("b10").Resize(lastrow - 9, 4) = Range("o10").Resize(lastrow - 9, 4).Value
 
                Columns("F:R").EntireColumn.Clear
            ReDim LineValues(1 To .Cells(1, Columns.CountLarge).End(xlToLeft).Column)
                For RowNum = 1 To .Cells(Rows.CountLarge, "a").End(xlUp).Row
                For ColNum = 1 To .Cells(1, Columns.CountLarge).End(xlToLeft).Column
                    LineValues(ColNum) = .Cells(RowNum, ColNum)
                    
            OutputFileNum = FreeFile
            Open PathName & "\" & .Index & ".csv" For Output Lock Write As #OutputFileNum
            
      
                Next
                Line = Join(LineValues, ",")
                Print #OutputFileNum, Line
            Next
            
        End With
            Close OutputFileNum
    Next
End Sub

The loop element really confuses me. I put your new code prior to the CSV code as i expected that we should firstly run the data auditing and clearing first and then do the save CSV section adn placing inside the loop would accomplish that. But It doesn't work. What did i do wrong?

thanks again for your assistance.

hi - its actually stopping the loop.

But it's because i wasn't very clear in my post - my apologies. Each worksheet will have the following format:

AC ME ACME CO
Date PX_OPEN PX_HIGH PX_LOW PX_LAST
27/09/2011 1108.23 1141.13 1075.33 1108.23
28/09/2011 1130.33 1163.47 1097.19 1130.33
29/09/2011 1145.81 1180.21 1111.40 1145.81

with the first date (27/09/2011) in cell A3 and the PX_Last in cell E3 and going down in rows...

So my spread sheet was meant to show each cleaning phase and not the actual spreadsheet format. SO for example

AC ME ACME CO
Date PX_OPEN PX_HIGH PX_LOW PX_LAST
27/09/2011 1108.23 1141.13 1075.33 1108.23
28/09/2011 1130.33 1163.47 1097.19 1130.33
29/09/2011 1145.81 #N/A N/A 1111.40 8145.81

Once passed through the loop becomes

AC ME ACME CO
Date PX_OPEN PX_HIGH PX_LOW PX_LAST
27/09/2011 1108.23 1141.13 1075.33 1108.23
28/09/2011 1130.33 1163.47 1097.19 1130.33
29/09/2011 1145.81 1163.47 1111.40 1130.33

In the same cells...

then we run the "create individual CSV file loop"

with each worksheet being a different stock name...

Apologies..