How to calculate in/out minutes used per month?

[RussMyers]

Have the Obi200/GV combo.

Since we will (probably) be moving to a different SIP this May 2014, how to I calculate incoming/outgoing minutes we use per month so I can pick a plan that works for us?

I see no easy way to do that from either the GV or Obi call history interface, but maybe I'm missing it.

Thanks,

RussMyers

[sdb-]

You may be able to export your call history from google into a spreadsheet?

You can save the last 200 calls from the OBi.  I would manipulate that with python but a spreadsheet may suffice.

[giqcass]

You can save the last 200 calls from the OBi.  I would manipulate that with python but a spreadsheet may suffice.

I don't know how to grab the last 200 calls from an obi as one file.  Let me know and I can set up something to work with that.


I didn't really feel like trying to write a script to scrape the data but that would be easier from the user side.  Short of that I don't see any fast and easy way to do this.  I have an easy but time consuming way using a spreadsheet in Google Drive.
In Google Voice Click More >> placed calls
This shows you a list of the last 20 outgoing calls.
Copy and paste the first 20 outgoing calls from your GV account into a spreadsheet.  The spreadsheet will look terrible.  All the data will be posted in column A. Hit the next 20 results in GV and paste that at the very bottom of the spreadsheet in column A.  Repeat until you have your month worth of calls.

This code will find how long each call was.  If the data doesn't have a call in any cell it just puts a zero. In cell B1 place this code.
=iF((right(A1,4))="long",(SPLIT( LOWER(RIGHT(A1,18)) ; "abcdefghijklmnopqrstuvwxyz " )),0)

Copy cell B1.
Highlight Column B and paste the code.
Now add up the minutes.  In Cell C1 enter this code
=sum(B:B)

That is how many outgoing minutes you used.  Repeat the process for incoming.

Anyone have any easier way to import data from GV or the Obi into a spreadsheet?  

[giqcass]

I just found how to save from the Obi.  I'm not sure if I feel like parsingo that xml mess in python or Java.
Another pain in the butt way.

Go to
>>>Status
>>>>>>>>call history
On the far right is a button to save your call history.  Last 400 records.

Save the file as callhistory.html
Edit with textedit
Find and replace the following
<CallHistory date="  with  blank space
" time=" with ,
<Event time=" with ,
<e0> with ,
<e1> with ,
</CallHistory> with <BR>
"> with blank space

Save the file
Open in firefox
Select all
Copy
Paste in new text file.  
Save as callhistory.txt
Import into Google Drive as a spreadsheet

Put this code into cell N1
=if((left(E1,2))="Fo","Incoming","Outgoing")

Put this code into cell O1
=if(L1>0,(if(N1="incoming",L1-B1,0)),0)
Click O1 and copy it.  Click O column header and paste.

Put this code into cell P1
=if((if(ISNUMBER(J1)=FALSE,0,J1))>0,(if(N1="Outgoing",J1-B1,0)),0)
Click P1 and copy it.  Click P column header and paste.

Put this code into cell Q1
=CONCAT("Incoming ",(sum(O:O)))

Put this code into cell R1
=CONCAT("Incoming ",(sum(P:P)))

[sdb-]

Go to
>>>Status
>>>>>>>>call history
On the far right is a button to save your call history.  Last 400 records.

I am fairly certain on the OBi110 it was only 200...  400 on the OBi202?  That would be nice.

You can fetch that XML will curl:

Code Select Expand

curl --digest -u "$ADMIN:$PASSW"  http://$OBI_IP/callhistory.xml

I have a script I run nightly that does a backup of the config and a couple of times per week does the callhistory.  It compares the new call history against the old, and if the entire file is the same it discards the new one, and if the end of the file is the same it discards the old one.  Someday I hope to merge the records into monthly files but that isn't done (12hr days at work slow down the side projects).

When I was running my nightly script against an OBi110 I found fetching data like that from it was unreliable.  Usually the callhistory.xml came OK (but the config was almost always messed up unless I rebooted the OBi shortly before trying) but sometimes it was truncated and every now and then there were drops from the middle of the file.

With my OBi202 I have not observed any problems fetching data from it.

[QBZappy]

I just found how to save from the Obi.  I'm not sure if I feel like parsingo that xml mess in python or Java.
Another pain in the butt way.

The easiest way I have found is to open the call history xml file in excel. It results in nice columns of information which can then be further processed to determine other useful info like call duration, number of calls,  (whatever) using the excel functions. No manual parsing is involved since excel parses it for you automatically.

[giqcass]

I just found how to save from the Obi.  I'm not sure if I feel like parsingo that xml mess in python or Java.
Another pain in the butt way.

The easiest way I have found is to open the call history xml file in excel. It results in nice columns of information which an then be further processed to determine other useful info like call duration, number of calls,  (whatever) using the excel functions. No manual parsing is involved since excel parses it for you automatically.

My computer with Office on it is down right now so I was not able to import it with Excel.  I tried importing it into Google drives spreadsheet program directly and that would not work.  That's why I hacked the file into a CSV file.  That imported into Google drive nicely.  Then with a little spreadsheet magic I was able to pull out my totals.  It was fun writing out those formulas above.  I haven't done anything on a spreadsheet with nested logic in a while.

[sdb-]

The easiest way I have found is to open the call history xml file in excel.

My computer with Office on it is down right now so I was not able to import it with Excel.  I tried importing it into Google drives spreadsheet program directly and that would not work.

Yeah, unless I take it in to work I'm not going to open it with Excel.  I think the last Microsoft Office I had at home was 1997 and I trashed that in 2001 or 2002.

It didn't look usable when opened with LibreOffice Calc.  So I used sed instead of manual editing for:

Find and replace the following
<CallHistory date="  with  blank space
" time=" with ,
<Event time=" with ,
<e0> with ,
<e1> with ,
</CallHistory> with <BR>
"> with blank space

Code Select Expand


sed 's/<CallHistory date="/ /g; s/" time="/,/g; s/<Event time="/,/g; s/<e[01]>/,/g; s/<\/CallHistory>/<BR>/g; s/">/ /g'   < dlfile.xml >newfile.txt


(that's not tested, just from qlcass' post like I did it the first time)

[Robert.Thompson]

You can try this: http://www.obitalk.com/forum/index.php?topic=7188.0