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