Excel Macros and Exotel Reports
6 min
Get Leg1 & Leg2 Duration from Exotel Call Reports
If you regularly analyze Exotel call reports in Excel, you can automate extraction of Leg1 and Leg2 durations using a VBA macro. Below are prerequisites, a short explanation of the data, step-by-step instructions to import and run the macro, and the macro source code.
Prerequisites
Windows
- Excel with the Developer tab enabled. (Macros and VBA tools are on the Developer tab, which is hidden by default — see “Show the Developer tab” in Microsoft Office help.)
Mac
- Make the Developer tab visible:
- Go to Excel → Preferences… → Ribbon & Toolbar.
- In Customize the Ribbon, check Developer and click Save.
About Leg1 & Leg2 Duration
Call reports downloaded from the Exotel dashboard include a PriceDetails field that contains an array. That array can include Duration values (in seconds) for:
- Leg1 — the From number (outgoing leg)
- Leg2 — the To number (incoming leg)
Note: for a completed incoming call, you will typically see only Leg2 duration.
Quick Setup: Run the Macro
- Download LegDuration.bas.
- In Excel, open the Developer tab and click Visual Basic.
- In the VBA editor: File → Import File..., and choose LegDuration.bas.
- Return to your Exotel call report worksheet.
- Click Macros, select LegDuration, and click Run.
Note: The macro was created and optimised for Microsoft Office 2016. It is expected to work on newer versions, but Exotel does not provide troubleshooting support for local macro errors — feel free to inspect or modify the source if needed.
VBA Macro Source (LegDuration.bas)
Sub LegDuration()
' LegDuration Macro
' Keyboard Shortcut: Ctrl+Shift+L
Columns("O:O").Select
Selection.Copy
Columns("S:S").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("S:S").Select
Selection.Replace What:="Divert", Replacement:="Exc", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.TextToColumns Destination:=Range("S1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
TrailingMinusNumbers:=True
Columns("T:T").Select
Selection.Delete Shift:=xlToLeft
Columns("U:U").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Range("S1").Select
Selection.AutoFilter
sheetName = Worksheets(1).Name
Set sht = Worksheets(sheetName)
Set StartCell = Range("R3")
LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column
ActiveSheet.Range("$A$1:$T$" & CStr(LastRow)).AutoFilter Field:=19, Criteria1:="<>*Leg1*"
Range("T1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("R1").Select
Selection.End(xlDown).Select
Range("T" & CStr(LastRow)).Select
Range("T1:T" & CStr(LastRow)).Select
Range("T" & CStr(LastRow)).Activate
Selection.FillDown
Range("T1").Select
Selection.AutoFilter
Columns("T:T").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("S1").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveSheet.Range("$A$1:$T$" & CStr(LastRow)).AutoFilter Field:=19, Criteria1:="<>*Leg1*"
Range("S2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Selection.End(xlUp).Select
Selection.AutoFilter
Columns("S:T").Select
Selection.Replace What:="N/A", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Columns("S:S").Select
Selection.Cut
Range("U1").Select
ActiveSheet.Paste
Columns("S:S").Select
Selection.Delete Shift:=xlToLeft
Columns("T:T").Select
Selection.TextToColumns Destination:=Range("T1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="D", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Columns("U:U").Select
Selection.TextToColumns Destination:=Range("U1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="=", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Columns("V:V").Select
Selection.TextToColumns Destination:=Range("V1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="s", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Columns("T:T").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Range("T1").Select
ActiveCell.FormulaR1C1 = "Leg1"
Columns("S:S").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("U:U").Select
Selection.Cut
Range("S1").Select
ActiveSheet.Paste
Columns("T:T").Select
Selection.TextToColumns Destination:=Range("T1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="D", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Columns("U:U").Select
Selection.TextToColumns Destination:=Range("U1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="=", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Columns("V:V").Select
Selection.TextToColumns Destination:=Range("V1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="s", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Columns("T:U").Select
Selection.Delete Shift:=xlToLeft
Selection.End(xlUp).Select
ActiveCell.FormulaR1C1 = "Leg2"
Range("S1").Select
End Sub
Troubleshooting & Notes
- The macro assumes your Exotel call export layout has the PriceDetails (or equivalent) column in column O. Adjust column references in the macro if your export format differs.
- If you encounter errors, verify:
- The Developer tab is enabled and macros are allowed.
- The spreadsheet has data starting at row 1 and the expected columns.
- The Sheet1 name or ID references match your workbook (if integrating with Google Sheets scripts).
- This macro performs multiple text-to-columns and replace operations; always test on a copy of your report.