>>Thanks. My notes are embedded below:
What, in words, is the code meant to do?
>>Summary:
Its a trading analytics tools. Basically I find pattners in the market and build trading machines based on it. (I have an MS in Finance/Investments and I code as necessary.)
Here are the basic steps:
1)Find all combinations
2)Find all permuations of all combinations
3)Activate correct file with desired values using permuations as indices
4)Do a comparison of the values
5)Record the observation if it was intersting
6)Repeat
Here are the details ...For the code I posted (the entire program description would take a book to explain):
The first Do While Loop goes through every combination of a set of 200 values 1 through 200 choosing 1 then 2 values and so on to 5 values (my PowerSet5 function). So:
One digit:
1
2
3... to 200
then two digits:
1 2
1 3
1 4...to 199 200
then 3 digits
1 2 3
1 2 4
1 2 5....to 198 199 200
...on up to five digits where the first 5 digit combination would be 1 2 3 4 5 and the last combination would be 196 197 198 199 200
The the next loop does permuations of each of these combinations:
The first permutation would just be 1 and the last permutation would be 200 199 198 197 196.
For example a 3 digit combination of 1 2 3 would have these permutations:
1 2 3
1 2 4
1 2 5...to 3 2 1
There permutations are all done at the bottom of the second loop show above using:
Code:
bStopPermuting = PermuteIndices(vaIndexPerm)
The "For i = 1 To iUpperBoundIndexComboArray" loop:
The permutations of combinations are actually idices for referencing different string file names in a array of strings called straIndyArray. I then use those file names to open files with the values I want. The use of the permuted indices to find the file name and open up a file with the proper values on it is done on this line:
Code:
Workbooks(straIndyArray(vaIndexPerm(i))).Activate
With the proper file active (leave the 200 files open to save time), I reference certain cell values for comparison on these individual files. I only show one comparison function in my code (to keep it simple):
Code:
isgreaternowArray = greaternow(i, greater_c, greater_s, comptoprange, seriestoprange)
Then after the "For i = 1 To iUpperBoundIndexComboArray" loop, if I found something of interest, I build a label (for reference in a different part of the program not discussed here) and check to see if I already found this 'thing of interest' and record the label if I did not already observe it and record it before. This is label building, checking and recording is done in:
Code:
strTestLabel = BuildTestLabel(straIndyArray, vaIndexPerm, "GtrNw", bb, compmoverow)
result = RepCheck_RecordTestLabelToPriceFile(strTestLabel)
Then, as mentioned above in the "second loop" I find the next permuation of the combination of Indy's via:
Code:
bStopPermuting = PermuteIndices(vaIndexPerm)
And at the end of the outter most loop, the "first do while" discussed in the beginning of this post, I find the next permutation of the indices of the combinations that were found:
Code:
bStopPermuting = PermuteIndices(vaIndexPerm)
Oh and at the bottom of the outter most loop I update the status bar every 10 minutes...just to be sure its all working still....may take out once I am all finished.
The first thing is to use Long, not Integer.
>>Everything that is a Long must be a Long (unless I missed one or two). I've checked this already.
The second is don't declare variables in the middle of the code, especially within loops.
>>I've put a timer on the Dim's and it makes zero time difference. Personally I like the Dim's as close to where I use the variable as possible for reference etc. I know you VBA guys like your at the top...sorry that would drive me crazy.
Here is an example how I timed it (I found this online somewhere):
Code:
Sub foo()
Dim Ctr1 As Currency, Ctr2 As Currency, Freq As Currency
Dim Overhead As Currency
Dim i As Long
QueryPerformanceFrequency Freq
QueryPerformanceCounter Ctr1
QueryPerformanceCounter Ctr2
Overhead = Ctr2 - Ctr1 ' determine API overhead
QueryPerformanceCounter Ctr1 ' time loop
For i = 1 To 100000
Dim my_something as Long 'time it with and without this line
Next
QueryPerformanceCounter Ctr2
Debug.Print (Ctr2 - Ctr1 - Overhead) / Freq
End Sub
Oh, and I hope you don't mind I indented the code to make it a little easier to understand, ish.
>>No problem at all, thanks...
And you don't need to initialize your boolean variables - there default value is False as far as I know.
>>I think every place I do this it needs to be done since I need to reset them given some contraints. But I'll take another look at that. Thanks!
Any and all suggests are welcome!
