VBA to clean and audit data

[Yg74]

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!!

[patel45]

Do you need code for the loop or total code ? I don't see any code in the attached file

[Yg74]

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..

[mike7952]

try this on acme sheet

Please Login or Register  to view this content.

[Yg74]

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:

Please Login or Register  to view this content.

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.

[mike7952]

Looks like you forgot the "." in front the ranges. see if this helps

Please Login or Register  to view this content.

[mike7952]

Just noticed are you wanting to create 1 file or a file file for each Sheet?

[mike7952]

This should work.

Please Login or Register  to view this content.

Needed to move this outsite of you Next statement

Please Login or Register  to view this content.

[Yg74]

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..

[mike7952]

Have you tried the code in post #8.
One thing you should change is Cells(1,......

Please Login or Register  to view this content.

to Cells(3,...... So it gets the last column in row 3

Please Login or Register  to view this content.

or if the will only be 5 columns just use

Please Login or Register  to view this content.