define DAVG, 1
define DCOUNT, 2
define DMAX, 3
define DMIN, 4
define DSTD, 5
define DSUM, 6
define DVAR,7

REM : LOTUS SUPPORT MACROS
REM :
REM : This file contains an entry for every lotus function. Some
REM : functions are unimplemented because they have direct Schema2
REM : equivalents; some are not implemented because they are
REM : too system-depeneent; whilst others are represented by macros
REM : in this file.
REM :
REM : A LOTUS standard function called XXX is represented by a macro
REM : called L_XXX
REM :
REM : @@            Not translated
REM :
REM : ABS           Direct
REM : ACOS          Direct
REM : ASIN          Direct
REM : ATAN          Direct
REM : ATAN2         Direct
REM : AVG           Direct (Average)
REM : CELL          Not translated
REM : CELLPOINTER   Not translated
REM : CHOOSE        Not translated (has variable number of parameters)
REM : CLEAN         Translated as L_CLEAN
REM :

macro L_CLEAN(x)
FUNCMACRO
=x
endmacro

REM : CODE:               Direct (ASC)
REM : COLS:               Direct (SECOND)
REM : COS:                Direct
REM : COUNT:              Direct
REM : CTERM               Implemented as L_CTERM

macro L_CTERM (i,f,p)
if (i <= 0) or (f <= 0)  then ="Bad parameters to L_CTERM"
= ln(f/p)/ln(1+i)
endmacro

REM : DATE                Implemented as L_DATE

macro L_DATE(y,m,d)
=ttime(y+1900,m,d,0,0,0)
endmacro

REM : DATEVALUE:           Not implemented

REM : String matching with wildcards for data base operation

macro crm_wildcards(a,b)
local j,length,m
length = if len(a)<len(b) then len(a) else len(b) endif
for j = 1 to length
    m = mid(b,j,j)
    if m = "?" then goto nextone
    if m = "*" then =1
    if m <> mid(a,j,j) then =0
nextone:
next j
=(len(a) = len(b))
endmacro

REM : Comment on all data base functions:  Criteria are strings,
REM : strings with wildcards, or exact values. Formulae cannot
REM : be handled, but instead we allow relations. A relation
REM : is written as a two-letter code (one of LE, LT, EQ, NE,
REM : GE or LT) followed by a value.

macro Dselect(data(),l(),criterion())
local q,j,k,p,z,f,s,res
local list(second(criterion))
for z = 0 to second(criterion) -1
   list(z) = -1
   for q = 0 to second(data) -1
     if criterion(0,z) = data(0,q)  then list(z) = q
   next q
   
next z
f=cell(data,"Y")
s=cell(l,"Y")
=crm_succeeds( data,criterion, s-f, list)

endmacro


macro crm_is_empty(z(),x,y)
= (len(cell(z(x,y)><z(x,y),"expression")) = 0)
endmacro

macro crm_succeeds( data(),criterion(), row, list())
local z,q,r,yes,start,value,res
for r = 1 to first(criterion) -1
   yes = 1
   for z = 0 to second(criterion) -1
   if yes = 0 then goto giveup
      if list(z) >= 0 and crm_is_empty(criterion,r,z) = 0 then
          if type(criterion(r,z)) = 3 then
              start = left(criterion(r,z),2)
              value = 0+right(criterion(r,z),len(criterion(r,z))-2)
              if (start = "LE") then
                yes = (data(row,list(z)) <= value)
              else
                 if (start = "LT") then
                    yes = (data(row,list(z)) < value)
                 else
                    if (start = "EQ") then
                       yes = (data(row,list(z)) = value)
                    else
                       if (start = "NE") then
                          yes = (data(row,list(z)) <> value)
                       else
                          if (start = "GE") then
                             yes = (data(row,list(z)) >= value)
                          else
                             if (start = "GT") then
                                yes = (data(row,list(z)) > value)
                             else
                                 yes = yes and crm_wildcards(data(row,list(z)),criterion(r,z))
                             endif
                          endif
                       endif
                    endif
                 endif
             endif
          endif
          if type(criterion(r,z)) <> 3 then
              yes = yes and (data(row,list(z))=criterion(r,z))
          endif
      endif
   next z
   if yes = 1 then =1
giveup:
next r
=0
endmacro




macro crm_data_base(data(),col,criterion(),type)
local q,j,k,p,z
local list(second(criterion))
for z = 0 to second(criterion) -1
   list(z) = -1
   for q = 0 to second(data) -1
     if criterion(0,z) = data(0,q)  then list(z) = q
   next q
next z
q=0
k=0
p=0
for j = 1 to first(data)-1
   if crm_succeeds(data,criterion,j,list) then
      k=k+1
      p = p+data(j,col)
      if type = DAVG or type = DSUM then q=q+data(j,col)
      if type = DCOUNT then q=q+1
      if type = DMAX and (k=1 or data(j,col) > q) then q = data(j,col)
      if type = DMIN and (k=1 or data(j,col) < q) then q = data(j,col)
      if type = DSTD or type = DVAR then q = q+data(j,col)^2
   endif
next j
if k=0 then = "No records found"
if type = DAVG then q=q/k
if type = DVAR then q = q/k - (p/k)^2
if type = DSTD then q = sqrt(q/k-(p/k)^2)
=q
endmacro




REM : DAVG                 Implemented as L_DAVG

macro L_DAVG (data(),col, criterion())
 = crm_data_base(data,col,criterion, DAVG)
endmacro



REM: DAY:             Direct

REM : DCOUNT                 Implemented as L_DCOUNT

macro L_DCOUNT(data(),col, criterion())
 = crm_data_base(data,col,criterion, DCOUNT)
endmacro


REM : DDB                  Implemented as L_DDB

macro L_DDB(cost,salvage,life,period)
   local cv(life+1),dep(life)
   local q
   cv(0) = cost
   for q = 0 to life-1
      dep(q) = cv(q)*2/life
      cv(q+1) = cv(q) - dep(q)
      if cv(q+1) < salvage then
         dep(q) = dep(q) - salvage+ cv(q+1)
         cv(q+1) = salvage
      endif
   next q
   if period <= life then = dep(period-1) else =0
endmacro

REM : DMAX                 Implemented as L_DMAX

macro L_DMAX (data(),col, criterion())
 = crm_data_base(data,col,criterion, DMAX)
endmacro

REM : DMIN                 Implemented as L_DMIN

macro L_DMIN (data(),col, criterion())
 = crm_data_base(data,col,criterion, DMIN)
endmacro

REM : DSTD                 Implemented as L_DSTD

macro L_DSTD (data(),col, criterion())
 = crm_data_base(data,col,criterion, DSTD)
endmacro

REM : DSUM                 Implemented as L_DSUM

macro L_DSUM (data(),col, criterion())
 = crm_data_base(data,col,criterion, DSUM)
endmacro

REM : DVAR                 Implemented as L_DVAR

macro L_DVAR (data(),col, criterion())
 = crm_data_base(data,col,criterion, DVAR)
endmacro

REM :  ERR                 Implemented as L_ERR
macro L_ERR
="ERR"
endmacro

macro L_EXACT(a,b)
if type(a) <> 3 or type (b) <> 3 then = "ERR"
= (a=b)
endmacro

REM : EXP:                  Direct

REM : FALSE               Implemented as L_FALSE
macro L_FALSE
=0
endmacro

REM :FIND:                Implemented as L_FIND
macro L_FIND(a,b,c)
   local q
   q = find(right(b,len(b)-c),a)
   if q > 0 then =q+c-1
   ="ERR"
endmacro

REM FV:                   Implemented as L_FV
macro L_FV(a,b,c)
local q
q = (b+1)
= a * (q^c-1)/b
endmacro

REM HLOOKUP:               Implemented as L_HLOOKUP

macro L_HLOOKUP(x,t(),k)
local q,w
q = -1
if type(x) =3 then
   for w = 0 to second(t)-1
      if x = t(0,w) then q = w
   next w
   else
   for w = 0 to second(t)-1
      if t(0,w) <= x then q = w
   next w
endif
if q < 0 then = "ERR"
= t(k,q)
endmacro

REM : HOUR:                 Direct

REM : IF                     Handled by L_IF
macro L_IF(a,b,c)
   if a then =b else =c
endmacro

REM : INDEX                 Provided by L_INDED
macro L_INDEX (t(),x,y)
=t(y,x)
endmacro

REM : INT                  Direct

REM : IRR                  Provided by L_IRR
macro L_IRR(a,b())
= irr(b)
endmacro

REM : ISERR                Provided by L_ISERR
macro L_ISERR (x)
 = (x = "ERR")
endmacro

REM : ISNA                Provided by L_ISNA
macro L_ISNA(x)
 = (x = NA())
endmacro

REM : ISNUMBER            Provided by L_ISNUMBER
macro L_ISNUMBER(x)
  = (type(x) = 1 or type(x)= 2)
endmacro

REM : ISSTRING             Provided by L_ISSTRING
macro L_ISSTRING(x)
   = (type(x) = 3)
endmacro

REM :LEFT                  Direct
REM :LENGTH                Direct (len)
REM :LN                    Direct
REM :LOG                   Direct
REM :LOWER                 Direct
REM :MAX                   Direct
REM :MID                   Provided by L_MID
macro L_MID(a,b,c)
 = mid(a, b, b+c-1)
endmacro

REM :NIN                   Direct
REM :MINUTE                Direct
REM :MOD                   Provided by L_MOD
macro L_MOD(a,b)
=a%b
endmacro

REM : MONTH :              Direct
REM : N :                  Provided by L_N
macro L_N (t())
if type(t(0,0)) = 3 then =0
=t(0,0)
endmacro

REM :  NA  :               Provided by L_NA
macro L_NA
= "NA"
endmacro

REM: NOW :                 Direct
REM :NPV :                 Direct
REM : PI                   Provided by L_PI
macro L_PI
= 3.14159265358979324
endmacro

REM : PMT                  Provided by L_PMT

macro L_PMT (a,b,c)
= -pmt(b,c,a)
endmacro

REM : PROPER               Provided by L_PROPER
macro L_PROPER(x)
local a,b,c,y

b=1
c = ""
for a = 1 to len(x)
   y = mid(x,a,a)
   if b=1 then
       c = c cat upper(y)
   else
       c = c cat lower(y)
   endif
   if lower(y) >= "a" and lower(y) <= "z" then
      b = 0
   else
      b = 1
   endif
next a
=c
endmacro

REM : PV                   Provided by L_PV
macro L_PV(a,b,c)
=-pv(b,c,a)
endmacro

REM : RAND                 Provided by L_RAND
macro L_RAND
=rand(1)
endmacro

REM : RATE:                Provided by L_RATE
macro L_RATE(a,b,n)
  =(a/b)^(1/n)-1
endmacro

REM : REPEAT               Provided by L_REPEAT

macro L_REPEAT(a,b)
local x,y
x =""
if (b < 1) then =x
for y = 1 to b
  x = x cat a
next y
=x
endmacro

REM : REPLACE              Provided by L_REPLACE
macro L_REPLACE(a,b,c,d)
local z
z = len(a)-b-c
= left(a,b) cat d cat right(a,if z>=0 then z else 0 endif)
endmacro


REM : RIGHT                 Direct
REM : ROUND                 Direct

REM : ROWS                  Direct (FIRST)
REM : S                     Provided by L_S
macro L_S(t())
if type(t(0,0)) <> 3  then =""
= t(0,0)
endmacro
REM : SECOND:               Direct (SECONDS)
REM : SIN:                  Direct
REM : SLN:                  Direct
REM : SQRT:                 Direct
REM : STD:                  Direct (STDEV)

REM : STRING                Provided by L_STRING
macro L_STRING (a,b)
local j
if b < 0 or b > 15 then b=15
j = "%0." cat b cat "f"
=format(a,j)
endmacro

REM : SUM:                  Direct

REM : SYD                   Provided by L_SYD
macro L_SYD(cost,salvage,life,period)
=((cost-salvage)*(life-period+1))/(life*(life+1)/2)
endmacro

REM : TAN:                  Direct
REM : TERM                  Provided by L_TERM
macro L_TERM(payment,interest,future)
=ln(1+(future*interest/payment))/ln(1+interest)
endmacro

REM : TIME                  Provided by L_TIME
macro L_TIME(h,m,s)
=ttime(0,1,1,h,m,s)
endmacro

REM : TIMEVALUE             Not provided

REM : TRIM                  Provided by L_TRIM
macro L_TRIM(x)
local a,b,c,d
a=""
b = 0
for c = 1 to len(x)
   d = mid(x,c,c)
   if d <> " " then
     a=a cat d
     b=1
   else
     if b = 1 then a = a cat d
   b=0
endif
next c
if right(a,1) = " " then a = left(a,len(a)-1)
=a
endmacro

REM : TRUE:             Provided by L_TRUE
macro L_TRUE
=1
endmacro

REM :  UPPER:           Direct

REM : VALUE             Provided by L_VALUE
macro L_VALUE(x)
= L_TRIM(x) + 0
endmacro

REM : VAR               Provided by L_VAR
macro L_VAR(t())
= stdev(t) ^ 2
endmacro

REM : VLOOKUP           Provided by L_VLOOKUP
macro L_VLOOKUP(x,t(),k)
local q,w
q = -1
if type(x) =3 then
   for w = 0 to first(t)-1
      if x = t(w,0) then q = w
   next w
   else
   for w = 0 to first(t)-1
      if t(w,0) <= x then q = w
   next w
endif
if q < 0 then = "ERR"
= t(q,k)
endmacro
REM : YEAR             Direct (YEARS)

