If you are familiar with SQL programming and Excel Macros, you can create programmable queries and access data with Excel Macros or Formula functions. FinJinni adds the following functions to Excel:

SQL-Access Excel Functions

=FJ.READSQL(“Query name”, Server/Database, SQL-command, Dummy/Refresh)

Reads a result set from a SQL database into Excel memory for access from other locations. ).

The first parameter is a unique query name used to identify this query.

The second parameter is the server name followed by a “/” and the database name.

The third parameter is the complete SQL command, which must be either a SELECT statement or an EXEC command that returns a result set.

The fourth parameter is not used by this function but can be used to force Excel to refresh the query: A query is only refreshed when one of its parameters changes. This parameter can be any data type but is most useful as a numeric value that is incremented by a macro, e.g. using a refresh button.

Note: The server must be registered with the FinJinni add-in before it can be accessed.

=FJ.GETCELL(query-cell-address, row, column) 

Reads a single cell value from the result set for the referenced SQL query. The cell-address refers to a FJ.READSQL function. The row and column numbers start with 1.

=FJ.GETCOLUMN(query-cell-address, column) 

Returns an entire column from the result set for the referenced SQL query. The return value is an array. 

=FJ.GETROW(query-cell-address, row) 

Returns an entire row from the result set for the referenced SQL query. The return value is an array. 

Returns an entire row

=FJ.ROWCOUNT(query-cell-address) 

Returns the number of rows in the result set for the referenced SQL query.

=FJ.COLUMNCOUNT(query-cell-address) 

Returns the number of columns in the result set for the referenced SQL query.

Example

If you want to query a single account’s balance as of the current date, you could either pick the result out of a table or you can program a SQL command to run and retrieve the single value desired. To set this up, I am using cells A1-D2 in an example spreadsheet:

A1       =YEAR(TODAY())&”-01-01″

B1        =TEXT(TODAY(),”YYYY-MM-DD”)

C1        = company ID value from FinJinni

D1       = account number

A2       = server/database, e.g. “.\sqlexpress/qbtestdb”

B2        = SQL command formula as follows:

=”select sum(balance) as Balance from vBalanceSheet where company_id = ‘” &$C1& “‘

and account_number = ‘” &$D1& “‘ and trans_date between ‘” &$A$1& “‘ and ‘” &$B$1&  “‘ ”

C2        = formula to retrieve SQL results: =FJ.READSQL(“Q1”,$A$2,$B$2,2)

D2       = formula to read single result value: =FJ.GETCELL(C2,1,1)

Note: Use the FinJinni Query Builder to find which tables/views need to be referenced to retrieve a value. 

General-Purpose Excel Functions  

=FJ.HIDECOLUMN(value)

Conditionally hides the column containing this formula. If “value” evaluates to a nonzero number, the column is hidden. If “value” evaluates to zero, the column is visible. 

=FJ.HIDEROW(value)

Conditionally hides the row containing this formula. If “value” evaluates to a nonzero number, the row is hidden. If “value” evaluates to zero, the row is visible.