Server statistics in Excel with Python | Hacker News Reader

Server statistics in Excel with Python

mit.edu

44 pointsvj4422 comments

"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/

"Python runs on Windows, Linux/Unix, Mac OS X, and has been ported to the Java and .NET virtual machines."

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."

This post uses Python. As far as Gnuplot, command line tools are not that powerful on Windows.

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/

But to solve the problem described in the article Excel is just not needed.

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.)

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.

The Enthought Python Distribution[1] provides tools that can perform all of this analysis, including visualization, fairly easily and in multiple operating systems.

[1] http://www.enthought.com/products/epd_free.php

> There is pretty much no decent scripting language available on Windows

Hmm, how about JScript? All excel files can be manipulated via COM.

I think you all are missing this little line here:

"I did not want my script to require any technical knowledge, so that non-techies (business people, marketing, sales team etc.) could just press a button and see the results of their work"

Most of us in here know the results of this tutorial can be reproduced with one single line in bash and a perl script.

But try telling a non-techie guy to get that command working. If you did set up the environment for them beforehand it's all frosty. But if not... that would require introducing them to the command line, perl or python AND the INSTALLATION of their respective modules needed to work with Excel.

Just to install perl on windows would mean having to send them on an odyssey through: strawberry, activestate, cygwin, etc and then hope for the best when they get to the module installation part.

That IronSpread thing sure is quite interesting. Too bad it is not supported by Excel 2003 (yup, I'm in a legacy box here)

JScript is already installed on every Windows including XP, it can access and parse files, it has most of Perl regexp goodness so you could still have a one click, again without installing IronSpread. And that would work with your Excel 2003 too.

R http://cran.r-project.org/ is scriptable, install anywhere and can make plots.

Decent scripting language on windows? Powershell...

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

PowerShell?

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.

Powershell is being used for very serious stuff (IIS, Sharepoint). And you can take advantage of any .NET library.

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.

I've played with gltail (can be found at 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.

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/) using which one can generate excel files from python.

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

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.

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.

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.

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.

Reply on news.ycombinator.com