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  nor input history. Fortunately, there's the IPython project - an extraordinary attempt by the open source community  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!
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.
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.
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.
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 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:
Runs module 'foo'
Changes the current working directory to 'folder' |
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!|
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.
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!
As always, don't hesitate to contact me at email@example.com with any Excel + Python related questions. I would love to see how you use IPython in Excel.
Happy scripting Excel with IPython!
Are you interested in a custom Excel plugin? We can help.
Contact us at firstname.lastname@example.org.