// excel calculator

O:(("=";"<";">";"<>";"<=";">=")						/ relations
   ,"&"									/ join
   "+-"									/ plus, minus
   "*/"									/ times, divide
   ,"^"									/ power
   ,"_"									/ unary minus
   ,"IF"								/ ternary functions
   ("AND";"OR";"MIN";"MAX")						/ binary functions
   ,"NOT"								/ unary functions
   ("SUM";"AVG")							/ list functions
   ,","									/ range union
   ," "									/ range intersect
   ,"!"									/ at
   ,":"									/ cell range
   ,";"									/ arg separator
   ,"("									/ left paren
   ,")"									/ right paren
   ,Z:_ci 255)								/ end of string

G:(("IF";"AND";"OR";"MIN";"MAX");("SUM";"AVG"))				/ ; vs ,

V:2 2 2 2 2 1 3 2 1 1 2 2 2 2 -1 0 -2 0					/ valence

/   =&+*^_321f, !:;()Zc : s -> right assoc, r -> left assoc

P:("rsssssssssssssrsrrc"	/ = < > <> <= >=
   "rrssssssssssssrsrrc" 	/ &
   "rrrsssssssssssrsrrc"   	/ + -
   "rrrrssssssssssrsrrc"	/ * /
   "rrrrrsssssssssrsrrc"	/ ^
   "rrrrrrssssssssrsrrc"	/ _
   "rrrrrrrsssssssrsrrc"	/ 3
   "rrrrrrrrssssssrsrrc"	/ 2
   "rrrrrrrrrsssssrsrrc"	/ 1
   "rrrrrrrrrrssssrsrrc"	/ f
   "rrrrrrrrrrrsssrsrrc"	/ ,
   "rrrrrrrrrrrrssrsrrc"	/  
   "rrrrrrrrrrrrrsrsrrc"	/ !
   "rrrrrrrrrrrrrrrrrrc"	/ :
   "rrrrrrrrrrrrrrrrrrc"	/ ;
   "sssssssssssssssss c"	/ (
   "rrrrrrrrrrrrrrr rrc"	/ )
   "ssssssssssssssss ac") 	/ Z

K:+(("<>";"(~=)")							/ k translations
    ("<=";"(~>)")
    (">=";"(~<)")
    ("&";"JOIN")
    ("/";"%")
    ("_";"-:")
    (" ";"INTERSECT")
    (",";"UNION")
    ("!";"AT")
    (":";"RANGE"))

R:("INTERSECT";"UNION";"RANGE";"AT")					/ reference operators

state:{{.[x;(;y);:;z]}/[((#x),256)#-1;y;+x]}

D:"0123456789."
C:(D,"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVXYZ";"[";"]";"\"")
C,:,F:?(,//O)_dvl Z,*C

M:state[(1 -1 -1  4  6		/ 0 start
         1  2 -1  4  6		/ 1 constant
         2 -1  3 -1  2		/ 2 [
         1 -1 -1  4  6		/ 3 ]
         4  4  4  5  4		/ 4 open quote
         1 -1 -1  4  6		/ 5 close quote
         1 -1 -1  4  6)]_ic C	/ 6 operator

I:0 3 5				/ token class endpoints

/ tokenize:

token:{sep neg min(()one/fsm x)two/("<>";"<=";">=")}			/ tokenize
fsm:{cut[x;0 M\_ic x]}							/ scan input
cut:{{:[1=#x;*x;x]}'(&(~=)':I _binl y)_ x}				/ cut into vectors and atoms
one:{x,:[&/y _lin F;y;,y]}						/ operator strings -> atoms
two:{@[x;i;:[;y]]_di -1+i:&0{&/x~'(z;y)}[y]':x}				/ 2 char operators -> 1
min:{@[x;&{((y _in,/O)&~y~")")&x~"-"}':"(",x;:;"_"]}			/ - x -> _ x
neg:{@[x;i;,;x i+1]_di 1+i:&{(y~"_")&(*x)_in D}':x}			/ _ n -> _n

sep:{									/ g(,) -> g(;)
 b:0#i:0;while[i<#x;c:x i						/ loop over tokens
 :[c _in*G;b,:1;c _in G 1;b,:0;c~")";b:-1_ b;(*|b)&c~",";x[i]:";"]	/ , -> ; in scope of g
 i+:1];x}								/ iterate

/ parse:

parse:{T::();,/*|end oper/("";token[x],,Z;,Z;"")}			/ zero trace, parse
end:{~&/x[1 2]~\:,Z}							/ o- and v-stack = Z?
oper:{t:P . class'x[2 1;0];trace[t;x];A[`$t]. x}			/ apply operator to input	
class:{(|/'x _in/:O)?1}							/ syntax class
trace:{T,:,(,/15 -15 15 15$',//'@[y;0;,;" | "]),5$x}			/ append to trace
tree:{,(,ktr x 0),y}							/ parse-tree
ktr:{(K[1],,x)K[0]?x}							/ k translation
A.a:{[j;i;o;v](j;i;o;v)}						/ accept
A.c:{[j;i;o;v](j,*i;1_ i;o;(,*i),v)}					/ constant -> v-stack
A.s:{[j;i;o;v](j,*i;1_ i;(,*i),o;v)}					/ shift operator -> o-stack
A.r:{[j;i;o;v]:[0<n;rp;rn][j;i;o;v;n:V class o 0]}			/ reduce v-stack by *o
rp:{[j;i;o;v;n](j;i;1_ o;tree[o;|n#v],n _ v)}				/ reduce with result
rn:{[j;i;o;v;n](j;i;(-n)_ o;v)}						/ reduce without result

/ evaluate:

calc:{(!.W)sheet'.W[];}							/ recalculate book
sheet:{((!#y),/:\:!#*y)cell[x]''y;}					/ calculate sheet
cell:{:[-3=4:z;.[`.W;x,y;:;eval[x;y;value parse 1_ z]]. x,y;z]}		/ calculate cell
value:{:[("R"~*x)|(*x)_in R;("VALUE";x);~4:x;@[x;1_!#x;_f];x]}		/ insert VALUE
eval:{:[4:z;leaf[x;y;z];(.*z)._f[refer[x;*z];y]'1_ z]}			/ evaluate expression
refer:{:[y~"OF";`$y 1;x]}						/ refer to sheet!
leaf:{(.:;r1c1[x;y];`$)[(|/'(*z)=(D;"R"))?1;z]}				/ evaluate leaf
r1c1:{(x;++rc'[^`.W x;y;(0,z?"C")_ z:(),z])}				/ "RxCy" -> cell
rc:{:[0=#z;!x;1=#z;y;rel[y;z].(0$z _dvl"RC[]")]}			/ z = R/C, R/Cn, R/C[n]
rel:{:["["_in y;x+z;z-1]}						/ relative reference

SUM:+/;AVG:{(+/x)%#x}							/ aggregation
MIN:AND:&;MAX:OR:|;NOT:~:						/ logic
IF:{:[@x;(y;z)x;@[&#x;t,f;:;y[t:&x],z f:&~x]]}				/ conditional
JOIN:{`$($x),$y}							/ catenate strings
INTERSECT:{(*x;x[1]@&x[1]_lin y 1)}					/ intersect cells
UNION:{(*x;?(x;y)[;1])}							/ union cells
RANGE:{(*x;,/,/:\:/x[1]+!:'1+*-':(x;y)[;1])}				/ cell range
AT:{@[y;0;:;x]}								/ sheet reference
VALUE:{:[~4:x 1;_f'+x;cell[*x;x 1]`.W[*x]. x 1]}			/ cell value

/ Excel <-> K

get:{`$$.W x}								/ get sheet
set:{.W[x]:.[$y;(;);{:["="=*x;x;y]};z]}					/ set sheet 
vec:{s:^m:*x[];i:&2=4::'v:,/m;v[i]:+(,/'x[])[;i];.,(`s;s#v)}    	/ vectorize
test:{[n].W[]:{:[2=4:x;1.*n _draw 100;x]}'''.W[];}                      / replicate scalars

\

VB Macros:

Declare Function k Lib "k20" (s, ParamArray a())

Sub kloads()
    s = ???
    For i = 1 To UBound(s)
        k "load", s(i), Worksheets(s(i)).UsedRange.FormulaR1C1Local, Worksheets(s(i)).UsedRange
    Next
    k "!-1"
End Sub

Declare Function k Lib "k20" (s, ParamArray a())

Sub kload()
    k "\l excel.k"
    k "set", "one", Worksheets("one").UsedRange.FormulaR1C1Local, Worksheets("one").UsedRange
    k "set", "two", Worksheets("two").UsedRange.FormulaR1C1Local, Worksheets("two").UsedRange
    k "set", "three", Worksheets("three").UsedRange.FormulaR1C1Local, Worksheets("three").UsedRange
    k "set", "four", Worksheets("four").UsedRange.FormulaR1C1Local, Worksheets("four").UsedRange
    k "calc[]"
    k "!-1"
End Sub