6 ms·

Server statistics in Excel with Python

44 points by vj44 14y ago

bilbobaggie 14y ago
This looks like a good way to design reporting dashboards - it's too bad Excel can't update in real time.
- vj44 14y ago
  http://ironspread.com http://ironspread.com can update Excel in real time as well - that's what I'm working on for my next blog post, which will be available sometime next week.
  - mgurlitz 14y ago
    What are the advantages of IronSpread over traditional Python COM access? The API appears only marginally better than Excel's VBA interface, and it looks like IronSpread can't interact with multiple workbooks. The benefit of having the shell launched from within Excel, with no setup needed, disappear when you want to run more complex functions like continual updates. And on top of that IronSpread doesn't support Office 2003.
    - karamazov 14y ago
      Regular COM calls can fail (when Excel is busy, for example), and you can't just click "run" from within Excel, you have to start python from a shell, etc. VBA, on the other hand, is hard to extend - getting it to pull logs from a remote server would be painful. As far as interacting with multiple workbooks, that's something we're working on.
- aleyan 14y ago
  Before replying, I just so happen as to be working on a python process to populate an Excel report with COM. Cells and graphs that depend on those update as soon as python executes the COM calls. You probably don't want to use Excel as a GUI for your python app, especially if you need to update frequently, but it is fine for quick reports.
benjarrell 14y ago
First, there is pretty much no decent scripting language available on Windows PowerShell?
- vj44 14y ago
  Let me tell you what's wrong with PowerShell - I've been using it for a while (yes, it's much more useful than the regular cmd), BUT it has multiple inconsistencies with the regular windows shell, in particular when it comes to file paths and escaping white spaces & other characters. I've spent ages debugging such issues and trust me, you don't want to repeat this (also, it's slow, doesn't come with ssh or most other unix tools). Other than that, I have yet to meet anyone who knows how to script in powershell. Of course if you want to, go ahead, but from my experience I'm not going to use it for anything too serious.
  - wslh 14y ago
    Powershell is being used for very serious stuff (IIS, Sharepoint). And you can take advantage of any .NET library.
cygwin98 14y ago
I've played with gltail (can be found at http://www.fudgie.org/ http://www.fudgie.org/) since yesterday. It should do what you want to achieve and run under Windows as well, though it's written in openGL and Ruby.
acqq 14y ago
"There is pretty much no decent scripting language available on Windows, so if you do not have immediate access to some unix (or at least cygwin), you're out of luck." He seems to be fully uniformed about what he writes about. The graphs he produced in Excel can be produced without Excel and just with open-source tools on practically any platform. Python and Gnuplot can be installed anywhere: http://www.python.org/ http://www.python.org/ "Python runs on Windows, Linux/Unix, Mac OS X, and has been ported to the Java and .NET virtual machines." http://www.gnuplot.info/ http://www.gnuplot.info/ "Gnuplot is a portable command-line driven graphing utility for Linux, OS/2, MS Windows, OSX, VMS, and many other platforms."
- karamazov 14y ago
  This post uses Python. As far as Gnuplot, command line tools are not that powerful on Windows.
  - acqq 14y ago
    When you really need to script any VBA enabled application on Windows (Excel, Word, you name it) they can be scripted with ActiveState Python or ActiveState Perl which are free and moreover exist for more than a decade: http://www.activestate.com/ http://www.activestate.com/ But to solve the problem described in the article Excel is just not needed.
    - vj44 14y ago
      Unfortunately with activestate you can't run python from within excel, you've got to somehow run the script at the "outside" (be it shell, some other program etc.)
      - acqq 14y ago
        You can: with a few lines of text a script (be it JScript, Active Python, Active Perl) can be made a COM accessible "object" and can be run from Word and Excel or any other program which has VBA. Visual Studio had that too until Version 2012. Now MS removed VBA infrastructure from the Visual Studio 2012 -- although since 1998 I was able to assign a keypress to run a Perl code from a VS editor to, for example, modify the selection of a C code I develop (I really use such tricks to do non trivial modifications over some bigger piece of code -- pure magic!) So only now in 2012 they killed that in VS. Stupid MS, destroying the best features they already had.
sateesh 14y ago
I don't understand why the author is using 'IronSpread' which is available on Windows only. For python there are modules like xlwt (www.python-excel.org) and openpyxl (https://bitbucket.org/ericgazoni/openpyxl/ https://bitbucket.org/ericgazoni/openpyxl/) using which one can generate excel files from python.
hogu 14y ago
I'm all in favor of better python interfaces for excel, but you need a better example, computing rolling time series statistics, and visualizing them? That's a job for pandas. If you want excel as the front end (I understand why you would want that) you could use pandas to process the data, and use excel as the frontend, instead of writing all that code.

44 points by vj44 14y ago

bilbobaggie 14y ago

This looks like a good way to design reporting dashboards - it's too bad Excel can't update in real time.

vj44 14y ago
http://ironspread.com http://ironspread.com can update Excel in real time as well - that's what I'm working on for my next blog post, which will be available sometime next week.
- mgurlitz 14y ago
  What are the advantages of IronSpread over traditional Python COM access? The API appears only marginally better than Excel's VBA interface, and it looks like IronSpread can't interact with multiple workbooks. The benefit of having the shell launched from within Excel, with no setup needed, disappear when you want to run more complex functions like continual updates. And on top of that IronSpread doesn't support Office 2003.
  - karamazov 14y ago
    Regular COM calls can fail (when Excel is busy, for example), and you can't just click "run" from within Excel, you have to start python from a shell, etc. VBA, on the other hand, is hard to extend - getting it to pull logs from a remote server would be painful. As far as interacting with multiple workbooks, that's something we're working on.
aleyan 14y ago
Before replying, I just so happen as to be working on a python process to populate an Excel report with COM. Cells and graphs that depend on those update as soon as python executes the COM calls. You probably don't want to use Excel as a GUI for your python app, especially if you need to update frequently, but it is fine for quick reports.

benjarrell 14y ago

First, there is pretty much no decent scripting language available on Windows PowerShell?

vj44 14y ago
Let me tell you what's wrong with PowerShell - I've been using it for a while (yes, it's much more useful than the regular cmd), BUT it has multiple inconsistencies with the regular windows shell, in particular when it comes to file paths and escaping white spaces & other characters. I've spent ages debugging such issues and trust me, you don't want to repeat this (also, it's slow, doesn't come with ssh or most other unix tools). Other than that, I have yet to meet anyone who knows how to script in powershell. Of course if you want to, go ahead, but from my experience I'm not going to use it for anything too serious.
- wslh 14y ago
  Powershell is being used for very serious stuff (IIS, Sharepoint). And you can take advantage of any .NET library.

cygwin98 14y ago

I've played with gltail (can be found at http://www.fudgie.org/ http://www.fudgie.org/) since yesterday. It should do what you want to achieve and run under Windows as well, though it's written in openGL and Ruby.

acqq 14y ago

"There is pretty much no decent scripting language available on Windows, so if you do not have immediate access to some unix (or at least cygwin), you're out of luck." He seems to be fully uniformed about what he writes about. The graphs he produced in Excel can be produced without Excel and just with open-source tools on practically any platform. Python and Gnuplot can be installed anywhere: http://www.python.org/ http://www.python.org/ "Python runs on Windows, Linux/Unix, Mac OS X, and has been ported to the Java and .NET virtual machines." http://www.gnuplot.info/ http://www.gnuplot.info/ "Gnuplot is a portable command-line driven graphing utility for Linux, OS/2, MS Windows, OSX, VMS, and many other platforms."

karamazov 14y ago
This post uses Python. As far as Gnuplot, command line tools are not that powerful on Windows.
- acqq 14y ago
  When you really need to script any VBA enabled application on Windows (Excel, Word, you name it) they can be scripted with ActiveState Python or ActiveState Perl which are free and moreover exist for more than a decade: http://www.activestate.com/ http://www.activestate.com/ But to solve the problem described in the article Excel is just not needed.
  - vj44 14y ago
    Unfortunately with activestate you can't run python from within excel, you've got to somehow run the script at the "outside" (be it shell, some other program etc.)
    - acqq 14y ago
      You can: with a few lines of text a script (be it JScript, Active Python, Active Perl) can be made a COM accessible "object" and can be run from Word and Excel or any other program which has VBA. Visual Studio had that too until Version 2012. Now MS removed VBA infrastructure from the Visual Studio 2012 -- although since 1998 I was able to assign a keypress to run a Perl code from a VS editor to, for example, modify the selection of a C code I develop (I really use such tricks to do non trivial modifications over some bigger piece of code -- pure magic!) So only now in 2012 they killed that in VS. Stupid MS, destroying the best features they already had.

sateesh 14y ago

I don't understand why the author is using 'IronSpread' which is available on Windows only. For python there are modules like xlwt (www.python-excel.org) and openpyxl (https://bitbucket.org/ericgazoni/openpyxl/ https://bitbucket.org/ericgazoni/openpyxl/) using which one can generate excel files from python.

hogu 14y ago

I'm all in favor of better python interfaces for excel, but you need a better example, computing rolling time series statistics, and visualizing them? That's a job for pandas. If you want excel as the front end (I understand why you would want that) you could use pandas to process the data, and use excel as the frontend, instead of writing all that code.