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

Re: Sys Test--Summary Report Question



PureBytes Links

Trading Reference Links

Hi Walter,
     Hope this helps , hard to effectively comment in Outlook.
     I started off trying to get the results of  system tests into excel
with one click of a button.  In 6.52 I saved text files of the system test
reports. Then identify the text files in excel with the loop.

strFileFound = Dir$("c:\metastock\*.txt")
Do Until strFileFound = ""
'do what you like
strFileFound = Dir
loop


open them as they are looped

 Move the current file to the   Book1.xls.

loop the cell values to 2 arrays as each txt file opens , 'I see i may of
used isnumeric and val unnecessarily '
Then divide by the number of files opened to get the average. ,Then populate
sheet1 with the average values of all systems reports. I can see how it
could be hard to follow .Probably statistically meaningless but I think vba
is going to be very useful. Any suggestions ??


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 = "" 'loop all .txt files
> |
> |   Filecounter = Filecounter + 1 ' used to divide to create average
> |
> |    ChDir "C:\Metastock" 'location of files
> |   Workbooks.OpenText FileName:=strFileFound, Origin:= _      " open
files
> |        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 'set column widths
> |     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" ' format date so it fits in
column
> |
> |    ActiveSheet.Move Before:=Workbooks("Book1.xls").Sheets(1)  'move
sheet to workbook
> |
> |    ActiveSheet.Select
> |    StrSheetName = Left(strFileFound, 8)  ' reduce the size of the file
name
> |  ActiveSheet.Name = StrSheetName  ' Name current sheet file name
> |    For RowCount = 5 To 32
> |    If RowCount <> 8 And RowCount <> 10 And RowCount <> 13 And RowCount
<>_
> 18> | And RowCount <> 26 And RowCount <> 29 Then       ' Avoid cells with
null value|
> |    BnumericChecker = Worksheets(StrSheetName).Cells(RowCount, 2).Value
> |    DnumericChecker = Worksheets(StrSheetName).Cells(RowCount, 4).Value
> |     If IsNumeric(BnumericChecker) Then intBcellvalue(RowCount) =_ '
check for numeric then populate array

> | intBcellvalue(RowCount) + Val(Format(BnumericChecker, "#.00"))
> |      If IsNumeric(DnumericChecker) Then intDcellvalue(RowCount) =_
'check for isnumeric then populate array with 2 decimal point value
> | 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 ' ' Avoid rows with null
values

> |     Worksheets("sheet1").Cells(RowCount, 2).Value =_
> | Format(intBcellvalue(RowCount) / Filecounter, "#.00") 'average the total
by the amount of files opened and add values to cells
> |      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
> | > > |
> | >
> |
> |
>