Articles/JExcel
Using J in Excel
By Richard Hill
info@assuredsystems.com.au
23 January 2004
Published in Vector V21#1
Whenever I tried to use Microsoft's Excel, I missed to ability to use Iverson's notation for a formula or a macro. Some people seem to be able to easily learn different languages, and the advice given in the documentation for Iverson's language, J, is fairly consistent, "use things like Excel's macro facility, VBA, when appropriate, J when appropriate".
Don't you find it maddening when you have to use another tool, at rare intervals, and you have to re-learn its inconsistencies? I do, so when I use Excel, I try to use the bare minimum of Excel facilities, whether or not it would be better to use more VBA and less J.
Personally, I find it enough to learn J and I don’t really want to learn the other things. To put it another way, you could be trying to use a computer for a purpose other than learning programming. You might prefer to use a single multipurpose programming tool that meets your needs rather than be forever spending time trying to learn yet another programming tool just because it is the best for a particular purpose.
This means that you can use your familiar multipurpose tool to get on with what you really want to do, be it modelling factory production, actuarial research, textual analysis or whatever. If using your chosen tool is your goal, and you have chosen Iverson's notation, then you might want to temper the advice from Jsoftware, and actually try to use J everywhere you possibly can. This approach should increase the amount of practice with the language and familiarity with the tokens.
There are several ways you can use J from Excel. One of the simplest is to use it in a formula.
Take the Excel formula, (assume it is for cell D1):
=A1 - B1 - C1
(Assuming cells A1 B1 C1 hold 3 4 5, then in Excel: 3 - 4 - 5 = -6)
Instead, you can force the formula to be done in Iverson notation:
=jcmd(A1 & "-" & B1 & "-" & C1)
(Assuming cells A1 B1 C1 hold 3 4 5, then in J: 3 - 4 - 5 = 4)
To achieve this you have to have the macros and functions to support this in your spreadsheet. The Excel VBA macros and functions to support J are held in the text file: system\examples\ole\excel\jsutil.txt. I found the best way to get them in, is to:
open the util file with Notepad, highlight from start to end, Ctrl C, then open the spreadsheet, select Tools/Macro/Visual Basic Editor/Module/View Code and Ctrl V the code into the module.
You also need to get the spreadsheet to automatically open J.
There is an auto-open macro in the comments at the start of the jsutil text. Just delete the leading quotes from this bit of text.
Then you have to save and close the worksheet and re-open it to get it to work.
This process is documented in the J Help facility, User manual, OLE & OCX, Tutorial: J OLE Server for Excel.
To quote the tutorial, "In general, jcmd can be used for calculations which return a single value to be displayed in the current cell. The right argument is the sentence to be sent to J."
There are other macros described in the tutorial, mostly for handling spreadsheet ranges.
Using these macros, you can Excel's VBA editor as a method of building and handling J scripts, like this:
Sub accum()
' writes the accumulated values and progressive means in adjacent columns
' To test, please enter some numbers in column E rows 5,6,7,8,9
jsetr "jvar", "E5:E9"
' Note: the value that comes from Excel has each cell boxed
' then you can have a set of J statements
jdo " a=: +/\,>jvar"
jdo " m=: a% 1+ i.#jvar"
' the value to be put back into the spreadsheet
' has to be boxed to match the target range
jdo "ret=: <""0 a,. m"
jcmdr "ret", "F5:G9"
End Sub
With this method, you have the J execution box open to help in debugging. If there is a problem on the J side you get an error code in Excel, and you can flick back to J and see more about the error.
If you want to pass the spreadsheet on to other users, but you don’t want to have the users involved with the J side, you can remove the jshow and jlog lines in the auto_run macro to hide the J part of the exercise.
This facility should enable complex applications to be built in Excel, with the developer being able to keep as much as possible within the clean consistent logic of Iverson's notation.
Here is a more detailed example. Let us assume that we want to do some more complicated development, for Excel, using J.
For example, let us try to develop a useful tool for a factory production planner.
A problem in production planning is to predict what your customer will order next and when.
If you are trying to forecast what a group of customers will do, then there are many well chewed over approaches that work quite well with aggregate demand.
However, there is a class of problems where there is only one customer for the product and the classic approaches don't work. You could, for example, be supplying a unique component to an automotive manufacturer.
Typically the manufacturer will have some sort of planning system and will be ordering components to a pattern that looks like this...
Date Quan 4/3 101 5/3 197 7/3 208 8/3 151 11/3 103 12/3 97 15/3 209 17/3 147 20/3 205
The problem is to predict the date and quantity of the next few requirements.
The next section of these notes shows how one can go about developing a solution starting with Excel and working in J.
Lets look at the quantity problem first.
"Local knowledge" tells us that the customer has some sort of batching process internally. They could be using racks to move the parts up to the assembly line or some sort of "Kanban" lot cards to move parts within the plant. Therefore the requirements will be in multiples of the internal lot size, but there is a superimposed variation caused by quality problems and/or odd quantities needed for replacement parts.
From the user's point of view, we want to give them the facility to highlight some figures in Excel, hit a key, and have the result returned to the spreadsheet.
Here how I use VBA and J to do that.
The first step is to get the selected numbers out of Excel into J when the user presses a key in Excel.
In Excel, I usually make a new macro this way.
Select Tools/Macro/Record New Macro. Type in the name Lotsize, and give it the shortcut key L. Then go back to Tools/Macro and select Stop Recording. Back again to Tools/Macro, select Lotsize from the list of macros and click on Edit.
Now we can type in the statements to get the selected numbers into J. The result ends up looking like this:
Sub Lotsize()
'
' Lotsize Macro
' Macro recorded 8/01/2004 by Hill
'
' Keyboard Shortcut: Ctrl+l
'
' get selected shipments into J
shp = Selection.Value
' this assumes that auto_run has already built the linkages.
ec = js.set("shp", shp)
If ec Then MsgBox "Error code: " & Str(ec)
End Sub
I usually find it best to save the Excel, and restart it, at this point. When you start Excel, the auto_run macro brings up the J window. Then Alt-tab back to Excel, select the numbers to be handled, press Ctrl L.
If you now go to the ijx window, type in shp and Enter, you will find the numbers in J (boxed).
,shp +---+---+---+---+---+--+---+---+---+ |101|197|208|151|103|97|209|147|205| +---+---+---+---+---+--+---+---+---+ ...
Now its time for the fun part of the job. I love playing around with numbers in J.
As soon as I've got a sentence worth saving, I start building a script file. This is a good idea, because if there is problem between Excel and J, the quick way out is to start Excel again and you can lose the ijx window.
When you have a J script, you need it to be loaded by your Excel macro. Another couple of j statements in the macro will handle this. For example:
jdo "load 'g:\jex\lotsize.ijs'" jdo "lotsize shp"
Finally you get a macro that contains a mix of VBA and J, like this:
'
' Lotsize Macro
' Macro recorded 8/01/2004 by Hill
'
' Keyboard Shortcut: Ctrl+l
'
' get selected shipments into J
shp = Selection.Value
' this assumes that auto-run has already built the linkages.
ec = js.set("shp", shp)
If ec Then MsgBox "Error code: " & Str(ec)
' bring in a J script
jdo "load 'g:\jex\lotsize.ijs'"
' execute J functions within the script
jdo "ltsz=: lotsize shp"
' return the calculated lotsize to the spreadsheet
' the value to be put back into the spreadsheet
' has to be boxed to match the target range
jdo "ltsz=: <ltsz"
jcmdr "ltsz", "A1:A1"
End Sub
The bulk of the logic is in the J script. This is what was written in this case.
lotsize=: 3 : 0
NB. <calc lotsize>=: lotsize <vector of ship quantities>
NB. Changes:
NB. a 040114 improved text
NB. summary of algorithm
NB. remove sports eg outside n*sigma limit
NB. take newest
NB. assume lots per day 1 to n
NB. get remainders for various n
NB. round to units
NB. nub of possible lot sizes
NB. remainders with each lot size
NB. if > 50 pct, use complement
NB. get propor to ship size
NB. lot size with min total propor diff
NB. allow for incoming boxed arrays
s=. >,y.
A=. # %~ +/ NB. mean (from Phrases)
mn=. A s
m27=. (+/@(*:@(] - +/ % #)) % #)"1 NB. Sample variance (dispersion) of y
m28=. %:@m27"1 NB. Standard deviation of y
sd=. m28 s
NB. filter out shipments outside limits
f=. (mn- f),mn+ f=. 2*sd
s=. ((s>0{f)*.(s<1{f))#s
NB. select last few usable shipments for further work
s=: (-12 <. #s){. s
NB. assume a limited number of lots per shipment
NB. get possible lot sizes
b=: ~. , <. 0.5+ s % / 1+i.9
NB. which lot size gives minimum remainder?
r=: b | / s
u=: r> 0.5*b
c=: (r* -. u) + u* b -r
d=: +/ |: c %"1 s
(d i. <. / d){b
)
You can use Excel and Iverson Notation together without having to learn VBA at all, by using jcmd in the Excel formula box. Unfortunately, once the user interaction is slightly complicated, you have to write some VBA. However, you can keep it to a minimum.
I like to do the fun part in J and the least that I can get away with in VBA.