|
PureBytes Links
Trading Reference Links
|
Hi,
I use automate to loop files to the system tester then save the file as
a text file in C:\Metastock\. I then run this VBA sub to open and move all
txt files in that folder, and produce an average in sheet1. If you manually
save the files, save them as the security name .txt. I tried data
consolidation in excel but could not get it to loop.
Selection.Consolidate Sources:="'C:\Metastock\[Book1.xls] VARIABLENAME
!R5C2", _
Function:=xlAverage, TopRow:=False, LeftColumn:=False, CreateLinks:=
_
False
You might need to add a few _ where the code looks like it has started a
new line while being pasted into outlook. I am a little new to VBA so any
suggestions welcome.
Murray
Sub txtToExcel()
Dim strFileFound, StrSheetName As String
Dim Filecounter, RowCount As Integer
Dim intBcellvalue(32), intDcellvalue(32) As Integer
Dim BnumericChecker, DnumericChecker As Variant
strFileFound = Dir$("c:\metastock\*.txt") ' Location of text files
Do Until strFileFound = ""
Filecounter = Filecounter + 1 ' used to divide to create average
ChDir "C:\Metastock" 'location of files
Workbooks.OpenText FileName:=strFileFound, Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1)
Columns("A:A").ColumnWidth = 15
Columns("A:A").ColumnWidth = 17
Columns("C:C").ColumnWidth = 13
Columns("C:C").ColumnWidth = 19
Columns("C:C").ColumnWidth = 22
Range("D9").Select
Selection.NumberFormat = "mm/dd/yy"
ActiveSheet.Move Before:=Workbooks("Book1.xls").Sheets(1)
ActiveSheet.Select
StrSheetName = Left(strFileFound, 8)
ActiveSheet.Name = StrSheetName
For RowCount = 5 To 32
If RowCount <> 8 And RowCount <> 10 And RowCount <> 13 And RowCount <> 18
And RowCount <> 26 And RowCount <> 29 Then
BnumericChecker = Worksheets(StrSheetName).Cells(RowCount, 2).Value
DnumericChecker = Worksheets(StrSheetName).Cells(RowCount, 4).Value
If IsNumeric(BnumericChecker) Then intBcellvalue(RowCount) =
intBcellvalue(RowCount) + Val(Format(BnumericChecker, "#.00"))
If IsNumeric(DnumericChecker) Then intDcellvalue(RowCount) =
intDcellvalue(RowCount) + Val(Format(DnumericChecker, "#.00"))
End If
Next RowCount
strFileFound = Dir
Loop
Sheets("Sheet1").Select
For RowCount = 5 To 32
If RowCount <> 8 And RowCount <> 10 And RowCount <> 13 And RowCount <> 18
And RowCount <> 26 And RowCount <> 29 Then
Worksheets("sheet1").Cells(RowCount, 2).Value =
Format(intBcellvalue(RowCount) / Filecounter, "#.00") 'average the total by
the amount of files opened
Worksheets("sheet1").Cells(RowCount, 4).Value =
Format(intDcellvalue(RowCount) / Filecounter, "#.00") 'average the total by
the amount of files opened
End If
Next RowCount
Close
End Sub
----- Original Message -----
From: Yngvi Hardarson <hardy@xxxxxxxxxxxxx>
To: <metastock@xxxxxxxxxxxxx>
Sent: Sunday, 4 June 2000 23:04
Subject: RE: Sys Test--Summary Report Question
> Dave!
>
> My experience shows that when working with two MAs like that a ratio of 3
> between the length of the two pops up amazingly often!
>
> I like 3/10, 7/21, 11/33 and 13/39 to mention a few.
>
> Best regards,
> Yngvi Hardarson
> hardy@xxxxxxxxxxxxx
>
> -----Original Message-----
> From: owner-metastock@xxxxxxxxxxxxx
[mailto:owner-metastock@xxxxxxxxxxxxx]On
> Behalf Of Dave Nadeau
> Sent: 3. juni 2000 20:15
> To: metastock@xxxxxxxxxxxxx
> Subject: Re: Sys Test--Summary Report Question
>
> Walter and Larry,
>
> Thanks to you both for the information.
>
> These are the sorts of investigations that I want to do. Getting the
> results
> out of the system tester summary was my first step. Thanks again. Also,
if
> you
> have a handy example of a VBA sub to open each, or you can point me to
some
> resources to improve/brush up my Visual Basic skills, I'd really
appreciate
> it.
> It looks like I'll probably really benefit from using it to help manage
the
> analysis.
>
> Thanks for the suggestions!
>
> Dave Nadeau
>
> Walter Lake wrote:
>
> > Hi Dave
> >
> > In System Tester --> Reports --> Reports ... Click the "Trades" tab -->
> > Right click ---> save to file
> >
> > Open into Excel. If more than one file, use a looping VBA sub to open
each
> > text file as a separate sheet in your workbook. Use "!" in your formulas
> or
> > VBA code to link it to a data or results sheet.
> >
> > "... can evaluate histograms for harmonics or other repeating patterns.
> ..."
> >
> > Do you mean histogram type indicators or data analysis histograms? Could
> you
> > say a little more about your project or post some code to explain what
you
> > mean.
> >
> > Best regards
> >
> > Walter
> >
> > ----- Original Message -----
> > From: "Dave Nadeau" <dave.nadeau@xxxxxxxx>
> > To: "MetaStock Listserver" <metastock@xxxxxxxxxxxxx>
> > Sent: Wednesday, May 31, 2000 11:58 AM
> > Subject: Sys Test--Summary Report Question
> >
> > | This is probably repetitious because I think I remember a
> > | recent posting describing the answer to this...my apologies
> > | if this is so.
> > |
> > | Does anyone know how to export the results of a system test,
> > | as shown in the summary report dialog box? I've tried
> > | printing to a file without success in being able to read the
> > | results. Ultimately, I would like to get this into MS Excel
> > | so that I can evaluate histograms for harmonics or other
> > | repeating patterns.
> > |
> > | Dave Nadeau
> > |
>
|