Mathieu Fenniak's Weblog

For the past couple weeks, I’ve been thinking about mathematical model development. There are lots of great tools out there to help with such tasks, like Mathcad and Mathematica. But if you’re doing software development, once you’ve built and tested a model, what you really want is code. Your Mathcad files are great for documentation, testing, and development of your model, but they can’t be embedded in your Java or C++ application.

Additionally, it’s very easy to use functions from those pieces of software which can’t be easily replicated in your software. They have very optimized methods for root solving, matrix math, symbolic derivative calculations, and other such tasks that you can’t reproduce without years worth of effort. So, when you’re developing a model that’s going to be used in software, what’s the easiest way to do it?

Python, of course. Python code tends to be very legible and terse, while still being well suited for mathematical programming. In fact, the syntax and control structures even look pretty similar between Mathcad and Python:

Part of a root solver implemented in Python.

Part of a root solver implemented in Mathcad.

So, it seems natural to me to use Python to develop models. My code can easily be translated into other programming languages after the fact, and it tends to have a more proper programming structure if it’s not translated from Mathcad. Plus, translating a big chunk code from Mathcad can be a real pain in the ass to do manually – there’s just too much to miss, and it can be very time consuming to confirm it’s been done correctly.

Python is not quite a silver bullet in this case, though. Mathcad has a lot of tools for visualization which are very useful when developing a new model. Python’s wonderful abillity to interoperate makes it fairly easy to leverage plotting capabilities of an application like Microsoft Excel though. So, without any further leadup, here’s a quick process which will get you up and running with an Excel plotting Python program:

1. Get and install Python, and the PythonWin extensions (or install ActivePython, which contains all the necessary tools and more).

2. Run the PythonWin IDE program, and generate static COM wrappers for Microsoft Excel. This is as easy as selecting the COM Makepy Utility option from the Tools menu of PythonWin, then selecting the most recent version of the Microsoft Excel n.m Object Library available:

   

   Selecting COM Makepy utility from PythonWin’s menu.

   The static COM wrappers must be used in order to access the Excel constants (of which there are hundreds).

3. Import the COM Dispatch function and the constants namespace into your application:

   
   from win32com.client import Dispatch, constants
   

4. At this point, all that’s left is to go to town on automating Excel. There is a lot of documentation that comes along with Excel, as well as a big chunk of MSDN content that will also help. Let’s dive in, and I’ll just throw an XY scatter plot at you:

   def plot(x, y, xAxisLog=False, yAxisLog=False):
       # acquire application object, which may start application
       application = Dispatch("Excel.Application")
   
       # create new file ('Workbook' in Excel-vocabulary)
       workbook = application.Workbooks.Add()
   
       # store default worksheet object so we can delete it later
       defaultWorksheet = workbook.Worksheets(1)
   
       # build new chart (on seperate page in workbook)
       chart = workbook.Charts.Add()
       chart.ChartType = constants.xlXYScatter
       chart.Name = "Plot"
   
       # create data worksheet
       worksheet = workbook.Worksheets.Add()
       worksheet.Name = "Plot data"
   
       # install data
       xColumn = addDataColumn(worksheet, 0, x)
       yColumn = addDataColumn(worksheet, 1, y)
   
       # create series for chart
       series = chart.SeriesCollection().NewSeries()
       series.XValues = xColumn
       series.Values = yColumn
       series.Name = "Data"
       series.MarkerSize = 3
   
       # setup axises
       xAxis = chart.Axes()[0]
       yAxis = chart.Axes()[1]
       xAxis.HasMajorGridlines = True
       yAxis.HasMajorGridlines = True
       if xAxisLog:
           xAxis.ScaleType = constants.xlLogarithmic
       if yAxisLog:
           yAxis.ScaleType = constants.xlLogarithmic
   
       # remove default worksheet
       defaultWorksheet.Delete()
   
       # make stuff visible now.
       chart.Activate()
       application.Visible = True
   
   def genExcelName(row, col):
       """Translate (0,0) into "A1"."""
       if col < 26:
           colName = chr(col + ord('A'))
       else:
           colName = chr((col / 26)-1 + ord('A')) +\
               chr((col % 26) + ord('A'))
       return "%s%s" % (colName, row + 1)
   
   def addDataColumn(worksheet, columnIdx, data):
       range = worksheet.Range("%s:%s" % (
           genExcelName(0, columnIdx),
           genExcelName(len(data) - 1, columnIdx),
           ))
       for idx, cell in enumerate(range):
           cell.Value = data[idx]
       return range
   

   I suppose that at least an explanation of what this is doing would be a good thing. This program will cause Excel to be run, and a new file to be created. The new file will contain one worksheet with the contents of the x/y arrays in columns A and B. It will also contain one sheet-location chart, an XY scatter, with the x/y points plotted. The x and y arguments to plot are sequences of numbers, and the two flags indicate whether the X and Y axis should be logarithmic or linear.

5. Some examples of using plot:

   # A simple example:
   plot( (1,2,3,4,5), (6,7,8,9,10) )
   
   # Some more data:
   x, y = [], []
   for i in range(100):
       x.append(i)
       y.append(i ** 2)
   plot(x, y)
   
   # Using log axises:
   plot(x, y, True, True)
   

6. Remember, there are good tools available if you’re not in Windows too. gnuplot and gnuplot-py provide a pretty nice graphing environment which is probably quite a bit more capable than Excel.