IPython and Excel Integration

Monday, September 9th, 2013

The Python programming language offers a great choice of tools and libraries for all sorts of mathematical and scientific use cases. Yet the out of the box command line interface leaves a lot to be desired, in particular on Windows - there's no syntax coloring, no autocomplete [1] nor input history. Fortunately, there's the IPython project - an extraordinary attempt by the open source community [2] to add these features and provide a truly interactive Python interpreter. After 10 years of development, a stable, 1.0 version of IPython has lately been released.

I am proud to announce that, as of today, DataNitro fully integrates the IPython shell with Excel on Windows.

In this post I'm going to describe the installation process of IPython and demonstrate its most valuable features - all in Excel!

Installation

Setting up Python and its libraries on Windows is not always a straightforward process - see our previous blog post for an introduction. Fortunately, DataNitro installs IPython and its dependencies (distribute and pyreadline) by default.

Installing IPython in Excel from DataNitro installer


If you opted out during the installation, you can still add IPython (or update) at any later time by clicking "Install IPython" on the DataNitro Settings window:

Installing IPython in Excel


DataNitro supports IPython for 32-bit Python interpreters (versions 2.6, 2.7, 3.2 and 3.3) on any modern Windows platform (XP, Vista, 7 and 8).

If, for some reason, you prefer to manually install IPython, see the official instructions here.

Coloring and Indenting

Although the majority of Windows CMD users are accustomed to a plain, black and white console, IPython colors input and output, just like any modern shell. Additionally, the code typed is automatically indented, which particularly helps avoiding syntax errors when copying and pasting code from outside sources.

IPython and Excel

Input History

Just press the "up" key to browse thru your input history - super useful for repeated commands. Input history is also persisted across IPython sessions, so the next time you start Excel and the Python shell, everything will be available again.

Tab-completion and Help

How many times have you had to stop typing and think about the spelling of some rarely used function? Is it random.rand(), random.random() or random.randint() ? Is the random range inclusive or not? Integer result or a float? Just press random.<TAB> to see the list of available members in the random module. This is exactly what tab-completion is for in the interactive mode - it allows you to dynamically inspect any object.

You can also get extended help by typing help(object) or object?. Additionally, %quickref and "?" provide IPython help.

IPython in Excel Help

%magic commands

IPython treats any input that begins with the '%' character as a "magic function" - these are special commands for various purposes. Here are a couple interesting ones:

%run foo.py

Runs module 'foo'

%cd folder

Changes the current working directory to 'folder'

%time expr

Outputs the running time of expression "expr" (in seconds)

%paste


Pastes the content of the clipboard into the interpreter. Particularly useful when pasting formatted Python code from the Web or outside editor - no need to worry about indenting every single line!

You can also define your own magics by subclassing IPython.core.magic.Magics.

Invoking system commands

With !cmd you can invoke any Windows command directly in IPython. For example !ping www.google.com starts pinging google's servers, while !start excel.exe starts another instance of Excel.

IPython, Excel and Windows Commands

With "!" on hand, you pretty much never have to leave the Python shell to execute other programs.

Debugging with PDB

Type 'debug' to invoke PDB and start debugging your Excel scripts with a full-blown debugger. IPython will also automatically invoke PDB on any uncaught Python exception. I will run a more detailed blog post on this in the near future, stay tuned!

IPython, Excel and Python debugging

This is just the tip of the iceberg of what IPython can do for you. Have a look at the IPython documentation for a more thorough introduction - it's an extremely powerful tool.



As always, don't hesitate to contact me at victor@datanitro.com with any Excel + Python related questions. I would love to see how you use IPython in Excel.

Happy scripting Excel with IPython!




Notes:

[1] There are some efforts to introduce auto-complete in Python 3.4

[2] IPython was started by Fernado Perez at Berkley. Thank you for the great effort!



Want to use Python in Excel?

Try DataNitro!

 


Are you interested in a custom Excel plugin? We can help.
Contact us at code@datanitro.com.