Embed Jupyter into Microsoft Excel and write Python instead of VBA

Image for post
Image for post
Jupyter Notebooks in Microsoft Excel. Image by the author.

It used to be an “either/or” choice between Excel and Python Jupyter Notebooks. With the introduction of the PyXLL-Jupyter package now you can use both together, side by side.

In this article I’ll show you how to set up Jupyter Notebooks running inside Excel. Share data between the two and even call Python functions written in your Jupyter notebook from your Excel workbook!

Jupyter Notebooks in Microsoft Excel. Video by the author.

Getting Started

First off, to run Python code in Excel you need the PyXLL add-in. The PyXLL add-in is what lets us integrate Python into Excel and use Python instead of VBA. …


Learn how to stream live crypto prices into Microsoft Excel

Image for post
Image for post
Live, Real Time BitMEX prices in Excel. Image by Author.

Note from Towards Data Science’s editors: While we allow independent authors to publish articles in accordance with our rules and guidelines, we do not endorse each author’s contribution. You should not rely on an author’s works without seeking professional advice. See our Reader Terms for details.

Trading crypto currencies can be an extremely interesting and rewarding activity. There are many different platforms and exchanges for trading that will offer API access to their data which allows the savvy trader to build their own tools around their own strategy and trading needs.

Microsoft Excel is the go to choice for many traders because of the enormous potential it offers to create custom tools and dashboards to build market insights, test and experiment with data and ideas, and to monitor portfolio performance and keep track of positions. …


Dramatically improve spreadsheet performance and decouple your workbooks.

Image for post
Image for post

The INDIRECT function in Excel is a tricky beast. One the one hand it can be incredibly useful, but on the other hand, it is responsible for crippling the performance of many spreadsheets.

In this article, we’ll look at what the INDIRECT function is, why it is so bad for performance and an interesting alternative that is superior in almost every way.


Image for post
Image for post
You can have robust EUC policies and still use Excel!

The risk associated with End User Computing, or EUC, is something that companies more and more are taking seriously. End User Computing is, put simply, any system that allows and end user to perform complex computations outside of the prescribed solutions available to them.

A typical example in the finance industry is when a trader develops their own trading model in Excel, which they then use to put on trades in the market. …


Image for post
Image for post
Constructing CPPNs in Excel with PyTorch and PyXLL

After reading Making deep neural networks paint to understand how they work by Paras Chopra I was inspired to delve into some experiments of my own. The images produced were intriguing, and I wanted to play around and get a feel for how they changed in response to changing the structure of the neural network.

I encourage you to go back and read the original article, but as a brief summary it involves using Compositional Pattern Producing Networks (CPPN) with random weights to produce abstract images. …


Image for post
Image for post
Solving has been artifically slowed down for visualization.

Microsoft Excel has a great feature, “Goal Seek”, under the Data / What If… menu. Given a formula, it will calculate what the input needs to be in order for that formula to equal a specific result. It’s a very powerful but simple tool.

The standard “Goal Seek” feature while useful in many situations is somewhat limited. It can only solve for a single input cell, and there are no controls over the algorithm used.

There is the more sophisticated “Solver” add-in that also comes as part of Excel. This is like “Goal Seek” on steroids! …


Image for post
Image for post

Clojure combines the interactive development convenience of a scripting language with an efficient and robust infrastructure for multithreaded programming. It runs on the JVM and interfaces with Java code seamlessly, making it a pragmatic choice for those who want the the succinctness, flexibility and productivity of a dynamic language with the performance, security and stability of the JVM.

Developers may love what Clojure can offer, but more often than not code is written by developers to use used by end users who are not themselves developers. Creating rich user interfaces or web applications for end users takes time and effort, and can be brittle as changes to the underlying application often require changes to the user interface. …


Image for post
Image for post

Excel is ubiquitous in nearly every workplace. From top tier investment firms and large scale engineering companies right down to individual sole traders, people get work done using Excel.

This article will look at some of the problems and advantages of using Excel, and how using Java embedded in Excel those problems can be overcome.

You don’t have to look far to find criticism of Excel and cases where its mis-use has resulted in heavy losses to companies. …

About

Tony Roberts

Professional software developer living and working in London. Creator of the Excel add-ins PyXLL and Jinx.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store