SFB Program Features

Structured Formula Builder (SFB) provides the framework and tools for writing and maintaining complicated financial and business formulas. These formulas are often based on business rules expressed as logical (if, and, or) conditions. Here’s a difficult Excel business formula:

=IF(AND($D$7=5,NPV(0.15,$Q$77*0.85,$AE$77*0.85,$AS$77*0.85)*1/($Q$11+$AE$11+$AS$11)>2),NPV(0.15,$Q$77*0.85,$AE$77*0.85,$AS$77*0.85)*1/($Q$11+$AE$11+$AS$11),IF(AND($D$7=5,NPV(0.15,$Q$77*0.85,$AE$77*0.85,$AS$77*0.85)*1/($Q$11+$AE$11+$AS$11)<2),NPV(0.15,$Q$77*0.85,$AE$77*0.85,$AS$77*0.85)*1/($Q$11+$AE$11+$AS$11),IF(AND($D$7=3,NPV(0.15,$Q$77*0.85,$AE$77*0.85)*0.9/($Q$11+$AE$11)>2),NPV(0.15,$Q$77*0.85,$AE$77*0.85)*0.9/($Q$11+$AE$11),IF(AND($D$7=3,NPV(0.15,$Q$77*0.85,$AE$77*0.85)*0.9/($Q$11+$AE$11)<2),NPV(0.15,$Q$77*0.85,$AE$77*0.85)*0.9/($Q$11+$AE$11),IF(AND($D$7=2,NPV(0.15,$Q$77*0.85)*0.8/$Q$11>2),NPV(0.15,$Q$77*0.85)*0.8/$Q$11,IF(AND($D$7=2,NPV(0.15,$Q$77*0.85)*0.8/$Q$11<2),NPV(0.15,$Q$77*0.85)*0.8/$Q$11))))))

You can rewrite complicated Excel formulas as SFB solutions, which express formulas in a language that is easier to understand, modify, and document. The SFB user-interface provides a simplified programming environment and step-by-step procedure for coding complicated formulas. You can compile your SFB solutions into Microsoft COM (Component Object Model) components (DLLs), which you can then call from Excel and other programs (including SFB itself) that support the COM. You can also compile solutions into Microsoft .NET components.

When converting an Excel workbook to SFB, you should only rewrite the complicated Excel formulas; leave data tables and simple calculations performed by Excel in place. From within Excel you call your SFB COM components to perform the complicated calculations. SFB solutions replace the complicated business logic coded in Excel and other programming languages like Visual Basic, Java, and C++.

Program Features

· Variables are easy to create, modify, and document.

· Variables are displayed in a familiar user interface grid of rows and columns for easy access, sorting, searching, categorization, and filtering.

· Integrated end-user and developer help. You can create separate documentation for the developers who will maintain your solutions and the end‑users who will run your solutions.

· Connects to any ODBC data source. Variables in SFB can be linked to values and calculations from external tables: sum, max, min—anything you can compute with SQL select statements or stored procedure.

· Simple variable types: money, string, date, datetime, percent, and number with easy display format options.

· Consistent and powerful scripting language based on Perl for creating both calculations and constraints.

· Help screens and functions that assist you in the creation and editing of formulas. This includes quick views of existing variable names and current values.

· Forward and backward chaining engine that shows each variable’s precedents and dependents. You see how changing one variable affects other variables.

· Simplified testing. Input parameters are presented in an edit grid for quick changes.

· Variable names, data values, formulas, constraints, end-user help, and developer help is all stored in standard relational table format. Powerful reports on SFB solutions can be produced using standard report writers like Crystal Reports and Microsoft Access.

· Unlimited storage capacity for tracking different solutions, solution versions, and sets of input parameter values.

· SFB solutions are stored in any ODBC compliant database, including Microsoft Access and SQL Server.

· Sets of variable names and associated values can be saved locally and transmitted over the internet to other SFB users.

· Solutions can be run offline: Solutions (including changes to formulas and constraint logic) can be transmitted over the internet to any client program that supports COM or .NET components. This includes all Windows development tools like Visual Basic and Delphi, and all Microsoft Office applications including Word, Excel, and Visio. The newest version of any solution can be downloaded from a web server. Users need to be online only long enough to download new or updated solutions, or upload data sets.

· Solutions can be run online: Input parameters values to a solution can be submitted from a web form or internet enabled application using HTTP or SOAP. The web server calls the solution (compiled as a COM component or.NET component), sets the component’s properties to the input values submitted by the client, and returns the results of the solution by calling the component solution’s methods.

Getting Started

To build a financial or business model with SFB, you start by creating an empty set of variables called a solution. You give the solution a name, author, version, and description. You then begin creating variables. The values of the first variables you create are the initial starting conditions of your solution. These initial input parameter values are set by the end-user (or client application) of your solution or retrieved from linked database tables each time your solution is recalculated. After creating the initial input and table-lookup variables you are ready to create your first level of computed (or formula) variables. Each time your solution is recalculated all the formulas are evaluated and the value of each formula variable is set to the result returned by its associated formula.

An SFB formula consists of a series of mathematical and logical statements that are associated with a single variable. Each time the solution is recalculated the result of the formula is assigned to the associated variable. For example, if you create the input variables A and B with the initial values 2 and 3, respectively, you could then create a formula variable named C having the formula A + B. When you recalculate the solution, SFB shows that C is equal to 5.

You continue creating formula variables, building on previously calculated values, initial input values, and table‑lookup values, to whatever level of complexity is necessary to arrive at a final value, or set of values, that represent the output or “result” of your solution.

The simple solution described above has two input variables, A and B, and one formula variable, C, equal to A + B. (Creating variable names like A, B, and C is not recommended; it’s better to use descriptive names like Total_Cost_Improvement or Annualized_Total_Hours.) Suppose you create a fourth input variable, D, and set its value to 4; and a fifth formula variable E, equal to C + D. When you click the Re‑Calc button, SFB recalculates C, which is 5, and then calculates E to be 9 because the formula for E is C + D which is 5 + 4. As your solution grows and becomes more complicated many calculations will depend on the values returned by previous calculations and, more importantly, on the conditional logic in your formulas. Using conditional logic is described below. But first lets look at variable types and data types.

VarTypes

When you create a variable, you assign it one of the following VarTypes:

· Direct Input: The value is set by the end-user (or client application) of your solution.

· Table Lookup: The value is retrieved from an externally linked data source each time the solution is recalculated.

· Formula: The value is the result of a calculation that you write and typically depends on other variables and logical conditions. The value is updated each time the solution is recalculated.

DataTypes

You also assign each variable one of the following DataTypes:

· Number

· Date

· DateTime

· String

· Money

· Percent

· List

Lists

SFB Lists are table-lookup variables that hold an ordered set of values. For example, this List variable contains three colors:

qw(red green blue)

qw is the Perl function to quote words with white space. See Temporary Variables and Lists for details on using Lists.

Direct Input, Table Lookup, and Formula Variables

When you create an SFB variable, you give the variable a name, category, vartype, datatype, and format (i.e., number of decimals to display). Variable names cannot exceed 60 characters and may consist of only letters, numbers, and underscores. Category names are also limited to 60 characters but may contain any character.

SFB is case-sensitive and automatically capitalizes the first letter of the variable names you create. This prevents name conflicts with the keywords used in formulas and SQL statements. For example, if you create a variable named Where, it will not conflict with the SQL keyword where.

Direct Input Variables

For direct input variables, you set the variable’s name, category, data type, format, and initial value.

Table Lookup Variables

When you create a table lookup variable, you will create or use an existing ODBC Data Source Name (DSN) that points to any ODBC compliant data source, such as SQL Server, MS Access, Excel, or CSV text file. The table lookup variable’s value is retrieved from the linked external table each time the solution is recalculated. For simple (single table) lookups use the SFB SQL Builder utility to help you create the SQL select statement that will retrieve the appropriate value(s) from your linked table. You can also link variables to the value (or values) returned by stored procedures.

Each time you click the Re‑Calc button, SFB executes the table lookup variable’s associated SQL select statement or stored procedure and assigns the value(s) retrieved to the lookup variable. Each lookup variable you create can be linked to a different data source by a different DSN (ODBC Data Source Name). There is no limit on the number of different DSNs you can use in a solution.

Note: SFB requires that you use System (not User) DSNs.

Table-lookup variables are linked to a single column of data. However, by assigning the table-lookup variable’s DataType to List you can retrieve multiple rows of data. Typically, your SQL select statement’s where clause determines which rows of the table are retrieved. SFB List values are stored as character strings regardless of the data types retrieved from the linked table. For example, if a List variable’s SQL statement retrieves numeric values from six rows of a table, the table lookup value will be displayed as follows:

qw(24221 45651 72453 35932 33452 33458)

See Temporary Variables and Lists for details on using Lists.

Your table lookup variable’s associated SQL select statement or stored procedure will often require parameters. Here is a parameterized SQL statement:

select [CompanyName] from [Customers] where [CustomerID] = ?; CustID

The current value of the SFB variable CustID replaces the question mark when you recalculate the solution. A semi-colon separates the SQL select statement from the parameters. Multiple parameters are separated by commas. Notice the use of brackets around the column names [CompanyName], [CustomerName], and [CustomerID]. Enclosing column names in brackets prevents conflicts with SFB variables that may have the same names as the fields in your external table. Type SQL keywords like select, from, and where in all lower case to prevent name conflicts with SFB variable names. (SFB is case-sensitive and capitalizes all variable names. For example, if you try to create a variable named select, SFB changes the name to Select.)

This parameterized stored procedure takes two parameters. Notice the use of the exec function.

exec sp_findvalue ?, ?; Total_Cost_Improvement, Annualized_Total_Hours

The values for SFB variables Total_Cost_Improvement and Annualized_Total_Hours are passed to stored procedure sp_findvalue.

Formula Variables

When you create a formula variable, you write one or more mathematical expressions that return a value of the appropriate data type. The possible data types are number, date, datetime, string, money, and percent. Formulas are written in Perl. Although the entire Perl programming language is available to you for writing formulas, only a small subset of the language is usually needed. An SFB developer will benefit from knowing Perl, but it is not necessary to become fluent in Perl to use SFB effectively and build complicated solutions.

The value returned by a formula is often based on conditional logic. For example, here is a simple three-line formula for a variable we will name TestVariable:

return 5 if 'Sam' ne 'Joe';

# This is a comment.

return 10;

A semi-colon separates each statement (except for comments). The semicolon is optional after the last statement. White space, tabs, and line breaks are ignored. Format your formulas any way you like. The (#) symbol begins a line comment. Everything on the line following a (#) symbol is ignored. Comments should be used to explain complicated formulas. Formulas execute from top to bottom or until a return statement executes. This is how to read the formula above: return 5 if the string 'Sam' is not equal to the string 'Joe'; otherwise, return 10.

SFB formulas are case-sensitive. Write if not If, and return not Return.

Because 'Sam' ne 'Joe' is a true statement, the return statement on the first line executes, giving a result of 5 to the formula and causing the rest of the formula to be ignored; the second and third lines of the formula do not execute. Clicking the Re‑Calc button causes this formula to return 5 and, therefore, set the value of TestVariable to 5. TestVariable can now be used in other formulas where it will provide the value 5 within those formulas.

Suppose we create a table lookup string variable named FreeFruit that retrieves the value 'Apple' from a table. And we create a direct input string variable named FavoriteFruit, and also set its value to 'Apple'. The following two line formula for the variable we will name Cost_Of_Fruit returns 30, thereby setting the value of Cost_Of_Fruit to 30 each time you recalculate the solution.

return 30 if FavoriteFruit eq FreeFruit;

return 40;

Notice that the if test above is based on two previously defined variables, FavoriteFruit and FreeFruit. If the user of your solution changes the value of FavroiteFruit from 'Apple' to 'Grape' and recalculates the solution, Cost_Of_Fruit will be set to 40 instead of 30. 'Grape' is not equal to 'Apple' so this time the if test on the first line fails, return 30 is skipped, and the formula continues executing the next statement, return 40.

Essential Perl Operators

Here are the Perl operators you need to write formulas.

Different operators are used for comparing String variables and Numeric variables. Numeric variables in SFB are Number, Money, and Percent. (See DataTypes.)

`==`	`!=`	Numeric equality, inequality. Note the double equal sign.
`eq`	`ne`	String equality, inequality
`<`	`>`	Numeric less than, greater than
`lt`	`gt`	String less than, greater than
`<=`	`>=`	Numeric less (greater) than or equal to
`le`	`ge`	String less (greater) than or equal to

`not`		negation
`and`		AND
`or`	`xor`	OR, exclusive OR

Calling External Functions

External functions can be called from SFB formulas. The functions you can call include the Windows API, COM DLLs, built-in Excel functions like NPV, and XML Web Services. For Windows API functions, refer to the Perl Win32::API module documentation. This SFB formula calls the Windows API function GetTempPath:

use Win32::API;

my $GetTempPath = new Win32::API('kernel32', 'GetTempPath', ['N','P'],'N');

my $lpBuffer = " " x 80;

my $path =$GetTempPath->Call(80, $lpBuffer);

return substr($lpBuffer, 0, $path);

See Temporary Variables for details on using variables like $GetTempPath above.

Calling an external COMF DLL relies on the Perl Win32::OLE module. Here’s an example that calls a COM DLL written in Visual Basic from an SFB formula. A Visual Basic component named prj_dll_add contains a class named DLL_Add. Within the DLL_Add class, a function named 'add' adds two numbers.

use Win32::OLE;

my $ex = Win32::OLE->new('prj_dll_add.DLL_Add');

return $ex->add(3,6);

You can call COM components written in SFB from within other SFB solutions. This example calls a COM component (DLL) created from an SFB solution named Add. See Creating SFB COM Components. The Add component contains variables A, B, and C. A and B are direct input variables and C is a formula variable equal to A + B. This example returns 11. (Single quotes around 'A' and 'B' are unnecessary but prevent variable name conflicts.)

use Win32::OLE;

my $ex = Win32::OLE->new('Add.Add_obj');

$ex->{'A'} = 5;

$ex->{'B'} = 6;

return $ex->C;

Calling built-in Excel functions also relies on the Perl Win32::OLE module. This example calls the Excel function NPV:

use Win32::OLE;

my $excel = Win32::OLE->GetActiveObject('Excel.Application') ||

Win32::OLE->new('Excel.Application', 'Quit'); #get active Excel application

return $excel->NPV(.10, -10000, 3000, 4200, 6800);

The Excel documentation for the SLOPE function states that the arguments are numbers or names, arrays, or references that contain numbers. The Excel on-line help shows this example of a SLOPE function call:

SLOPE({2,3,9,1,8,7,5}, {6,5,11,7,5,4,4})

In Perl, however, brackets[], not braces{}, are used for array references. Therefore, this is the correct way to call the ExcelSLOPEfunction from within an SFB formula:

use Win32::OLE;

my $excel = Win32::OLE->GetActiveObject('Excel.Application') ||

Win32::OLE->new('Excel.Application', 'Quit'); #get active Excel application

return $excel->SLOPE([2,3,9,1,8,7,5],[6,5,11,7,5,4,4]);

Excel functions that accept and return arrays can be called with only one value at a time. (SFB formulas cannot return Lists.) This example returns the value for the GROWTH function evaluated for the array consisting of the single value 10. The Perl (@) symbol is used for arrays. See the Perl Reference Guide for more information on arrays.

return @{$excel->GROWTH([125,200,258,315],[2,4,6,8], [10])};

Win32::OLE can also be used to call other Microsoft Office applications. This example spell checks the value of the string variable named SomeWord:

use Win32::OLE;

my $word = Win32::OLE->GetActiveObject('Word.Application') ||

Win32::OLE->new('Word.Application', 'Quit'); #get active word application or new

my $correct = $word->CheckSpelling(SomeWord);

return "'" . SomeWord . "'" . ' is spelled correctly. ' if $correct;

return "'" . SomeWord . "'" . ' is spelled incorrectly.'

The period is the Perl string concatenation operator.

XML Web Services can be called by using the Perl SOAP::Lite module. This example calls the getStateName web service, located at http://www.userland.com. The function returns Wyoming because it is the 50^th state, alphabetically.

use SOAP::Lite;

# Frontier http://www.userland.com/

my $s = SOAP::Lite

-> uri('/examples')

-> on_action(sub { sprintf '"%s"', shift })

-> proxy('http://superhonker.userland.com/');

my $statename = $s->getStateName(SOAP::Data->name(statenum =>50))->result;

return $statename;

Rounding Numbers

Number, Money, and Percent variables are displayed with up to 8 digits after the decimal point. Numbers are rounded for display, but the actual (un-rounded) values are used in calculations regardless of the number of decimals displayed. The round function can be used to display exact values and eliminate round off errors.

For example, suppose a formula variable named M1 returns 1.32, another formula variable, M2, returns 1.54, and a third formula variable, M3, has the formula M1 + M2. If the decimals display format for all these variables is set to 2, you would see that 1.32 + 1.54 = 1.86. However, if you set all the decimal formats to 1, you would see 1.3 + 1.5 = 1.9, an apparent error. The sum appears to be in error because the actual values of M1 and M2, 1.32 and 1.54, are still used in the calculation M1 + M2 even though 1.3 and 1.5 are displayed. To avoid this round off error, either reset all the decimal formats back to 2, or leave the decimal formats set to 1 and use the round function in the formulas for M1 and M2. The round function can return actual values that have the same number of decimals that the displayed values have. For example, if the decimals format for M1 is 1, then change the return statement in the formula for M1 to:

return round(XXX,1)

where XXX is the expression for the value of M1.

Instead of returning 1.32 and displaying 1.3, M1 will now return and display 1.3. Use round(XXX,0) or just round(XXX) to round off all decimals and display only the rounded integer value.

Dates

When checking whether or not two date variables are equal, use the string eq and