Forums

[recapen]

Folks,

I have built some Macros for the download data coming from the 3400EM.

It works from 5.09.32-MV10 up so far.

It takes the engine data and creates graphs of your EGT vs RPM (same scale) and CHT, OAT, OilTemp (again same scale). There are also a couple that average your CHT/EGT and graph the differentials.

The downloads need to have just the right data items checked off for the download - but if you have the EM only - you don't need the rest of the stuff anyway.

I'm using it to help me analyze my CHT's at a more discrete level. For a constant power setting, I have a +/- 3 degree spread from average for my CHT's and +/- 10 degree spread for EGT's.

You could probably tailor them to your specific data needs and if you have other than the EM - it'll need some specific tweaking. I'll share my basic work if someone wants it.

Ralph Capen

[flyvans.com]

awesome!

we're not yet flying, but will have one EF and one EM, so we're definitely interested... also, when time comes i'll be glad to support further development, got a little .net/vb background.

rgds,
bernie

[wooody04]

I have an pair of Advanced Decks coming also, 1 EF and 1 EM and would be interested in the macros also.

TIA

Jim Streit

[orchidman]

[MTBehnke]

I was going to start writing this same type of macro - if you want to send me a copy maybe I can adapt to the full data set for those with both EF and EM. My email is mike at mikesrv9a dot com. Thanks.
_________________
Mike Behnke
RV-9A Flying
www.mikesrv9a.com

[Roegge]

Very interesting


Is it possible for you guys to post this little macro or an excel-sheet with the macro included here in this thread? I guess that many would appreciate that.

Regards
Andreas
_________________
Glastar Sportsman 2+2
with 3500 and 3400

[recapen]

Use at your own risk - no warranty implied etc etc.

Here's the instructions - rudimentary - but they allow me to remember what's going on...:
******Beginning of instructions************
Fields being downloaded are:

DATE
TIME
VOLTS
OAT
FUEL_COMP
RPM
MANIFOLD
FUEL_PSI
FUEL_FLOW
AMPS_SHUNT
OIL_PSI
OIL_TEMP
EGT1
EGT2
EGT3
EGT4
CHT1
CHT2
CHT3
CHT4

The fields show up in this order - all data after CHt4 is deleted by the macros

Open Excel normally
Open the XXDATEXX.ALD file by selecting all files
The import wizard will start
Select delimited
Select comma - deselect tabs
Press Finish
Click the developer tab
Click Macros
Run AFS Data capture macro
Macro completes with second row highlighted
scroll down to row prior to data of interest
Shift-Left-click to select rows to be deleted
Right click and select delete to delete early/extraneous data
Enter the following:
V2 gets graph starting point
W2 gets graph ending point
X2 gets worksheet name (XXDATEXX)
Click Macros
Run Charts_Creating macro
Macro completes with 4 added charts in their own tabs.

If you want to look at a specific time-range, enter the start row number in V2 and
the end row number in W2, delete the four charts and re-run

******end of instructions************

Here are the macros - you'll need to copy them in to the excel macro section

******Beginning of first Macro AFS DATA CAPTURE************

Sub AFS_Data_capture()
'
' AFS_Data_capture Macro
' Configures data from AFS3400EM flight computer for easier diagnostic use
'

' Get rid of the Version info row
Rows(1).Delete
' Get rid of extraneous data columns
Columns("U:X").Delete
' Set up the columns for appropriate width
Columns("B:B").ColumnWidth = 7
Columns("C:C").ColumnWidth = 6.5
Columns("D:D").ColumnWidth = 4.63
Columns("E:E").ColumnWidth = 5.75
Columns("F:F").ColumnWidth = 5.63
Columns("H:I").ColumnWidth = 5.88
Columns("J:J").ColumnWidth = 6.75
Columns("K:K").ColumnWidth = 4
Columns("L:L").ColumnWidth = 5.25
Columns("M:T").ColumnWidth = 4
' Set column headers for wrap to maintain column width
Columns("E:E").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("H:T").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
' Freeze first row for scrolling
Rows("1:1").Select
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
' Set second row to active to aid deleting extraneous data
Rows("2:2").Select
End Sub

******End of first Macro AFS DATA CAPTURE************

******Beginning of second Macro Charts creating************

Sub Charts_creating()
'
' Chart Creation Macro
'
'
' Prepare relationship table
Range("V6").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""$Q$"",R[-4]C)"
Range("V7").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""$B$"",R[-5]C)"
Range("V8").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""$D$"",R[-6]C)"
Range("V9").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""$L$"",R[-7]C)"
Range("V10").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""$M$"",R[-8]C)"
Range("V11").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""$F$"",R[-9]C)"
Range("V12").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""$AB$"",R[-10]C)"
Range("V13").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""$AG$"",R[-11]C)"
Range("V14").Select
ActiveCell.FormulaR1C1 = "=(""$AA$3"")"
Range("V15").Select
ActiveCell.FormulaR1C1 = "=(""$AF$3"")"
Range("W6").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""$T$"",R[-4]C)"
Range("W7").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""$B$"",R[-5]C)"
Range("W8").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""$D$"",R[-6]C)"
Range("W9").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""$L$"",R[-7]C)"
Range("W10").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""$P$"",R[-8]C)"
Range("W11").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""$F$"",R[-9]C)"
Range("W12").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""$AE$"",R[-10]C)"
Range("W13").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""$AJ$"",R[-11]C)"
Range("W14").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""$AE$"",R[-12]C)"
Range("W15").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""$AJ$"",R[-13]C)"
Range("X6").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""'"",R[-4]C,""'!"",RC[-2],"":"",RC[-1])"
Range("X7").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""'"",R[-5]C,""'!"",RC[-2],"":"",RC[-1])"
Range("X8").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""'"",R[-6]C,""'!"",RC[-2],"":"",RC[-1])"
Range("X9").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""'"",R[-7]C,""'!"",RC[-2],"":"",RC[-1])"
Range("X10").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""'"",R[-8]C,""'!"",RC[-2],"":"",RC[-1])"
Range("X11").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""'"",R[-9]C,""'!"",RC[-2],"":"",RC[-1])"
Range("X12").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""'"",R[-10]C,""'!"",RC[-2],"":"",RC[-1])"
Range("X13").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""'"",R[-11]C,""'!"",RC[-2],"":"",RC[-1])"
Range("X14").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""'"",R[-12]C,""'!"",RC[-2],"":"",RC[-1])"
Range("X15").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""'"",R[-13]C,""'!"",RC[-2],"":"",RC[-1])"
'
'
' EGT_RPM Macro
'
' Following statements get the cell with the active EGT row numbers in it and puts it in variable AFSMax
Dim DtRange As Range
Set DtRange = Range("X7")
Dim EGT As Range
Set EGT = Range("V10:W10")
Dim EGTRange As Range
Set EGTRange = Range("X10")
Dim RPMRange As Range
Set RPMRange = Range("X11")
'
' Following statement selects the range
EGT.Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range(EGTRange)
ActiveChart.ChartType = xlLine
ActiveChart.PlotArea.Select
' Add legend titles
ActiveChart.SeriesCollection(1).Name = "=""EGT 1"""
ActiveChart.SeriesCollection(2).Name = "=""EGT 2"""
ActiveChart.SeriesCollection(3).Name = "=""EGT 3"""
ActiveChart.SeriesCollection(4).Name = "=""EGT 4"""
' Add timeline to X-Axis
ActiveChart.SeriesCollection(4).XValues = Range(DtRange)
' Add RPM to graph
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(5).Name = "=""RPM"""
ActiveChart.SeriesCollection(5).Values = Range(RPMRange)
' Move chart to new page
ActiveChart.ChartArea.Select
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="EGT RPM"
' Make the data sheet active
Sheets(2).Select
'
'
' CHT_OAT_OilT Macro
' Graph of CHT OAT and OilTemps
'
'
' Following statements get the cell with the active CHT row numbers in it and puts it in variable AFSMax
Dim CHT As Range
Set CHT = Range("V6:W6")
Dim CHTRange As Range
Set CHTRange = Range("X6")
Dim OATRange As Range
Set OATRange = Range("X8")
Dim OilTRange As Range
Set OilTRange = Range("X9")
' Following statement selects the range
CHT.Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range(CHTRange)
ActiveChart.ChartType = xlLine
ActiveChart.PlotArea.Select
' Add legend titles
ActiveChart.SeriesCollection(1).Name = "=""CHT 1"""
ActiveChart.SeriesCollection(2).Name = "=""CHT 2"""
ActiveChart.SeriesCollection(3).Name = "=""CHT 3"""
ActiveChart.SeriesCollection(4).Name = "=""CHT 4"""
' Add timeline to X-Axis
ActiveChart.SeriesCollection(4).XValues = Range(DtRange)
' Add OAT to graph
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(5).Name = "=""OAT"""
ActiveChart.SeriesCollection(5).Values = Range(OATRange)
' Add Oil Temp to graph
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(6).Name = "=""Oil Temp"""
ActiveChart.SeriesCollection(6).Values = Range(OilTRange)
' Move chart to new page
ActiveChart.ChartArea.Select
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="CHT OAT OilT"
' Make the data sheet active
Sheets(3).Select
'
'
' EGT_Diffs Macro
' EGT differentials
'

' Following statements get the cell with the active EGT row numbers in it and puts it in variable AFSMax
Dim EGTDiff As Range
Set EGTDiff = Range("V12:W12")
Dim EGTDiffRange As Range
Set EGTDiffRange = Range("X12")
Dim EGTCopyRange As Range
Set EGTCopyRange = Range("X14")
'
Columns("AA:AE").Select
Selection.ColumnWidth = 8.5
Range("AA1").Select
ActiveCell.FormulaR1C1 = "Avg EGT"
Range("AB1").Select
ActiveCell.FormulaR1C1 = "Diff EGT 1 to Avg"
Range("AC1").Select
ActiveCell.FormulaR1C1 = "Diff EGT 2 to Avg"
Range("AD1").Select
ActiveCell.FormulaR1C1 = "Diff EGT 3 to Avg"
Range("AE1").Select
ActiveCell.FormulaR1C1 = "Diff EGT 4 to Avg"
Range("AA1:AE1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("AA2").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-14]:RC[-11])"
Range("AB2").Select
ActiveCell.FormulaR1C1 = "=RC[-15]-RC[-1]"
Range("AC2").Select
ActiveCell.FormulaR1C1 = "=RC[-15]-RC[-2]"
Range("AD2").Select
ActiveCell.FormulaR1C1 = "=RC[-15]-RC[-3]"
Range("AE2").Select
ActiveCell.FormulaR1C1 = "=RC[-15]-RC[-4]"
Range("AA2:AE2").Select
Selection.Copy
Range(EGTCopyRange).Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-15
EGTDiff.Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range(EGTDiffRange)
ActiveChart.ChartType = xlLine
ActiveChart.PlotArea.Select
' Add legend titles
ActiveChart.SeriesCollection(1).Name = "=""EGT 1"""
ActiveChart.SeriesCollection(2).Name = "=""EGT 2"""
ActiveChart.SeriesCollection(3).Name = "=""EGT 3"""
ActiveChart.SeriesCollection(4).Name = "=""EGT 4"""
' Add timeline to X-Axis
ActiveChart.SeriesCollection(4).XValues = Range(DtRange)
' Application.CutCopyMode = False
' Move chart to new page
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="EGT Diffs"
' Make the data sheet active
Sheets(4).Select

' CHT_Diffs Macro
' CHT differentials
'

'
' Following statements get the cell with the active EGT row numbers in it and puts it in variable AFSMax
Dim CHTDiff As Range
Set CHTDiff = Range("V13:W13")
Dim CHTDiffRange As Range
Set CHTDiffRange = Range("X13")
Dim CHTCopyRange As Range
Set CHTCopyRange = Range("X15")

Columns("AF:AJ").Select
Selection.ColumnWidth = 8.5
Range("AF1").Select
ActiveCell.FormulaR1C1 = "Avg CHT"
Range("AG1").Select
ActiveCell.FormulaR1C1 = "Diff CHT 1 to Avg"
Range("AH1").Select
ActiveCell.FormulaR1C1 = "Diff CHT 2 to Avg"
Range("AI1").Select
ActiveCell.FormulaR1C1 = "Diff CHT 3 to Avg"
Range("AJ1").Select
ActiveCell.FormulaR1C1 = "Diff CHT 4 to Avg"
Range("AF1:AJ1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("AF2").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-15]:RC[-12])"
Range("AG2").Select
ActiveCell.FormulaR1C1 = "=RC[-16]-RC[-1]"
Range("AH2").Select
ActiveCell.FormulaR1C1 = "=RC[-16]-RC[-2]"
Range("AI2").Select
ActiveCell.FormulaR1C1 = "=RC[-16]-RC[-3]"
Range("AJ2").Select
ActiveCell.FormulaR1C1 = "=RC[-16]-RC[-4]"
Range("AF2:AJ2").Select
Selection.Copy
Range(CHTCopyRange).Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-15
CHTDiff.Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range(CHTDiffRange)
ActiveChart.ChartType = xlLine
ActiveChart.PlotArea.Select
' Add legend titles
ActiveChart.SeriesCollection(1).Name = "=""CHT 1"""
ActiveChart.SeriesCollection(2).Name = "=""CHT 2"""
ActiveChart.SeriesCollection(3).Name = "=""CHT 3"""
ActiveChart.SeriesCollection(4).Name = "=""CHT 4"""
' Add timeline to X-Axis
ActiveChart.SeriesCollection(4).XValues = Range(DtRange)
' Application.CutCopyMode = False
' Move chart to new page
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="CHT Diffs"
Sheets(5).Select
End Sub

******End of second Macro Charts creating************

Good luck - have fun - let me know how you improve this for your use.

[MTBehnke]

Ralph - thanks for posting your macros. I've modified it to automatically import the data from the .AFD file and enabled it to work regardless of whether you have just the engine monitor or have an EFIS as well.

I've put my macro on my website for all to download. Feel free to email me with suggestions, comments or problems. Also, if you want to work together on developing more enhancements and features there's a link to email me on my site.

You can download the macros from http://www.mikesrv9a.com/AFS_data_macros.html
_________________
Mike Behnke
RV-9A Flying
www.mikesrv9a.com

[recapen]

Thanks for taking this to the next level. I only have the engine monitor - but I may upgrade later on.

Ralph

[orchidman]

[recapen]

I used excel 2002 - the first time I tried it, I got the same error message - I was trying to run it from the download screen. I saved the spreadsheet to the same directory as my .ald files and it worked fine......

[Roegge]

Very nice. Thanks for all your efforts....

Rgds
Andreas
_________________
Glastar Sportsman 2+2
with 3500 and 3400