Multi-Dimension Data Table MDDT Feb 2025

Remember this post from 4 years ago?

I offer an updated post. The idea is the same. I’ve made an effort to sharpen the presentation.

Yes, let’s get started.

Here’s the MDDT video.

And here’s a quick Jupyter post-processing video.

Notice that transferring the tabulated data from Excel to Jupyter was as simple as:

• selecting cell K12 (the upper left anchor cell)
• then executing line 64 in Jupyter

  b=%xl_get

And in Jupyter, after that transfer, pandas (for example) can be used to process the data — filter, sort, analyze the data. Of course, Excel or any other software can be used to further process the table of values.

Excel developers and power users may have come across Microsoft Data Tables. I have used these for several years for both one- and two-dimension scenarios. Microsoft Scenario Manager isn’t in my toolbox; I find it’s use futile.

Yes, MS Data Tables are great for a 1 or 2 dimension analysis. Easy, efficient, productive.

---

Past that, what do I use? Long ago, I developed my own method and programmed it as a VBA macro. Quite serviceable, suited my needs, easy to use.

Later, I developed the same idea using PyXLL and Python. My Python macro was much better than my VBA version. Much easier to code, to execute, to maintain, and to extend.

---

In the video, in the first section, I present a 3 dimension example.

In the second section I present the concept of a cached object, a way of condensing a large (maybe huge) output table to a single cell.

---

The Python code for my macro is only 170 lines and that includes comments and debug statements.

We’re getting a lot for our money!

---

Sorting, filtering (maybe using tables) can easily be incorporated, for exammple:

---

The video example illustrates only 3 independent variables but the idea easily extends beyond. Such extension is facilitated by the use of Python and PyXLL.

In the video addendum, I illustrate the use of a PyXLL cached object.

The cached object can be used to store any Python object in a single cell of a spreadsheet.

In this case, where we are analyzing combinations of only three independent variables,

• [1, 2, 3]
• [10, 20, 30, 40]
• [100, 200, 300, 400, 500]

we only have 3 x 4 x 5 = 60 output rows. If we had five independent variables and 10 each then the length of the output would balloon to 10^5 = 100,000 rows — clearly 100,000 rows is no longer effective in a spreadsheet.

In the video we are seeing the great benefit of using a cached object. We can easily look at any row in the output by calling a simple PyXLL function.

=TRANSPOSE(get_objlist_row(W5))

---

The function get_objlist_row(n) fetches only row n in the tabulated output.

Here are excerpts from my Python code showing setting and getting my cached list.

---

#
..
..
	#
	# variable 'range_result' is a Python list used to populate the tabulated output
	#
    # Cache the object in a cell # in this case cell G6
    # print(actbook_name)
    full_name = "".join(["'", activebook_name, "'", "!", "my_object"])
    cell = XLCell.from_range(xl.Range(full_name))
    cell.options(type="object").value = range_result 
	
..
..

@xl_func("int n: var")
def get_objlist_row(n):
    """Get row 'n' from a list object"""
    # Get the Excel.Application object
    xl = pyxll.xl_app()

    activebook_name = xl.ActiveWorkbook.Name
    full_name = "".join(["'", activebook_name, "'", "!", "my_object"])

    # Get the cached object stored at the named range
    try:
        cell = XLCell.from_range(full_name)
        my_list = cell.options(type="object").value
        return my_list[n - 1] # the list is zero-based indexed but the user would want one-based indexing
    except:
        return "error getting reference object or row"
#
#

And here’s the printout of the cell.value
(proving it’s just a list of lists)

#
#
#
[[1, 1.0, 10.0, 100.0, 0.1, 37.0], 
[2, 1.0, 10.0, 200.0, 0.05, 70.33333333333333], 
[3, 1.0, 10.0, 300.0, 0.03333333333333333, 103.66666666666667], 
[4, 1.0, 10.0, 400.0, 0.025, 137.0], 
...


...
[57, 3.0, 40.0, 200.0, 0.008000000000000002, 81.0], 
[58, 3.0, 40.0, 300.0, 0.0023703703703703703, 114.33333333333333], 
[59, 3.0, 40.0, 400.0, 0.0010000000000000002, 147.66666666666666], 
[60, 3.0, 40.0, 500.0, 0.0005120000000000001, 181.0]]
#
#