Process All CSV Files In SubFolders

Oh, I did not know, I though when it is divided by rows in Excel, it will be same in txt. But yeah, the source data should be divided into rows as it is in the sheet "original" in #3

Kenneth,

It opens fine in Excel and UltraEdit. And In my NotePad, too. :dunno

SamT

I changed decimal symbol to a dot accordingly, but no change, the code runs without an error, but no output is shown

OK. I just ran sub #19 with path C:\CSVs\ with two folder in it. The results were

0 0 table_aapl.csv

-9E+30 0 table_abbv.csv

The second 0 is because there is only one formula in the code.

When I said earler to change the msgbox trigger from 10 to 1, you need to cahnge it to 0 unless you run 2 files. Fn starts at zero. My bad.

OK. I swapped the files in their folders and edited the code to eliminate the extra zero and put the filename in column A.
The results

table_abbv.csv -9E+30

table_aapl.csv 0

If Maxxino can't temporarily change the decimal seperator, we will have to Replace the comma with a semicolon, then replace the dot with a comma. Then WE can't test ti wihtout changing our Regional Settings :)

Show us what you have in this line of code
From Code # 16

Code:

Const FolderPath As String = "The Path to your folder must go here\" 'include ending \

From Code #19

Code:

Const FolderPath As String = "X:\snb\maxxino\" 'include ending \

From my Workbook, where it ran fine

Code:

Const FolderPath As String = "C:\CSVs\" 'include ending \

Maybe you used Notepad+ Sam?

It is obvious that since Excel opens it ok, and a short test that I did, that your CSV files M are vbLF delimited. As such, one line of code needs to be changed.

Code:

Sub M_snb3() Dim sn() As String, c00() As String, j As Long, jj As Long Dim sp() As String, y As Double sn = Filter(Split(CreateObject("wscript.shell").exec _ ("cmd /c Dir ""X:\snb\maxxino\table\*.csv"" /b /s").stdout.readall, vbCrLf), ".") ReDim c00(1 To UBound(sn) + 1, 1 To 2) As String With CreateObject("scripting.filesystemobject") For j = 0 To UBound(sn) y = 0 sp = Split(.OpenTextFile(sn(j)).readall, vbLf) For jj = 0 To UBound(sp) - 1 sp(jj) = Log(Split(sp(jj), ",")(5)) / Log(10) If jj > 0 Then y = y + 100 * (sp(jj) - sp(jj - 1)) ^ 2 Next jj 'c00(j + 1, 1) = sn(j) 'CSV Full Filename c00(j + 1, 1) = .GetBasename(sn(j)) 'CSV Base Filename c00(j + 1, 2) = y 'Result Next j End With Range("A1").Resize(UBound(c00), 2).Value = c00 Columns("A:B").AutoFit End Sub

IF the decimal separator is an issue, you can change it from the hard coding in the code to use the regional setting by:

Code:

Application.DecimalSeparator

In the code from post #19, insert the two tests as shown below. The sub will stop as soon as you click on the second MsgBox. Note what the messages are. The first will give you the full path and name of the file that is opening.

Code:

'''''''''''''For testing only MsgBox "Opening file: " & FileNameArray(Fn) 'Exit Sub '''''''''''''End test ''''Put all lines from one file in Arrays FileLinesArray = Split(.opentextfile(FileNameArray(Fn)).readall, vbCrLf) Formula1LinesArray = FileLinesArray Formula2LinesArray = FileLinesArray 'Initializ Formula1Result = 0 Formula2Result = 0 ''''Calcuate first result for one file For Fl = 0 To UBound(FileLinesArray) - 1 '''''''''''''For testing only MsgBox "The first line in the file is " & Formula1LinesArray(Fl) Exit Sub '''''''''''''End test '''' Calculate first formula

Kenneth,

MS Notepad v 5.1. I tried Notepad+ a few years back, but didn't keep it, because I had UltraEdit and was/am very familiar with it.

Did you notice? I ran your last version of my code just fine on his two zipped files.

Ureka! I unzipped them again, this time with 7zip, and reproduced the problem. The first time I used AlZip.

So. I dug thru my archives and installed WinZip 7. Same Issue: one long string of numbers.

Alzip is free.

:dunno :dunno :dunno

Here is the contents of one of those files

Quote:

20130102,855,35.49,35.49,35.49,35.49,217,1,0,0
20130102,905,34.57,34.6,34.57,34.6,300,1,0,0
20130102,915,34.7,34.7,34.7,34.7,27092,1,0,0
20130102,925,34.66,34.71,34.62,34.62,1800,1,0,0
20130102,930,34.92,34.92,34.4,34.62,192824,1,0,0
20130102,935,34.7563,34.86,34.625,34.81,132380,1,0,0
20130102,940,34.805,35.2,34.805,35.2,79402,1,0,0
20130102,945,35.2,35.2,34.8,34.95,316021,1,0,0
20130102,950,34.83,34.9463,34.6,34.65,34657,1,0,0
20130102,955,34.639,34.83,34.52,34.72,76530,1,0,0
20130102,1000,34.72,34.72,34.66,34.7,61116,1,0,0
20130102,1005,34.6793,34.6997,34.6,34.6365,159095,1,0,0
20130102,1010,34.6289,34.7,34.49,34.7,153262,1,0,0
20130102,1015,34.6999,34.6999,34.4,34.53,80633,1,0,0
20130102,1020,34.53,34.53,34.48,34.491,101987,1,0,0
20130102,1025,34.495,34.51,34.38,34.39,113451,1,0,0
20130102,1030,34.3862,34.46,34.18,34.29,149975,1,0,0
20130102,1035,34.3,34.36,34.12,34.3201,95972,1,0,0
20130102,1040,34.335,34.37,34.14,34.365,101404,1,0,0
20130102,1045,34.36,34.42,34.36,34.37,135165,1,0,0
20130102,1050,34.37,34.37,34.2,34.281,74406,1,0,0
20130102,1055,34.29,34.4,34.27,34.4,164469,1,0,0
20130102,1100,34.4,34.4,34.24,34.3,111185,1,0,0
20130102,1105,34.3,34.38,34.27,34.37,190205,1,0,0
20130102,1110,34.37,34.37,34.27,34.34,125935,1,0,0
20130102,1115,34.33,34.35,34.25,34.25,209730,1,0,0
20130102,1120,34.27,34.3,34.23,34.29,78973,1,0,0
20130102,1125,34.29,34.35,34.26,34.35,73156,1,0,0
20130102,1130,34.35,34.45,34.34,34.4,178697,1,0,0
20130102,1135,34.4,34.4,34.25,34.29,126977,1,0,0
20130102,1140,34.2999,34.38,34.28,34.335,96976,1,0,0
20130102,1145,34.315,34.34,34.12,34.27,83276,1,0,0
20130102,1150,34.264,34.27,34.185,34.23,158084,1,0,0
20130102,1155,34.24,34.24,34.12,34.1935,107202,1,0,0
20130102,1200,34.195,34.21,34.1,34.2,103130,1,0,0
20130102,1205,34.21,34.23,34.19,34.225,50471,1,0,0
20130102,1210,34.225,34.34,34.22,34.321,95644,1,0,0
20130102,1215,34.34,34.5,34.25,34.47,302964,1,0,0
20130102,1220,34.47,34.68,34.36,34.48,228406,1,0,0
20130102,1225,34.49,34.51,34.42,34.4932,277869,1,0,0
20130102,1230,34.5,34.7,34.48,34.69,125386,1,0,0
20130102,1235,34.69,34.7,34.46,34.54,104569,1,0,0
20130102,1240,34.53,34.54,34.46,34.46,28719,1,0,0
20130102,1245,34.47,34.47,34.43,34.445,65092,1,0,0
20130102,1250,34.44,34.51,34.44,34.505,72991,1,0,0
20130102,1255,34.51,34.7,34.47,34.57,218060,1,0,0
20130102,1300,34.59,34.65,34.55,34.65,39236,1,0,0
20130102,1305,34.65,34.69,34.53,34.63,320401,1,0,0
20130102,1310,34.63,34.69,34.55,34.65,58373,1,0,0
20130102,1315,34.6593,34.69,34.606,34.65,102362,1,0,0
20130102,1320,34.64,34.81,34.64,34.8,364901,1,0,0
20130102,1325,34.8,34.82,34.57,34.72,69342,1,0,0
20130102,1330,34.72,34.85,34.63,34.75,76775,1,0,0
20130102,1335,34.74,34.88,34.7,34.88,309787,1,0,0
20130102,1340,34.88,34.95,34.8,34.88,146592,1,0,0
20130102,1345,34.89,34.93,34.81,34.92,156436,1,0,0
20130102,1350,34.92,34.95,34.836,34.9,116088,1,0,0
20130102,1355,34.895,34.94,34.84,34.89,102850,1,0,0
20130102,1400,34.9,34.96,34.89,34.96,95476,1,0,0
20130102,1405,34.945,35.03,34.9,34.94,227484,1,0,0
20130102,1410,35,35.05,34.87,34.91,213695,1,0,0
20130102,1415,34.92,35.02,34.92,34.96,218564,1,0,0
20130102,1420,34.96,35.06,34.87,35.05,106760,1,0,0
20130102,1425,35.055,35.14,35.04,35.08,62760,1,0,0
20130102,1430,35.072,35.23,35.07,35.2,102741,1,0,0
20130102,1435,35.2,35.23,35.12,35.12,74425,1,0,0
20130102,1440,35.13,35.25,35.12,35.12,117779,1,0,0
20130102,1445,35.12,35.17,35,35.014,119569,1,0,0
20130102,1450,35,35.07,35,35.005,56973,1,0,0
20130102,1455,35.01,35.01,34.99,35,271361,1,0,0
20130102,1500,35,35,34.9495,34.9901,77380,1,0,0
20130102,1505,35,35.05,34.99,35.03,1.17473e+06,1,0,0
20130102,1510,35.03,35.25,35.03,35.05,103381,1,0,0
20130102,1515,35.055,35.07,35,35.05,163033,1,0,0
20130102,1520,35.05,35.13,34.96,34.99,181895,1,0,0
20130102,1525,34.99,35.03,34.83,34.88,58188,1,0,0
20130102,1530,34.88,34.99,34.88,34.985,62392,1,0,0
20130102,1535,34.99,35.07,34.98,35.05,375413,1,0,0
20130102,1540,35.05,35.11,35.04,35.11,188005,1,0,0
20130102,1545,35.1,35.19,35,35,537742,1,0,0
20130102,1550,35.01,35.15,35,35.05,338168,1,0,0
20130102,1555,35.05,35.4,35,35.3,454502,1,0,0
20130102,1600,35.3,35.3,35.12,35.12,961747,1,0,0
20130102,1605,35.14,35.14,35.12,35.12,60500,1,0,0
20130102,1610,35.12,35.12,35.12,35.12,2717,1,0,0
20130102,1620,35.15,35.15,35.15,35.15,900,1,0,0
20130102,1650,35.12,35.12,35.1,35.1,25047,1,0,0
20130102,1655,35.1,35.12,35.1,35.12,4400,1,0,0
20130102,1710,35.12,35.12,35.12,35.12,600,1,0,0
20130102,1720,35.07,35.07,35.07,35.07,300,1,0,0
20130102,1730,35.1,35.1,35.07,35.1,956,1,0,0
20130102,1740,35.06,35.06,35.06,35.06,125,1,0,0
20130102,1825,35.12,35.12,35.12,35.12,100,1,0,0
20130102,1955,35.04,35.04,35.04,35.04,100,1,0,0

See y'all tomorrow

I have not used Alzip. I was running WinZip but uncompressed the files with Win8 and viewed in NotePad v6.3. One could split and check ubound to see which is greater I guess and use that one. In the end though, it would be easier if M knew what his file format was and then use the code accordingly.

The translation is not uncommon. Back when I dealt with Unix files in the 80's and 90's, vbLF was used but vbCrLf was used in DOS. I ran some programs to convert back and forth.

Correct code based on your sample files:

Code:

Sub M_snb() sn = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir G:\OF\table_*.csv /b /s").stdout.readall, vbCrLf), ".") With CreateObject("scripting.filesystemobject") For j = 0 To UBound(sn) sp = Split(.opentextfile(sn(j)).readall, vbLf) For jj = 0 To UBound(sp) - 1 sp(jj) = Log(Split(sp(jj), ",")(5)) / Log(10) If jj = 0 Then y = sp(jj) Else y = y + 100 * (sp(jj) - sp(jj - 1)) ^ 2 End If Next c00 = c00 & vbLf & sn(j) & "|" & y Next End With MsgBox c00 End Sub

@ Superman,
Yeppers.
Robin

Success: Decimal Separater set to dot.
Results:

table_aapl.csv -4.8E+48

table_abbv.csv -9E+30

Code:

Option Explicit Sub SNB__KenH_SamT() Dim Filename As String Dim NameLength As Long Dim FileNameArray As Variant 'sn Dim FileLinesArray As Variant 'sp Dim Formula1LinesArray As Variant Dim Formula1Result As Double 'y 'Repeat these two lies for each Formula. Edit the numbers to suit Dim Formula2LinesArray As Variant Dim Formula2Result As Double Dim Fn As Long 'Fn = Index number for FileNameArray 'jj Dim Fl As Long 'Fl = FileLinesArray Index number 'j Const FolderPath As String = "C:\CSVs\" 'include ending \ '''' Put all the file names in the path in Array FileNameArray = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir " & _ FolderPath & "*.csv /b /s").stdout.readall, vbCrLf), ".") '''' Open one file at a time With CreateObject("scripting.filesystemobject") For Fn = 0 To UBound(FileNameArray) ''''Put all lines from one file in Arrays FileLinesArray = Split(.opentextfile(FileNameArray(Fn)).readall, vbLf) Formula1LinesArray = FileLinesArray Formula2LinesArray = FileLinesArray 'Initializ Formula1Result = 0 Formula2Result = 0 ''''Calcuate first result for one file For Fl = 0 To UBound(FileLinesArray) - 1 '''' Calculate first formula 'Replace file line with Log of 6th value. Split(BlahBlah)(5) Formula1LinesArray(Fl) = Log(Split(Formula1LinesArray(Fl), ",")(5)) 'After the first line If Fl > 0 Then Formula1Result = Formula1Result + Formula1Result + _ (Formula1LinesArray(Fl) - Formula1LinesArray(Fl - 1) ^ 2) * 100 '''' Calculate second Formula here 'Replace file line with first part of formula. Think carefully 'Formula2LinesArray (Fl) = Your formula here Next Fl '''' Put results in sheet 'Get FileName NameLength = Len(FileNameArray(Fn)) - InStrRev(FileNameArray(Fn), "\") Filename = Right(FileNameArray(Fn), NameLength) 'Place result With ActiveSheet.Rows(Fn + 1) .Columns(2) = Formula1Result 'Column B '.Columns(3) = Formula2Result .Columns(1) = Filename End With 'Initialize Arrays FileLinesArray = "" Formula1LinesArray = FileLinesArray Formula2LinesArray = FileLinesArray Next Fn 'Work on next File End With End Sub

If only a dot is a problem:

Code:

Sub M_snb() sn = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir G:\OF\table_*.csv /b /s").stdout.readall, vbCrLf), ".") With CreateObject("scripting.filesystemobject") For j = 0 To UBound(sn) sp = Split(.opentextfile(sn(j)).readall, vbLf) For jj = 0 To UBound(sp) - 1 sp(jj) = Log(Replace(Split(sp(jj), ",")(5), ".", ",")) / Log(10) If jj = 0 Then y = sp(jj) Else y = y + 100 * (sp(jj) - sp(jj - 1)) ^ 2 End If Next c00 = c00 & vbLf & sn(j) & "|" & y Next End With MsgBox c00 End Sub

Hi guys,

SamT - I always put the file path in this way (for #19): Const FolderPath As String = "C:\Users\dbrandejs\david\skola\IES\Diplomka\adjusted data\allstocks_20130102" 'include ending \

When I ran this testing code you proposed, in the message box was: Compile error: Invalid Next control variable reference

When I use your code from #53 it shows the same result as before - no error, however no output

Kenneth - I used WinZip to decompress as well, then I open the csv files in Excel

snb - I ran code according to your #52 and #54 - message box is empty in both cases

Where is the trailing backslash?

Quote:

"C:\Users\dbrandejs\david\skola\IES\Diplomka\adjusted data\allstocks_20130102" 'include ending \

Ye sorry, the file path did not copy-pasted the whole, but I use ending "\", as Sam pointed out

Code:

Option Explicit Sub SNB__KenH_SamT() Dim Filename As String Dim NameLength As Long Dim FileNameArray As Variant 'sn Dim FileLinesArray As Variant 'sp Dim Formula1LinesArray As Variant Dim Formula1Result As Double 'y 'Repeat these two lies for each Formula. Edit the numbers to suit Dim Formula2LinesArray As Variant Dim Formula2Result As Double Dim Fn As Long 'Fn = Index number for FileNameArray 'jj Dim Fl As Long 'Fl = FileLinesArray Index number 'j Const FolderPath As String = "C:\Users\dbrandejs\david\skola\IES\Diplomka\adjusted data\allstocks_20130102\" 'include ending \ '''' Put all the file names in the path in Array FileNameArray = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir " & _ FolderPath & "*.csv /b /s").stdout.readall, vbCrLf), ".") '''' Open one file at a time With CreateObject("scripting.filesystemobject") For Fn = 0 To UBound(FileNameArray) MsgBox "Working on File " & FileNameArray(Fn) ''''Put all lines from one file in Arrays FileLinesArray = Split(.opentextfile(FileNameArray(Fn)).readall, vbLf) Formula1LinesArray = FileLinesArray Formula2LinesArray = FileLinesArray 'Initializ Formula1Result = 0 Formula2Result = 0 ''''Calcuate first result for one file For Fl = 0 To UBound(FileLinesArray) - 1 MsgBox " Line #" & Fl + 1 & " is" & vbCrLf & FileLinesArray(Fl) '''' Calculate first formula 'Replace file line with Log of 6th value. Split(BlahBlah)(5) Formula1LinesArray(Fl) = Log(Split(Formula1LinesArray(Fl), ",")(5)) 'After the first line If Fl > 0 Then Formula1Result = Formula1Result + Formula1Result + _ (Formula1LinesArray(Fl) - Formula1LinesArray(Fl - 1) ^ 2) * 100 Dim MsgAnswer MsgAnswer = MsgBox("The Result is " & Formula1Result & vbCrLf & vbCrLf _ & "Press Cancel to stop run.", vbOkCancel) If MsgAnswer = vbCancel Then Exit Sub '''' Calculate second Formula here 'Replace file line with first part of formula. Think carefully 'Formula2LinesArray (Fl) = Your formula here Next Fl '''' Put results in sheet 'Get FileName NameLength = Len(FileNameArray(Fn)) - InStrRev(FileNameArray(Fn), "\") Filename = Right(FileNameArray(Fn), NameLength) 'Place result With ActiveSheet.Rows(Fn + 1) .Columns(2) = Formula1Result 'Column B '.Columns(3) = Formula2Result .Columns(1) = Filename End With 'Initialize Arrays FileLinesArray = "" Formula1LinesArray = FileLinesArray Formula2LinesArray = FileLinesArray Next Fn 'Work on next File End With End Sub

Hi Sam,

I am sorry, but it is the same as before, no error, but no output, where exactly do you get the results as you described in #53? In the new excel? New sheet?

I describe step by step how I run the code:

1) open one excel from the folder C:\Users\dbrandejs\david\skola\IES\Diplomka\adjusted data\allstocks_20130102\
2) open VBA
3) insert Module into VBAProject
4) past the code #58
5) use F5

I don't know what could be done another way..

1) open one excel from Start Menu
2) open VBA
3) insert Module into VBAProject
4) past the code #58
5) go back to excel.
Run macro from menu
Result on Excel sheet.

However. #58 has three message boxes. One for every file name. One for every file line. One for current result. It will be a very long time to run the sub thru even one folder if you must press on three mesgBox.

Run the code as above. See if you see the Message boxes. The first one will say "Working on file (filename). The next will say "Line # (n) is (shows the line). The third box will let you stop the code.

Do you see any message boxes?

I will be back in an hour or so.

You seem to be going in circles. Advice was given earlier on ways to check things.

Again, this must show True:

Code:

MsgBox Dir("C:\Users\dbrandejs\david\skola\IES\Diplomka\adjusted data\allstocks_20130102\") <> ""

1 Attachment(s)

My umpthiest contribution

NB. Never (yes, never!) use spaces in foldernames.

snb - when I open your xlsb file and push start - numbers show in following order: 99, 151, and then 9 on and on

SamT - when I change foldername to "adjusted_data", then run #58 - the first msg box says: Working on File C:\Users\dbrandejs\david\skola\IES\Diplomka\adjusted_data\allstocks_2013010 2\table_aapl.csv
the second one: Line #1 is a20130102,700,544.72,546.4,544.35,545.75,15605,1,0,0
the third one: Run-time error '13': Type mismatch

kenneth - msg box shows True

SamT - huh, I again changed the settings of dots and commas in control panel and now it seems to work, the first two msg boxes remain the same, but the third one is showing zero now,
further it goes accordingly, firstly msx box shows what is in the line and then the result..

We have already taken care of the space character issue in previous code. SNB explained it again in his code. For Sam's code you need to add those quote characters to take care of the space characters.
Of course when you do this, checks for folder or filename existence use Dir() as I demonstrated, will fail. I normally just define a string as Sam did and then add the quote characters by concatenating them via q where q="""".

Code:

Const FolderPath As String = """C:\Users\dbrandejs\david\skola\IES\Diplomka\adjusted data\allstocks_20130102\""" 'include ending \

This is really something that you should be addressing. Use the debugging methods that I detailed in some past post here. We can only do so much for you.

We did some error checks but there are others that can be done.

Code:

Sub ken() Dim FolderPath As String, q As String, fn As String, Msg As String On Error GoTo ErrMsg q = """" FolderPath = "C:\Users\dbrandejs\david\skola\IES\Diplomka\adjusted data\allstocks_20130102\" 'include ending \ fn = q & FolderPath & q MsgBox FolderPath & vbLf & "Exists? " & (Dir(FolderPath, vbDirectory) <> "") 'Next line will error: MsgBox fn & vbLf & "Exists? " & (Dir(fn, vbDirectory) <> "") 'Exit Sub ErrMsg: If Err.Number <> 0 Then Msg = "Error # " & Str(Err.Number) & " was generated by " _ & Err.Source & Chr(13) & Err.Description MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext Exit Sub End If End Sub

1 Attachment(s)

Hi it means:

- 100 files have been found
- a file consists of 152 lines
- a line consists of 10 'fields'

So the macro in the attachment will perform it's duty without any msgbox interruption.

OK so now when it is clarified, I tried to run the code from #19 according to SamT's post #44, I deleted "For testing only" part from the code in #19 and finally I get some output - in column D there is a name of the file and in columns B and C there are numbers, problem is that all the numbers are zeros - Sam did you make any corrections to code #19? As you wrote that you got non-zero result in #44

Start over.

Read the entire thread for bottom to top. Yes, read #67 first, then #66, 65, 64, etc.

Hi guys,

I made some adjustments in the settings of my pc and code #53 works for me exactly in the way as Sam describes. thank you very much!!
table_aapl.csv -4.8E+48
table_abbv.csv -9E+30

The only remainig problem is, that the calculation included is not exactly the same as I described, I need a slightly different equation, it seems to me that in #23 there is an equation: [Log(F1) - Log(F1-1)^2)] * 100, but I rather need:
Y_i = {[Log (X) - Log (X-1)] * 100 }^2 and then the summary of all Y for i=1,...,I; if I itemize it:

1)equation in column K: K= log (F)
2)equation since column L2: L2=[100*(K2-K1)]^2, this equation is made accordingly in the whole column L, except cell L1, i.e. L3=[100*(K3-K2)]^2; L4=[100*(K4-K3)]^2;...
3)sum of the whole column L, and this result (one number) should get into column B according to code #53

I tried to calculate manually table_aapl.csv and the result is 0,504763141272035, thus it should be the result generated in the column B

Quote:

The only remainig problem is, that the calculation included is not exactly the same as I described, I need a slightly different equation, it seems to me that in #23 there is an equation: [Log(F1) - Log(F1-1)^2)] * 100, but I rather need:
Y_i = {[Log (X) - Log (X-1)] * 100 }^2 and then the summary of all Y for i=1,...,I; if I itemize it:

1)equation in column K: K= log (F)
2)equation since column L2: L2=[100*(K2-K1)]^2, this equation is made accordingly in the whole column L, except cell L1, i.e. L3=[100*(K3-K2)]^2; L4=[100*(K4-K3)]^2;...

Do you mean
Y_i = {[Log (F) - Log (F-1)] * 100 }^2

Maybe this

Code:

'Replace file line with Log of 6th value. Split(BlahBlah)(5) Formula1LinesArray(Fl) = Log(Split(Formula1LinesArray(Fl), ",")(5)) 'Column F 'After the first line... Formula1Result = Sum If Fl > 0 Then Formula1Result = Formula1Result + _ (Formula1LinesArray(Fl) - Formula1LinesArray(Fl - 1) * 100 ) ^2 'Column L

1 Attachment(s)

Hi Sam,

yes I mean that: Y_i = {[Log (F) - Log (F-1)] * 100 }^2, but it means to add one parenthesis in the code:

((Formula1LinesArray(Fl) - Formula1LinesArray(Fl - 1)) * 100) ^ 2 'Column L

however even this code gives a wrong result 2.67620274495088, I attach the testing csv file, where I carried out the manual calculation with the right result 0.504763141272035

is the calculation in the code right according to my manual calculation?

I rewrote the procedure long form so that you can see the steps in the calculations and adjust the formula as needed. It will take a few seconds longer to run.

Code:

Option Explicit Sub SamT_2() Dim Filename As String Dim NameLength As Long Dim FileNameArray As Variant 'sn Dim FileLinesArray As Variant 'sp Const ColumnF As Long = 5 'CSV field number counting from zero Dim Formula1Result As Double 'y Dim ColumnKArray As Variant Dim ColumnLArray As Variant Dim Fn As Long 'Fn = Index number for FileNameArray 'jj Dim CR As Long 'CR = Column Arrays Index number. Same As Column Row number 'j Const FolderPath As String = "C:\Users\dbrandejs\david\skola\IES\Diplomka\adjusted data\allstocks_20130102\" 'include ending \ '''' Put all the file names in the path in Array FileNameArray = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir " & _ FolderPath & "*.csv /b /s").stdout.readall, vbCrLf), ".") '''' Open one file at a time With CreateObject("scripting.filesystemobject") For Fn = 0 To UBound(FileNameArray) 'Initializ Formula1Result = 0 ''''Put all lines from one file in Arrays FileLinesArray = Split(.opentextfile(FileNameArray(Fn)).readall, vbLf) ReDim ColumnKArray(UBound(FileLinesArray)) ReDim ColumnLArray(UBound(FileLinesArray) + 1) ''''Log(F) into Column K For CR = 0 To UBound(FileLinesArray) ColumnKArray(CR) = Log(Split(FileLinesArray(CR), ",")(ColumnF)) Next CR ''''Calculate Formula on Column K, put in Column L For CR = 0 To UBound(ColumnKArray) - 1 '-1 to compensate for column formula offsets ColumnLArray(CR + 1) = (100 * (ColumnKArray(CR + 1) - ColumnKArray(CR))) ^ 2 Next CR ''''Sum of Column L For CR = 1 To UBound(ColumnKArray) FormulaResult = FormulaResult + ColumnLArray(CR) Next CR '''' Put results in sheet 'Get FileName NameLength = Len(FileNameArray(Fn)) - InStrRev(FileNameArray(Fn), "\") Filename = Right(FileNameArray(Fn), NameLength) 'Place result With ActiveSheet.Rows(Fn + 1) .Columns(1) = Filename .Columns(2) = Formula1Result End With Next Fn 'Work on next File End With End Sub

Hi Sam, I ran your code, but the error shows up: "Compile error: Variable not defined" and marks Ln51, Col 39; I though that it could be a typo and changed Ln59 in this way:
Formula1Result = Formula1Result + ColumnLArray(CR), but then this shows up: "Run-time error '9': Subscript out of range"

Change all of FormulaResult to Formula1Result.

Hi Kenneth, yes I did that, I changed it only in Ln59 as only in this line is FormulaResult, but as I write in #75 then the error shows up: "Run-time error '9': Subscript out of range"

it is
For CR = 1 To UBound(ColumnKArray)

IT needs to be
For CR = 1 To UBound(ColumnLArray)

My bad.

Code:

Option Explicit Sub SamT_3() Dim Filename As String Dim NameLength As Long Dim FileNames As Variant Dim FileLines As Variant Const F As Long = 5 'CSV field number counting from zero Dim K_Array As Variant Dim L_Array As Variant Dim Sum_L As Double Dim Fn As Long 'Fn = Index number for FileNames Dim CR As Long 'CR = Column Arrays Index number. Same As Column Row number 'j Const FolderPath As String = "C:\Users\dbrandejs\david\skola\IES\Diplomka\adjusted data\allstocks_20130102\" 'include ending \ '''' Put all the file names in the path in Array FileNames = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir " & _ FolderPath & "*.csv /b /s").stdout.readall, vbCrLf), ".") '''' Open one file at a time With CreateObject("scripting.filesystemobject") For Fn = 0 To UBound(FileNames) 'Initializ L_Array = 0 ''''Put all lines from one file in Arrays FileLines = Split(.opentextfile(FileNames(Fn)).readall, vbLf) ReDim K_Array(UBound(FileLines)) ReDim L_Array(UBound(FileLines) + 1) ''''Log(F) into Column K For CR = 0 To UBound(FileLines) K_Array(CR) = Log(Split(FileLines(CR), ",")(F)) Next CR ''''Calculate Formula on Column K, put in Column L For CR = 0 To UBound(K_Array) - 1 '-1 to compensate for column formula offsets L_Array(CR + 1) = (100 * (K_Array(CR + 1) - K_Array(CR))) ^ 2 Next CR ''''Sum of Column L For CR = 1 To UBound(K_Array) Sum_L = Sum_L + L_Array(CR) Next CR '''' Put results in sheet 'Get FileName NameLength = Len(FileNames(Fn)) - InStrRev(FileNames(Fn), "\") Filename = Right(FileNames(Fn), NameLength) 'Place result With ActiveSheet.Rows(Fn + 1) .Columns(1) = Filename .Columns(2) = Sum_L End With Next Fn 'Work on next File End With End Sub

http://www.vbaexpress.com/forum/imag...lies/pray2.gif http://www.vbaexpress.com/forum/imag...lies/pray2.gif http://www.vbaexpress.com/forum/imag...lies/pray2.gif http://www.vbaexpress.com/forum/imag...lies/pray2.gif http://www.vbaexpress.com/forum/imag...lies/pray2.gif http://www.vbaexpress.com/forum/imag...lies/pray2.gif http://www.vbaexpress.com/forum/imag...lies/pray2.gif

Hi Sam, I copy-pasted your code from #77 and I tried both examples: "For CR = 1 To UBound(K_Array)" and "For CR = 1 To UBound(L_Array)", but in both cases it shows: "Run-time error '9': Subscript out of range"

After some investigation, I found that at least some CSV files have extra LineFeeds at the end. Trying to get the Log of the 6th element of "" caused the problem.

Check these results manually and see if this Sub is working now.

table_aapl.csv 2.6762027449490400

table_abbv.csv 16.3658370318833000

Also, Read and understand the formula in this code. It is still giving the same wrong result. But this is the formula you gave us.

BTW, In Table_appl_Test.csv, the value separator is a semicolon. This code only works with a comma separator. Don't be changing things on us without telling us.

Code:

Option Explicit Sub SamT_3() Dim Filename As String Dim NameLength As Long Dim FileNames As Variant Dim FileLines As Variant Const F As Long = 5 'CSV field number counting from zero Dim K_Array As Variant Dim L_Array As Variant Dim Sum_L As Double Dim Fn As Long 'Fn = Index number for FileNames Dim CR As Long 'CR = Column Arrays Index number. Same As Column Row number 'j Const FolderPath As String = "C:\TestFolder\" 'include ending \ '<<<<<<<<<<<<<<<<<<<<<<<<< '''' Put all the file names in the path in Array FileNames = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir " & _ FolderPath & "*.csv /b /s").stdout.readall, vbCrLf), ".") '''' Open one file at a time With CreateObject("scripting.filesystemobject") For Fn = 0 To UBound(FileNames) ''''Put all lines from one file in Arrays FileLines = Split(.opentextfile(FileNames(Fn)).readall, vbLf) 'Compensate for extra vbLf's in FileLines '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Do While FileLines(UBound(FileLines)) = "" ReDim Preserve FileLines(UBound(FileLines) - 1) Loop ReDim K_Array(UBound(FileLines)) ReDim L_Array(UBound(FileLines) + 1) ''''Log(F) into Column K For CR = LBound(FileLines) To UBound(FileLines) - 1 K_Array(CR) = Log(Split(FileLines(CR), ",")(F)) Next CR ''''Calculate Formula on Column K, put in Column L For CR = 0 To UBound(K_Array) - 1 '-1 to compensate for column formula offsets L_Array(CR + 1) = (100 * (K_Array(CR + 1) - K_Array(CR))) ^ 2 Next CR ''''Sum of Column L For CR = 1 To UBound(K_Array) Sum_L = Sum_L + L_Array(CR) Next CR '''' Put results in sheet 'Get FileName NameLength = Len(FileNames(Fn)) - InStrRev(FileNames(Fn), "\") Filename = Right(FileNames(Fn), NameLength) 'Place result With ActiveSheet.Rows(Fn + 1) .Columns(1) = Filename .Columns(2) = Sum_L End With Next Fn 'Work on next File End With End Sub

Hi Sam, yes, this code works, I get the same results as you do with #79:

table_aapl.csv 397845.6

table_abbv.csv 524507.7

but I already calculated manually the correct result in #72 and it should be 0.504763 for table_aapl.csv

table_aapl.csv	2.6762027449490400
table_abbv.csv	16.3658370318833000