Excel Macros and Exotel Reports

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