[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Sys Test--Summary Report Question



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