PDA

View Full Version : Process All CSV Files In SubFolders



maxxino
05-20-2015, 04:54 PM
Hi,

I have a dataset consisting of more than 50k .csv files, all files are in the same format now and I need to perform some procedures on all of these files (text to columns, basic calculation - summary of certain columns, etc.). I googled some codes, but none worked for me, could you pls help me? I have Office 2013.

Thanks in advance.

M

SamT
05-20-2015, 08:05 PM
I have a dataset consisting of more than 50k .csv files, all files are in the same format now and I need to perform some procedures on all of these files (text to columns, basic calculation - summary of certain columns, etc.). I googled some codes, but none worked for me, could you pls help me?

Maxxino,

Let me be the first to welcome you to VBA Express, IMO, the best site for help on all Microsoft Office Questions.

Unfortunately, you did not provide enough information for us to get started. Can youGive a 5 line sample of real data from one of the CSV files?
Then add another sheet to the (xlsm) workbook to show what you need as a summary.

Be sure to use the actual data in the five rows on the CSV sheet. Make the example complete, that is use all the formulas needed, although we might be using VBA code to accomplish the same things.

With only 5 rows used, there will be plenty of room for notes so we understand exactly what is needed.

maxxino
05-22-2015, 04:49 PM
13483

Hi SamT,

thank you for the answer, I attach the excel file with the 5 line sample, another sheet and an explanation of the calculation in the sheet "adjusted",
I will be very grateful for any kind of help you can provide.

Kind regards,

M

SamT
05-22-2015, 06:23 PM
Maxino,

What do we do with the Adjusted file after we put the (N2) number in the new file (B1, B2, B3 Etc)

Can you use A VBA that only calculated N2, (according to all the formulas in K, L, and M,) and put the results in column B?

SamT

maxxino
05-23-2015, 01:57 AM
Hi,

I will run another calculation (slightly different) on the basis of the code provided by you (hopefuly) and the results will go into the new file C1, C2, C3, etc., then I will
just make some basic calculations between columns B and C directly in the new file.

I am not sure whether I understand correctly the second question, do you want to know whether the calculation from the original file from N2 may go into the same original file in column B?
It will not work for me, because there is over 50k files in the whole folder (divided into 500 subfolders) and I need to extract the number from cell N2 from every single original file and put it
into the new (one) file, thus I would be able to make some calculations inside just this one new excel, but I would have information from the whole folder.

I hope I clarified it a bit, if you need to know more, do not hesitate to ask. And once again I really appreciate your help, thank you very much!

Kind regards,

M

snb
05-23-2015, 03:51 AM
This is all you need:


Sub M_snb()
sn = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir G:\OF\*.csv /b /s").stdout.readall, vbCrLf), ".")

With CreateObject("scripting.filesystemobject")
For j = 0 To UBound(sn)
y = 0
sp = Split(.opentextfile(sn(j)).readall, vbCrLf)

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
c00 = c00 & vbLf & sn(j) & "|" & y
Next
End With

MsgBox c00
End Sub

NB. Replace "G\OF\" by the foldername you want to be searched.

SamT
05-23-2015, 08:50 AM
I will run another calculation (slightly different) on the basis of the code provided by you (hopefuly) and the results will go into the new file C2, C3, etc., then I will
just make some basic calculations between columns B and C directly in the new file.

What will you do with the old file with all the formulas up to N2 inclusive?

As you see from snb's post c00 is the same value as N2, but he never put the CSV file on a spreadsheet.


I am trying to be more clear. With VBA, we can use the CSV file to compute the value you want in B2, B3, etc. We (may be,) can do the same with the value you want in C2, C3, etc. D2, D3, etc, E2, E3. Etc, etc etc. This is much faster

Do you must also have 50k Excel files with all the numbers from all the 50K CSV files? Saving and keeping the old excel file is slower. If you must keep the numbers, is it OK to keep them all on one worksheet?

snb
05-23-2015, 09:08 AM
@Sam

y is identical to [N2]

c00= filename1 | [N2]
filename2 | [N2]
filename3 | [N2], etc.

SamT
05-23-2015, 09:18 AM
:( >> :)

snb
05-23-2015, 12:14 PM
:yes & :thumb & :friends:

maxxino
05-24-2015, 02:24 PM
Well it doesn't matter so much to me, in the old files all the formulas can be deleted or can remain (would be slightly better probably).

Ye actually this is what I asked for, I want to keep the numbers (results from all 50k old files from cell N2) on one worksheet. And as I said, it would be very helpful, if the name of the particular old file could match to the particular result from N2 (the name and the number would be in the same row).

maxxino
05-24-2015, 02:36 PM
Hi snb,

well this code does not work for me, I run the code, but the message box shows up with no value. And more importantly I don't need to show the number in the message box,
I need to get the results from all the original 50k excel files from the cell N2 (for explanation see above) into the new excel file, thus in the new excel file, there will be 50k rows with 50k values(ideally with the name of the original file in the next column)

snb
05-25-2015, 02:05 AM
I could not post a 'solution' since you provided too little information to do so.
So the suggestion I posted is a suggestion you will have to adapt to your situation yourself (the name of the folder for instance).

If you are not able to put an outcome that is presented in my suggetion in a combobox into an Excel file, I'd advise not to use any VBA at all.
If you are not open to any suggestion at all, why asking a question in the first place ?

maxxino
05-25-2015, 09:06 AM
Don't get offended pls, I did not mean wrong. I just need to process data for my diploma thesis, I am not a VBA user, I just read some basics online, thus ofc I am open to any suggestion and appreciate very much any kind of help you can provide.

I changed the name of the folder, but it did not work.

If you need more information to get a solution, I will be more than happy to give them to you.

maxxino
05-30-2015, 04:43 PM
hi, pls can you reply? I tried that, but it shows the message box with no value.
And as I said, with all respect, I don't need to show the number in the message box, I need to get the results from all the original 50k excel files from the cell N2 (for explanation see above) into the new excel file, thus in the new excel file, there will be 50k rows with 50k values(ideally with the name of the original file in the next column).
Thanks in advance for any kind of help.
DB

SamT
05-30-2015, 07:21 PM
It is always fun and instructive to figure out one of snb's codes. :D

I tried to make this verbose and simple enough that eve a Czech, (home of the Tatra :thumb,) Statistical Analyst can figure it out :hi:

If it errors, please note the error statement and number for us.

Place this code in a standard Module, Edit the FolderPath Constant to fit. After you see the Message "IT Works! Stopping Sub" Then code your next formula and run it again. The Message shows after only10 files have been successfully processed. When the code is complete and runs all the way to the message, remove the "For Testing Only" section at the bottom.

To run the Sub, make sure an empty sheet is visible in Excel, then in the VBA, put the mouse cursor anywhere in the sub and press F5. BTW, Press F8 to step (run) through the code one line at a time. If you do that, you can hover the mouse over a variable to see its Value at that time.


Option Explicit



Sub SamT_snb()

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 = "The Path to your folder must go here\" 'include ending \


'''' Put all the file names in the path in Array
FileNameArray = Filter(Split(CreateObject("wscript.shell").exec(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, vbCrLf)
Formula1LinesArray = FileLinesArray
Formula2LinesArray = FileLinesArray
'Initializ
Formula1Result = 0
Formula2Result = 0
''''Calcuate first result for one file
For Fl = 0 To UBound(FileLinesArray)

'''' 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, "\")
Filename = Right(FileNameArray(Fn), NameLength)

'Place result
With ActiveSheet.Rows(Fn + 1)
.Columns(2) = Formula1Result 'Column B
.Columns(3) = Formula2Result
.Columns(4) = Filename
End With

'Initialize Arrays
FileLinesArray = ""
Formula1LinesArray = FileLinesArray
Formula2LinesArray = FileLinesArray

''''' For testing only
If Fn >= 100 Then
MsgBox "IT Works! Stopping Sub"
Exit Sub
End If
''''End Test

Next Fn 'Work on next File
End With
End Sub

Kenneth Hobs
05-30-2015, 07:32 PM
I see that Sam and I were thinking along the same lines.

M, when we see a new user's posts, we don't know their level of experience. We have to assume that they know some VBA basics. Sometimes we assume too much and some times, not enough. A MsgBox() or Debug.Print (output to Immediate Window in Visual Basic Editor, VBE), are tools we often use to debug code to show intermediate or final results. We leave it to the user to finish it up. Pressing F8 in a routine will execute one line of code at a time so you can see what is happening. Hover a cursor over a variable to see what it resolved to.

When users ask for complex solutions, it is often hard for them to understand when they often know very little VBA. As with any learning experience, we learn by small steps. Ergo, the MsgBox() and such. I do these same small steps when I am trying to solve a problem. The key to solving problems is baby steps. SNB has solved most of what you need.

Ok, now let's nail down a few snags that can happen in your problem and snb's solution. First off, always test code on backup copies of folders or files. In this case, I recommend that you copy say 3 CSV files to one folder. Change your folder path (between the double quotes) in my modified snb code in a Module and then run it.

Sub M_snb2()
sn = Filter(Split(CreateObject("wscript.shell").exec _
("cmd /c Dir ""X:\snb\maxxino\*.csv"" /b /s").stdout.readall, vbCrLf), ".")

With CreateObject("scripting.filesystemobject")
For j = 0 To UBound(sn)
y = 0
sp = Split(.opentextfile(sn(j)).readall, vbCrLf)

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
c00 = c00 & vbLf & sn(j) & vbTab & y
Next
End With

MsgBox c00
End Sub
Hopefully, it showed the expected results. You may have noticed that in the last split, that I change the semicolon to a comma. This has to do with the delimited character used. It is a case where had you attached a csv file, it would have helped. I do think that your example XLSX file was good though. Sometimes, that level of extra help to better help us help you, well, helps.

If this works, we can then go on to fully solve your problem by poking the MsbBox data into cells. The problem I fear though is the time it will take. I have few speed tricks that can help. 500,000 files and therefore 1,000,000 cells to fill is going to take a very long time to fill. Likely as not, we would want to use another approach rather than string concatenation for the final part. We can easily fill each of the two values from each file once you understand that the first part was solved.

SamT
05-30-2015, 08:11 PM
Kenneth,

I believe this is a one time use file so he can analyze a bunchaton of numbers for his thesis. That is why I wasn't worried about time. It will give him something to watch while he is cracking the books :D

He doesn't say how long each file is, but I got the impression that thwey were pretty large. I don't think, well, hardly ever, that the final Poke of each file's computations will add that large a % to the total time. :dunno.

Kenneth Hobs
05-30-2015, 08:12 PM
I had to tweak 2 lines of code in Sam's code. The filter line needed the "cmd /c Dir" and a loop needed a -1.

I must be getting sleepy as I can't see what math needs to be fixed where you commented Sam.

Anywho, here is my slight modification. M, you will notice that Sam took it to the last level to poke some values into the active sheet. The math needs to be resolved properly though. Maybe I can see more clearly tomorrow. Allergies and late night last night and being an old fogy does not help.


Sub KenH_SamT_snb2()
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 = "X:\snb\maxxino\" '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, vbCrLf)
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(4) = Filename
End With

'Initialize Arrays
FileLinesArray = ""
Formula1LinesArray = FileLinesArray
Formula2LinesArray = FileLinesArray

''''' For testing only
If Fn >= 10 Then
MsgBox "IT Works! Stopping Sub"
Exit Sub
End If
''''End Test

Next Fn 'Work on next File
End With
End Sub

SamT
05-30-2015, 08:28 PM
Thanks, Kenneth,

I spent hours analyzing snb's code. That's my excuse and I'm sticking to it.

Aussiebear
05-30-2015, 09:56 PM
Thanks, Kenneth,

I spent hours analyzing snb's code. That's my excuse and I'm sticking to it.


Yeah..... Sure you were.....


You fell asleep again didn't you

:bubblebat

SamT
05-30-2015, 10:09 PM
shaddup, you

snb
05-31-2015, 03:14 AM
@KH

Writing 10^6 results into the workbook wont' be that tedious if we use an array of that size


redim kh(10^6,0)
cells(1).resize(10^6+1)=kh

SamT
05-31-2015, 08:54 AM
Probably
ReDim kh(10^5 ,3)

What do you think of the difference in appearance between your and my versions of the same code?

snb --
SamT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

:jester:

:funnyashe

Kenneth Hobs
05-31-2015, 10:27 AM
Here is my last revision of snb's code.

I am still concerned about speed for M. I have seen this code run slow the 1st time but after that it ran fast. ADO might be another route but it often run's slow too.

You will notice that I like to use Option Explicit so I added Dim accordingly. I also like to use early bound methods for the FSO object but I know many like the late bound method so I left it with the intent not to change snb's code too much which might confuse you. I also added the concept that Sam used where the full filename was trimmed to the basename.


Option Explicit



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\*.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, vbCrLf)

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

maxxino
05-31-2015, 12:37 PM
Thanks very much to all of you guys for your effort!!

The last code by Kenneth works best for me; I get the results the way I need them - in the column A the name of the .csv file, in the column B is the calculation, the only problem is, that in the column B all results are zero, thuscI suppose there is an error in the calculation part of the VBA code.

Time is not an issue, I have these 50k files (not 500k) divided into 500 subfolders, thus I can run the code in the particular subfolder and it is done in a sec.
I can repeat the code in all subdolders by myself, but if someone could even help me how to run the code through all the subfolders, I will be even more grateful :)

SamT: each source .csv file has max 200 rows

I really cherish your contribution, it's a huge help for me as I am in a time press to finish the diploma thesis, so I though that I could donate some smaller amount of my student money to your forum as a reward, if it is possible? For your time and effort.

Kenneth Hobs
05-31-2015, 12:51 PM
No reward other than a thanks and marking a thread solved and maybe adding to a helper's reputation (lower left star) is needed.

I suspect that your CSV files are not setup as we tested. I noted that you detailed some translation of periods to commas in your XLSX file but I did not see a need for that. I have attached the XLSM and 3 CSV files that I used for testing.

snb
05-31-2015, 01:02 PM
@KH

the first time a cmd command is being run the 'command.com' has to be loaded. When loaded (the second time) it's fast as lightning.

snb
05-31-2015, 01:05 PM
Probably
ReDim kh(10^5 ,3)

What do you think of the difference in appearance between your and my versions of the same code?

snb --
SamT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

:jester:

:funnyashe


What difference ?:rotlaugh:

SamT
05-31-2015, 01:15 PM
snb's code and Kenneth's last code take the Log of Column F and divides it by Log(-10)

My code (#16 (http://www.vbaexpress.com/forum/showthread.php?52649-format-all-files-in-a-folder&p=325501&viewfull=1#post325501) Sub SamT_snb) and Kenneth's corrected version of that (#19 (http://www.vbaexpress.com/forum/showthread.php?52649-format-all-files-in-a-folder&p=325504&viewfull=1#post325504) Sub KenH_SamT_snb) doesn't divide by Log(-10) and puts the formula result(s) in columns B & C and puts the name in Column D. You can easily edit the locations at the bottom of the code in Ken's version.

There is a space in Sub KenH_SamT_snb to add more formulas to be run on those files at the same time.

Point the code at the main folder and it will process all files in all the sub folders.

Please Private Message the owner of the Forum, Link: Jacob Hilderbrand (http://www.vbaexpress.com/forum/member.php?135-Jacob-Hilderbrand), about contributing.

snb
05-31-2015, 02:02 PM
The /s will take care that all subfolders will be looked into too. So there's nothing more to add to the code to involve all subfolders.

SamT
05-31-2015, 02:36 PM
I'll edit my poast. Toast? Poasted eggs? Post Toasted

maxxino
05-31-2015, 05:05 PM
Kenneth

I believe that my csv files are setup as I described, the replace of dot with comma is for columns C, D, E and F - these columns are prices and they have a dot to separate integer from decimals (but I want to replace dot with commas for calculation purposes)
Maybe the problem could be that I did not describe properly that text to columns should be done via comma - thus period are in column A and B (not included in the further calculation)

and as SamT noted - I do not want to divide log of Column F by Log(-10), the sequence of calculation is as I wrote in the sample

maxxino
05-31-2015, 05:23 PM
SamT

I tried your code (nr. 16 Sub SamT_snb) and Kenneth's corrected version of that (nr. 19 Sub KenH_SamT_snb) and actually it is more understandable for me as it is more itemized,
but unfortunately these codes did not work for me:

#16 gives me an error: Run-time error'-2147024894 (80070002)': The system cannot find the file specified
eventhough I put the file path into the line 22

and #19 runs without any error, but there is no output - no message "IT Works! Stopping Sub"

SamT
05-31-2015, 05:34 PM
Yes, ignore #16. It has errors built in. # 19 does not have those errors, but may have others.

in this part

If Fn >= 10 Then
MsgBox "IT Works! Stopping Sub"
Exit Sub
End If

Change Fn >= 10 to Fn >= 1

This will stop the code at the end of the first file. it should print some thing in the first row on the worksheet.

As each of your CSV files are ~200 lines. Please attach two here so we can have a meaningful test on our computers. You can zip them into one if you please.

So not despair. This is typical of programming. it is just slower with remote programming.

SamT
05-31-2015, 05:41 PM
After we get the code to work, and your sheet is filled with numbers using dots as decimal separaters, select the column(s) and press Ctrl+H. in that dialog's "Find What" box, type a dot. In the "Replace With" Box, type a comma. The find and Replace Dialog will change them all in a short time.

maxxino
05-31-2015, 06:04 PM
13571

It did not show anything either =/

I attach two csv files.

maxxino
05-31-2015, 06:09 PM
ye, I know Ctrl+H, but I wanted to include this Find&Replace into the code, as the calculation can be made only with commas, thus it somehow has to be in the code or I am not sure what you mean :)

Kenneth Hobs
05-31-2015, 06:27 PM
Open one of your CSV files in Notepad and you will see the problem. It is essentially a one record CSV file. If this is truly the case, more code is needed to split the file into the proper number of columns with added vbCrLf characters to demarcate the "records".

SamT
05-31-2015, 06:37 PM
I wanted to include this Find&Replace into the code, as the calculation can be made only with commas

Ah Ha! Before we do any more work here, will you, on your computer, go the the Start Menu, Settings, ControlPanel, Regional and Language Settings, Regional Options Tab, Customize button, and change the decimal symbol on your computer to a dot, then run the #19 code?
Please.

maxxino
05-31-2015, 06:43 PM
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

SamT
05-31-2015, 06:45 PM
Kenneth,

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

maxxino
05-31-2015, 06:53 PM
SamT

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

SamT
05-31-2015, 07:02 PM
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 :)

SamT
05-31-2015, 07:09 PM
Show us what you have in this line of code
From Code # 16

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

Const FolderPath As String = "X:\snb\maxxino\" 'include ending \
From my Workbook, where it ran fine


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

Kenneth Hobs
05-31-2015, 07:34 PM
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.

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:

Application.DecimalSeparator

SamT
05-31-2015, 07:35 PM
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.


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

SamT
05-31-2015, 07:57 PM
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 (http://www.altools.com/Downloads.aspx) is free.

:dunno :dunno :dunno

SamT
05-31-2015, 08:02 PM
Here is the contents of one of those files


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

SamT
05-31-2015, 08:15 PM
See y'all tomorrow

Kenneth Hobs
05-31-2015, 08:25 PM
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.

snb
06-01-2015, 12:11 AM
Correct code based on your sample files:


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

SamT
06-01-2015, 06:44 AM
@ Superman,
Yeppers.
Robin

Success: Decimal Separater set to dot.
Results:


table_aapl.csv
-4.8E+48


table_abbv.csv
-9E+30





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

snb
06-01-2015, 06:51 AM
If only a dot is a problem:


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

maxxino
06-01-2015, 04:42 PM
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

Kenneth Hobs
06-01-2015, 05:02 PM
Where is the trailing backslash?

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

maxxino
06-01-2015, 05:14 PM
Ye sorry, the file path did not copy-pasted the whole, but I use ending "\", as Sam pointed out

SamT
06-02-2015, 06:44 AM
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

maxxino
06-02-2015, 07:44 AM
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?

maxxino
06-02-2015, 07:50 AM
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..

SamT
06-02-2015, 08:10 AM
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.

Kenneth Hobs
06-02-2015, 08:11 AM
You seem to be going in circles. Advice was given earlier on ways to check things.

Again, this must show True:

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

snb
06-02-2015, 08:44 AM
My umpthiest contribution


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

maxxino
06-02-2015, 11:50 AM
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

maxxino
06-02-2015, 11:58 AM
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..

Kenneth Hobs
06-02-2015, 12:23 PM
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="""".


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.

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

snb
06-02-2015, 12:46 PM
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.

maxxino
06-02-2015, 01:56 PM
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

SamT
06-02-2015, 02:00 PM
Start over.

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

maxxino
06-08-2015, 08:51 AM
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

SamT
06-08-2015, 10:51 AM
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

'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

maxxino
06-08-2015, 03:34 PM
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?

SamT
06-08-2015, 07:37 PM
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.


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

maxxino
06-09-2015, 08:20 AM
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"

Kenneth Hobs
06-09-2015, 09:19 AM
Change all of FormulaResult to Formula1Result.

maxxino
06-09-2015, 12:57 PM
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"

SamT
06-09-2015, 04:02 PM
it is
For CR = 1 To UBound(ColumnKArray)

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

My bad.



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/images/smilies/pray2.gifhttp://www.vbaexpress.com/forum/images/smilies/pray2.gifhttp://www.vbaexpress.com/forum/images/smilies/pray2.gifhttp://www.vbaexpress.com/forum/images/smilies/pray2.gifhttp://www.vbaexpress.com/forum/images/smilies/pray2.gifhttp://www.vbaexpress.com/forum/images/smilies/pray2.gifhttp://www.vbaexpress.com/forum/images/smilies/pray2.gif

maxxino
06-10-2015, 08:38 AM
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"

SamT
06-11-2015, 11:52 AM
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.


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

maxxino
06-11-2015, 02:01 PM
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

SamT
06-11-2015, 03:45 PM
Excel Log Function is Log(Base10)
VBA Log Function is Natural Log

Change this line as shown

K_Array(CR) = Log(Split(FileLines(CR), ",")(F)) / Log(10#)

New result =


table_aapl_test.csv
0.50476314127199400

SamT
06-11-2015, 03:58 PM
What I learned:
Filter cannot return a value by location in an empty array. It raises a "Run-time error '9': Subscript out of range"

Math functions in VBA are not the same as the same function in Excel!

SamT
06-11-2015, 04:10 PM
The final (Oh, I so hope,) product:

Option Explicit



Sub SamT_4()
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 = 0 To UBound(FileLines)
K_Array(CR) = Log(Split(FileLines(CR), ",")(F)) / Log(10#) '/Log(10) to match Excel Log Function
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(L_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

maxxino
06-12-2015, 01:20 AM
Hi Sam, it is weird, this code works and the calculation in the first row (for table_aapl.csv) is correct, however to confirm the correctness of the calculation in the code I tried manually calculate next two files (table_abbv.csv and table_abt.csv) and they give me different results than the code:


table_abbv.csv
3.086788296


table_abt.csv
4.311690684



manual calculation:
table_abbv_test.xlsx 2.582025
table_abt_test.xlsx 1.224902

I send the xlsx files so you may see the equation I used.

1367013671

snb
06-12-2015, 01:33 AM
What I learned:
Filter cannot return a value by location in an empty array. It raises a "Run-time error '9': Subscript out of range"

Math functions in VBA are not the same as the same function in Excel!


For instance

=Mod(12.34,5)

and

msgbox 12.34 mod 5

Or in strings

=trim("ad nmj kl p uw")
and
msgbox trim "ad nmj kl p uw"


NB. In post #6 in this thread you may find:

sp(jj) = Log(Split(sp(jj), ";")(5)) / Log(10)

SamT
06-12-2015, 04:21 AM
Send me the original CSV files.. Don't Zip them, just change the Extensions to "txt"

maxxino
06-12-2015, 06:08 AM
Send me the original CSV files.. Don't Zip them, just change the Extensions to "txt"

I changed the extension to txt and tried to upload it, but "Invalid file" occured, thus I upload it via czech data share website:

http://ulozto.cz/x9ifNzX8/table-aapl-txt
http://ulozto.cz/xdejuKhn/table-abbv-txt
http://ulozto.cz/xHjaJgZv/table-abt-txt

just pres "Sthánout", it means download.

If it helps you, I can share the whole dataset via ftp (cca 220 MB).

SamT
06-12-2015, 06:21 AM
I have the files. Check back in a couple of hours. Maybe as much as 8 hours. I am busy today.

Kenneth Hobs
06-12-2015, 06:24 AM
Those can not be downloaded by some. Zip your file(s) and then attach.

SamT
06-17-2015, 10:01 AM
With Sheets("Sheet1").Rows(Fn + 1)
.Columns(1) = Filename
.Columns(2) = Sum_L
Sum_L = 0 '<<<<<<<<<<<<<<<Add this Line
End With

Here is how I found it. Left no possibilities to chance! First I performs all calculations by Sheet Formulas, then ran the code until I got a wrong value. Of course it was at the very end. A stupid omission. :banghead::banghead::banghead:


Option Explicit

Sub SamT_4()
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 F_Array() As Double
Dim K_Array() As Double
Dim L_Array() As Double
Dim Sum_L As Double
Dim FName As String
Dim X


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)
FName = Mid(FileNames(Fn), 17, Len(FileNames(Fn)) - 20)

''''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 F_Array(UBound(FileLines))
ReDim K_Array(UBound(FileLines))
ReDim L_Array(UBound(FileLines) + 1)


''''Value F into F_Array
For CR = 0 To UBound(FileLines)
F_Array(CR) = Split(FileLines(CR), ",")(F)
X = Sheets(FName).Cells(CR + 1, 6).Value
If CDbl(F_Array(CR)) <> X Then
MsgBox FName & " Cell F" & CR + 1 & " Error."
Exit Sub
End If
Next CR

''''Log(F) into Column K
For CR = 0 To UBound(FileLines)
K_Array(CR) = Log(F_Array(CR)) / Log(10#) '/Log(10) to match Excel Log Function
X = Sheets(FName).Cells(CR + 1, 11).Value
If CDbl(K_Array(CR)) <> X Then
MsgBox FName & " Cell K" & CR + 1 & " Error."
Exit Sub
End If
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
X = Sheets(FName).Cells(CR + 2, 12).Value
If CDbl(L_Array(CR + 1)) <> X Then
MsgBox FName & " Cell K" & CR + 1 & " Error."
Exit Sub
End If
Next CR

''''Sum of Column L
For CR = 0 To UBound(L_Array)
Sum_L = Sum_L + L_Array(CR)
X = Sheets(FName).Cells(CR + 2, 13).Value
If CDbl(Sum_L) <> X Then
MsgBox FName & " Cell M" & CR + 1 & " Error" .'<<<<<<< Found Error Here
Exit Sub
End If
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)
With Sheets("Sheet1").Rows(Fn + 1)
.Columns(1) = Filename
.Columns(2) = Sum_L
Sum_L = 0 '<<<<<<<<Add this Line
End With

Next Fn 'Work on next File
End With
End Sub

maxxino
06-18-2015, 03:14 PM
Hi Sam, it works!! you are great..thank you very much!!

and finally, as I wrote at the very beginning, I need to get the result of the second equation into the column C

the second equation is similar to first one, I attach the excel file with my manual calculation and pdf file to see the equation in real (see third equation BV_t),
just notice please that the number 94 that I use in the last step of the manual calculation stays for number of rows that are used for the calculation in given csv file,
thus it will be different in every csv file (it is not a constant), I believe there is a code in VBA that generates that number

btw I am writting to Jacob about the contribution, thanks again

13730
http://ulozto.cz/x1LUbSuS/bv-calculation-pdf

SamT
06-18-2015, 04:06 PM
Sorry Max, that PDF might as well be in Czech for all I know.

I asked you a long time before if there was only one equation to use on the CSV files. Now you have another. Maybe there are more equations.

take this attachment and put in all equations you need just like I put in this one and the first one. Use all the columns you need. Yellow cells show the formulas and Green shows the final result formula and all the notes.

maxxino
06-19-2015, 01:05 PM
Hi Sam, pdf is in English ofc, I uploaded it via Czech sharing website as it was not possible to upload it via "Manage attachments"

I wrote already in #5 that there will be two equations and subsequently I will perform some calculation directly in final excel file between columns B and C, , no other equations :)

It is exactly as you describe it in the attachment in #92, I will need only these two equations and put results in columns B and C
Maybe only it would be helpful if in column D there was this number of the rows that enter into the second calculation (for table_aabv_test.xlsx it is 94), but that's all :))

SamT
06-19-2015, 01:36 PM
Maxxino

I knew the PDF was English, but it was Advanced Mathematics, which I forgot 40 years ago.

I am trying humor.

BTW, you will owe me a new Tatra, Left hand drive, 2 axles and Air-conditioned, please. :D

Paul_Hossler
06-19-2015, 05:33 PM
SamT -- you really earned your money on this one:beerchug:

SamT
06-20-2015, 11:53 AM
Maxxino,

Here ya go.

Be sure and change the FolderPath Constant.

SamT
06-21-2015, 07:02 AM
See Wiki article: Numeric precision in Microsoft Excel (https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel)

Basically, Excel is limited to an arbitrary number of significant digits. When you multiply two decimal numbers the result has as many more significant digits than the first two. Both formulas start with Logs, which are very likely to have many significant digits.

The first formula merely adds the square of the logs, so probably only gains 1 or 2 significant digits and will probably be accurate to at least 10 digits.

The second formula performs chain-multiplication, X = (((((L1*L2)*L3)*L4)*L5),,,). The result of each of those operations is rounded at an arbitrary digit location. After a hundred or so operations the cumulative error starts creeping up towards a more significant location in the result.

Contrary to the Wiki article, I did not see an increase in presision when using Variant type variables instead of Long types. YMMV

Paul_Hossler
06-21-2015, 12:09 PM
Minor observations


1. I'd put Sub Subfolder_File_Processing() in a standard module and not in the sheet module

2. If precision is important (I haven't been following this too closely), maybe a Decimal data sub-type of a Variant would work. It's a bit more work to use CDec() but might be idea




Decimal variables are stored as 96-bit (12-byte) signed integers scaled by a variable power of 10. The power of 10 scaling factor specifies the number of digits to the right of the decimal point, and ranges from 0 to 28. With a scale of 0 (no decimal places), the largest possible value is +/-79,228,162,514,264,337,593,543,950,335. With a 28 decimal places, the largest value is +/-7.9228162514264337593543950335 and the smallest, non-zero value is +/-0.0000000000000000000000000001.
Note
At this time the Decimal data type can only be used within a Variant, that is, you cannot declare a variable to be of type Decimal. You can, however, create a Variant whose subtype is Decimal using the CDec function.






Option Explicit
' Thanks to all @ http://www.vbaexpress.com/forum/showthread.php?52649-Process-All-CSV-Files-In-SubFolders/page3
Sub Subfolder_File_Processing()

Const F As Long = 5 'CSV field number counting from zero
Const FolderPath As String = "C:\TestFolder\" 'include ending \
Dim Filename As String
Dim NameLength As Long

Dim FileNames As Variant
Dim FileLines As Variant


Dim F_Array() As Variant '-----------------------
Dim Sum_L As Variant '-----------------------
Dim Q_Array() As Variant '-----------------------
Dim Sum_Q As Variant '-----------------------

Dim Pie As Double
Pie = Application.WorksheetFunction.Pi() * 1# '----------------------- why?

Dim Fn As Long 'Fn = Index number for FileNames
Dim CR As Long 'CR = FileLines Index number
Dim NumRows As Long


' 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 F_Array(UBound(FileLines))
ReDim Q_Array(UBound(FileLines))

'Initialize
NumRows = UBound(FileLines) + 1
Sum_L = CDec(0) '-----------------------
Sum_Q = CDec(0) '-----------------------

'Calcuate first result for one file
For CR = 0 To UBound(FileLines)
'Replace file line with Log of 6th value. Split(BlahBlah)(5)
F_Array(CR) = CDec(0) '-----------------------
F_Array(CR) = Log(Split(FileLines(CR), ",")(F)) / Log(10#) '-----------------------

'After the first line
If CR > 0 Then
Q_Array(CR) = CDec(0) '-----------------------
Sum_L = Sum_L + ((F_Array(CR) - F_Array(CR - 1)) * 100#) ^ 2 '-----------------------
Q_Array(CR) = Abs((F_Array(CR) - F_Array(CR - 1)) * 100#) '-----------------------
Sum_Q = Sum_Q + (Q_Array(CR) * Q_Array(CR - 1))
End If
Next CR

' Put results in sheet
'Get FileName
NameLength = Len(FileNames(Fn)) - InStrRev(FileNames(Fn), "\")
Filename = Right(FileNames(Fn), NameLength)

'Place result
With Sheets("Sheet1").Rows(Fn + 1)
.Columns(1) = Filename
.Columns(2) = Sum_L 'Column B
.Columns(3) = (Sum_Q * Pie * (94 / (94 - 1))) / 2
.Columns(4) = NumRows
End With

Next Fn 'Work on next File
End With
End Sub




I'm not sure that this would work any better (or at all)

SamT
06-21-2015, 01:59 PM
Variants are 16 bytes. I ran it with all variants, only converting to Decimal when placing the results on the sheet and without even that conversion and saws no difference.

After converting all numeric variable to Variants and using these calculations the results were identical to the 10th digit with the other methods. Note that I kept the Magic number 100. It made a difference when I subtituted the Variant C = CDec(100) for the Magic number


''''Calcuate results for one file
For CR = 0 To UBound(FileLines)
'Replace file line with Log of 6th value. Split(BlahBlah)(5)
F_Array(CR) = CDec(Log(Split(FileLines(CR), ",")(F)) / Log(10#)) '<<<<<<<<<<
'After the first line
If CR > 0 Then
Sum_L = Sum_L + ((F_Array(CR) - F_Array(CR - 1)) * 100) ^ 2
Q_Array(CR) = Abs((F_Array(CR) - F_Array(CR - 1)) * 100)
Sum_Q = Sum_Q + (Q_Array(CR) * Q_Array(CR - 1))
End If
Next CR

I believe that the VBA method is that much more precise than the Excel On-Sheet method, which is limited to 15 significant places.

I need to go back and edit my "Accuracy" post above.

With 100 to 150 lines, Excel is accurate to +-3 in the third
With With Double Type numerical variables, VBA is accurate to 11 places
Assuming that using all decimals is the most precise.




1st Formula Results



table_abbv.csv
table_abt.csv
table_aapl.csv


Number of lines
94
106
151


Excel
2.5820251543270400
1.2249023888699800
0.5047631412720350


Doubles
2.5820251543270400
1.2249023888699900
0.5047631412719940


Decimal Variables
2.5820251543248600
1.2249023888700300
0.5047631412714680









2nd Formula Results


Excel
1.7327113499938000
0.8586603953426530
0.4404276525780680


Doubles
1.7327113499937800
0.8597056301514100
0.4422153315780190


Decimal Variables
1.7327113499905900
0.8597056301520180
0.4422153315780510

SamT
06-21-2015, 02:01 PM
Most precise version to data. (IMO)

Option Explicit

Sub Fast_Subfolder_File_Processing()
'8-Byte accuracy
' Thanks to all @ http://www.vbaexpress.com/forum/showthread.php?52649-Process-All-CSV-Files-In-SubFolders/page3

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 F_Array() As Variant
Dim Sum_L As Variant
Dim Q_Array() As Variant
Dim Sum_Q As Variant

Dim C As Variant
Dim Pie As Variant
Pie = CDec(Application.WorksheetFunction.Pi())

Dim Fn As Long 'Fn = Index number for FileNames
Dim CR As Long 'CR = FileLines Index number
Dim NumRows As Long

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 F_Array(UBound(FileLines))
ReDim Q_Array(UBound(FileLines))

'Initialize
NumRows = UBound(FileLines) + 1
Sum_L = 0
Sum_Q = 0

''''Calcuate first result for one file
For CR = 0 To UBound(FileLines)
'Replace file line with Log of 6th value. Split(BlahBlah)(5)
F_Array(CR) = CDec(Log(Split(FileLines(CR), ",")(F)) / Log(10#))
'After the first line
If CR > 0 Then
Sum_L = Sum_L + ((F_Array(CR) - F_Array(CR - 1)) * 100) ^ 2
Q_Array(CR) = Abs((F_Array(CR) - F_Array(CR - 1)) * 100)
Sum_Q = Sum_Q + (Q_Array(CR) * Q_Array(CR - 1))
End If
Next CR

'''' Put results in sheet
'Get FileName
NameLength = Len(FileNames(Fn)) - InStrRev(FileNames(Fn), "\")
Filename = Right(FileNames(Fn), NameLength)


'Place result
With Sheets("Sheet1").Rows(Fn + 1)
.Columns(1) = Filename
.Columns(2) = Sum_L 'Column B
.Columns(3) = (Sum_Q * Pie * (94 / (94 - 1))) / 2
.Columns(4) = NumRows
End With

Next Fn 'Work on next File
End With
End Sub

Sub Precise_Subfolder_File_Processing()
'16-Byte accuracy
' Thanks to all @ http://www.vbaexpress.com/forum/showthread.php?52649-Process-All-CSV-Files-In-SubFolders/page3

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 F_Array() As Variant
Dim Sum_L As Variant
Dim Q_Array() As Variant
Dim Sum_Q As Variant

Dim Pie As Double
Pie = Application.WorksheetFunction.Pi() * 1

Dim Fn As Long 'Fn = Index number for FileNames
Dim CR As Long 'CR = FileLines Index number
Dim NumRows As Long

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 F_Array(UBound(FileLines))
ReDim Q_Array(UBound(FileLines))

'Initialize
NumRows = UBound(FileLines) + 1
Sum_L = 0
Sum_Q = 0

''''Calcuate first result for one file
For CR = 0 To UBound(FileLines)
'Replace file line with Log of 6th value. Split(BlahBlah)(5)
F_Array(CR) = Log(Split(FileLines(CR), ",")(F)) / Log(10#)
'After the first line
If CR > 0 Then
Sum_L = Sum_L + ((F_Array(CR) - F_Array(CR - 1)) * 100) ^ 2
Q_Array(CR) = Abs((F_Array(CR) - F_Array(CR - 1)) * 100)
Sum_Q = Sum_Q + (Q_Array(CR) * Q_Array(CR - 1))
End If
Next CR

'''' Put results in sheet
'Get FileName
NameLength = Len(FileNames(Fn)) - InStrRev(FileNames(Fn), "\")
Filename = Right(FileNames(Fn), NameLength)


'Place result
With Sheets("Sheet1").Rows(Fn + 1)
.Columns(1) = Filename
.Columns(2) = (Sum_L)
.Columns(3) = ((Sum_Q * Pie * (94 / (94 - 1))) / 2)
.Columns(4) = NumRows
End With

Next Fn 'Work on next File
End With
End Sub

snb
06-21-2015, 02:15 PM
@SamT

Hi Sam,

Can you inform me about the difference of your last code compared to the code in http://www.vbaexpress.com/forum/showthread.php?52649-Process-All-CSV-Files-In-SubFolders&p=325010&viewfull=1#post325010 ?

SamT
06-21-2015, 02:23 PM
More Reading:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=887
http://www.extremeoptimization.com/Documentation/Default.aspx
http://www.tushar-mehta.com/misc_tutorials/project_euler/LargeNumberArithmetic.htm

SamT
06-21-2015, 02:37 PM
@ snb,

?

It has one more formula and is more verbose. That is why I credited all here. I was standing on your shoulders, propped up by many others.

I am not clear yet on the difference in Precision/Accuracy between the Variant Type and the Decimal Type, but the reading I've done hints that the use of Decimal Types is preferred.

On the three files available, use of the Variant Type gave the same results as when using Doubles.

Paul_Hossler
06-21-2015, 04:46 PM
SamT --



only converting to Decimal when placing the results on the sheet and without even that conversion and saw no difference.


I would think that if all the internal calculations were done with 'low' precision Doubles, then converting that to 'higher' precision Decimal at the end would not show additional precision

I'd kind of leaning towards thinking that the Log() function will only return Double level precision, so I doubt that using Decimals will offer any improvement :crying:


As an aside, there is an add-in that allows up to 32760 digits to the right of the decimal point. I never needed that to balance my checkbook, but as an old retired math major, I found it interesting

www.thetropicalevents.com/Xnumbers60 (http://www.thetropicalevents.com/Xnumbers60)

SamT
06-21-2015, 06:22 PM
I'd kind of leaning towards thinking that the Log() function will only return Double level precision, so I doubt that using Decimals will offer any improvement

My thoughts were that it made the Array values Decimal. It doesn't make much difference in the first calculation which is just adding the squares of the difference in two numbers, but in the second calculation, which is a chain of multiplications, it seems to rally help. Of course Maxximo told me in a PM that 3 in the 3rd is good enough for him.

Like Richard Pryor in Superman, I want to balance my accounts to the hundred thousandths of a mil. :D :D :D

snb
06-22-2015, 01:17 AM
Just for reflection's sake:


Sub M_snb()
c00 = "C:\TestFolder\"
sn = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir """ & c00 & "*.csv"" /b /s").stdout.readall, vbCrLf), ".")

With CreateObject("scripting.filesystemobject")
For j = 0 To UBound(sn)
sp = Filter(Split(.opentextfile(sn(j)).readall, vbCrLf), ",")

For jj = 0 To UBound(sp)
sp(jj) = Log(Split(sp(jj), ",")(5)) / Log(10)
If j > 1 Then y = y + Abs(sp(jj - 1) - sp(jj - 2)) * Abs(sp(jj) - sn(jj - 1))
Next

sn(j) = [Pi() / 2] * y * (UBound(sp) + 1) / UBound(sn) * 10 ^ 4
Next
End With

Sheet1.Cells(1).Resize(UBound(sn) + 1) = Application.Transpose(sn)
End Sub

SamT
06-22-2015, 05:42 AM
Since I lifted your Filter-Split in toto, why doesn't that version error out at the extra carriage return? sp(Ubound(sp)) was empty in my version.

snb
06-22-2015, 06:01 AM
splittting by vbCrLf instead of by vbLf

SamT
06-22-2015, 06:20 AM
I see. But you (and I) started out using vbCrLf. What happened?

#52 (http://www.vbaexpress.com/forum/showthread.php?52649-Process-All-CSV-Files-In-SubFolders&p=325554&viewfull=1#post325554)

sp = Split(.opentextfile(sn(j)).readall, vbLf)

Bad Dog! No biscuit for you. :razz2:

But I think I will keep that Empty-Index-Stripper loop. Ya never know what some User will do.

snb
06-22-2015, 09:03 AM
@Maxxino

Check whether all subfolders are taken into account with


Sub M_snb()
c00 = "G:\OF\"

sn = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir """ & c00 & "*.csv"" /b/s").stdout.readall, vbCrLf), ".")
Cells(1).Resize(UBound(sn) + 1) = Application.Transpose(sn)
End Sub

On my system it works flawlessly

If your pathname contains spaces SamT's code will let you down.
Mine ( # 106) is more robust.



@SamT

Would 'option Explicit' have helped ? (:devil2:)
To - keep that Empty-Index-Stripper loop - is against any 'good coding practice' ;)

maxxino
06-22-2015, 12:20 PM
SamT - the accuracy has increased as I adjusted this line: .Columns(3) = ((Sum_Q * Pie * (NumRows / (NumRows - 1))) / 2)

snb - it works now, the code was in Sheet1, I moved it to Module, thx

SamT
06-23-2015, 03:30 PM
Added Sum of Column G to procedure


Sub Precise_Subfolder_File_Processing()
'16-Byte accuracy
' Thanks to all @ http://www.vbaexpress.com/forum/showthread.php?52649-Process-All-CSV-Files-In-SubFolders/page3

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
Const G As Long = 6 '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Dim F_Array() As Variant
Dim Sum_L As Variant
Dim Q_Array() As Variant
Dim Sum_Q As Variant
Dim Sum_G As Long '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Dim Pie As Double
Pie = Application.WorksheetFunction.Pi() * 1

Dim Fn As Long 'Fn = Index number for FileNames
Dim CR As Long 'CR = FileLines Index number
Dim NumRows As Long

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

Application.ScreenUpdating = False '<<<<<<<<<<<<<Added for speed. Comment out to watch process

'''' 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 F_Array(UBound(FileLines))
ReDim Q_Array(UBound(FileLines))

'Initialize
NumRows = UBound(FileLines) + 1
Sum_G = 0 '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Sum_L = 0
Sum_Q = 0

''''Calcuate first result for one file
For CR = 0 To UBound(FileLines)
'Replace file line with Log of 6th value. Split(BlahBlah)(5)
F_Array(CR) = Log(Split(FileLines(CR), ",")(F)) / Log(10#)
'After the first line
If CR > 0 Then
Sum_L = Sum_L + ((F_Array(CR) - F_Array(CR - 1)) * 100) ^ 2
Q_Array(CR) = Abs((F_Array(CR) - F_Array(CR - 1)) * 100)
Sum_Q = Sum_Q + (Q_Array(CR) * Q_Array(CR - 1))
End If
Sum_G = Sum_G + Split(FileLines(CR), ",")(G) '<<<<<<<<<<<<<<
Next CR

'''' Put results in sheet
'Get FileName
NameLength = Len(FileNames(Fn)) - InStrRev(FileNames(Fn), "\")
Filename = Right(FileNames(Fn), NameLength)


'Place result
With Sheets("Sheet1").Rows(Fn + 1)
.Columns(1) = Filename
.Columns(2) = (Sum_L)
.Columns(3) = ((Sum_Q * Pie * (NumRows / NumRows - 1))) / 2)
.Columns(4) = NumRows
.Columns(5) = Sum_G '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
End With

Next Fn 'Work on next File
End With
Application.ScreenUpdating = True '<<<< Reset to default
End Sub

maxxino
06-23-2015, 04:29 PM
If I run #112, it shows all zeros in column 3, however I took new lines (except the one added for speed) and put it into previous "fast" code and it works just fine :):clap:

Thx again and pls let me know about the bank....or I will have to send just the beers :))

SamT
06-28-2015, 04:43 PM
Here is one with only the new formulas in it. I only checked the results against the Table_abbv(3) sheet you sent. I left the filenames and the sum of Columne G in for your verification of the results. Either comment out those lines or run it on a blank sheet.

Sub Fast_Subfolder_File_Processing()
'8-Byte accuracy
' Thanks to all @ http://www.vbaexpress.com/forum/showthread.php?52649-Process-All-CSV-Files-In-SubFolders/page3

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
Const G As Long = 6

Dim F_Array() As Double
'Dim Sum_L As Double
'Dim Q_Array() As Double
'Dim Sum_Q As Double
Dim G_Array() As Double
Dim Sum_G As Double '<<<<<<<<<<<<<<<<<<<<<<<<<<<
Dim Sum_O As Double
Dim Sum_P As Double
Dim Temp_M As Double

Dim Pie As Double
Pie = Application.WorksheetFunction.Pi()

Dim Fn As Long 'Fn = Index number for FileNames
Dim CR As Long 'CR = FileLines Index number
Dim NumRows As Long

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 F_Array(UBound(FileLines))
ReDim Q_Array(UBound(FileLines))
ReDim G_Array(UBound(FileLines))

'Initialize
NumRows = UBound(FileLines) + 1
'Sum_L = 0
'Sum_Q = 0
Sum_G = 0
Sum_O = 0
Sum_P = 0 '<<<<<<<<<<<<<<<<<<<<<<<<<<<<

''''Calcuate first result for one file
For CR = 0 To UBound(FileLines)
'Replace file line with Log of 6th value. Split(BlahBlah)(5)
F_Array(CR) = Log(Split(FileLines(CR), ",")(F)) / Log(10#)
G_Array(CR) = Split(FileLines(CR), ",")(G)
'After the first line
If CR > 0 Then
If F_Array(CR) - F_Array(CR - 1) <> 0 Then
Temp_M = 100 * Abs(F_Array(CR) - F_Array(CR - 1))
Sum_O = Sum_O + Temp_M / G_Array(CR)
Sum_P = Sum_P + G_Array(CR) / Temp_M
End If
End If
Sum_G = Sum_G + G_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 Sheets("Sheet1").Rows(Fn + 1)
.Columns(1) = Filename
'.Columns(2) = Sum_L 'Column B
'.Columns(3) = (Sum_Q * Pie * (94 / (94 - 1))) / 2
'.Columns(4) = NumRows
'.Columns(5) = Sum_G
.Columns(6) = Sum_O '<<<<<<<<<<<<<<<<<<<<<<<<<<
.Columns(7) = Sum_P
End With

Next Fn 'Work on next File
End With
End Sub

Don't send money, mail me a thank you letter, On letterhead paper if appropriate.

SamT
06-28-2015, 04:50 PM
Here are the result I got on three files



table_aapl.csv


20887611
0.00061
1362412839


table_abbv.csv


13768906
0.006232
295734939.5


table_abt.csv


20283788
0.002766
3467549458

SamT
07-11-2015, 09:13 AM
Sub Get_Convar()
'16-Byte accuracy
' Thanks to all @ http://www.vbaexpress.com/forum/showthread.php?52649-Process-All-CSV-Files-In-SubFolders/page3

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 F_Array() As Variant
Dim Param_1() As Variant
Dim Param_2() As Variant

Dim Fn As Long 'Fn = Index number for FileNames
Dim CR As Long 'CR = FileLines Index number

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

Application.ScreenUpdating = False '<<<<<<<<<<<<<Added for speed. Comment out to watch process

'''' 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 F_Array(UBound(FileLines))
ReDim Param_1(UBound(FileLines) - 2)
ReDim Param_2(UBound(FileLines) - 2)

For CR = 0 To UBound(FileLines)
'Replace file line with Log of 6th value. Split(BlahBlah)(5)
F_Array(CR) = Log(Split(FileLines(CR), ",")(F)) / Log(10#)
'After the first line
If CR > 0 And CR < UBound(FileLines) Then _
Param_1(CR - 1) = (F_Array(CR) - F_Array(CR - 1)) * 100
If CR > 1 Then _
Param_2(CR - 1) = (F_Array(CR) - F_Array(CR - 1)) * 100
Next CR

'''' Put results in sheet
'Get FileName
NameLength = Len(FileNames(Fn)) - InStrRev(FileNames(Fn), "\")
Filename = Right(FileNames(Fn), NameLength)


'Place result
With Sheets("Sheet1").Rows(Fn + 1)
.Columns(1) = Filename
'.Columns(2) = (Sum_L)
'.Columns(3) = ((Sum_Q * Pie * (NumRows / NumRows - 1))) / 2)
'.Columns(4) = NumRows
'.Columns(5) = Sum_G '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
.Columns(8) = WorkdheetFunction(Covar(Param_1, Param_2))
End With

Next Fn 'Work on next File
End With
Application.ScreenUpdating = True '<<<< Reset to default
End Sub

SamT
07-11-2015, 01:38 PM
Hi Sam, thanks, just that there is a compile error: Sub or Function not defined (Covar). Could it be idk e.g. that you have a different version of Office? I have 2013.
Because I know that in previous Excels covar was a valid function, but in Excel 2013 covariance.p is its replacement (eventhough in Excel 2013 covar still works, but idk whether it is the same with VBA)
Then replace Covar with covariance.p. I can only use what you give me.

SamT
07-12-2015, 06:40 AM
WorksheetFunction.Covar(Param_1, Param_2)

SamT
07-12-2015, 09:20 AM
This works


Param_2(CR - 2) = (F_Array(CR) - F_Array(CR - 1)) * 100


.Columns(8) = WorksheetFunction.Covar(Param_1, Param_2)



table_aapl1.csv

-5.15158E-05


table_abbv.csv
-0.002501356


table_abt.csv
-0.000789329