Category Archives: VBA

Testing a DAX measure using Excel functionality – XIRR example

Power Pivot’s DAX is missing many of the Excel functions we’ve come to take for granted, but it’s often possible to simulate them using pure DAX, see here for my attempt at a DAX Percentile function.

Even if the logic behind such attempts is sound (or not), the result is likely to be judged against Excel’s version. Also, in many cases, due to the limitations of DAX, crafting an acceptable solution may not be possible. The attached workbook shows a method to either test, or replace, such a DAX measure using existing Excel functionality.

The example attempts to emulate the XIRR function in DAX and test the result against Excel’s version.

It uses a VBA User Defined Function which can “hook into” a pivot table which has been converted to static  CUBE functions. The UDF can then extract the “cross-tab” filters for each cell and generate a DAX Query call via ADO to get the filtered sub-set from the Power Pivot model. It then applies the Excel XIRR function to this data.

The DAX version of XIRR uses a brute force method to pass a range of rates to a NPV calculation, picking the average of the lowest return(s). In the example there are 3000 odd rates (-100% to 200% with one place of decimals so  …10.1%,10.2% …).

A smaller range would result in a faster measure, but be more likely to fail when the yield is outside that range. Likewise, increasing the range of rates, for example, by increasing the number of decimal points, would add to the time taken to calculate.

So, be warned, this is not a generic solution, it would need to be adjusted for the real-world yields that are likely to be encountered and perhaps modified to allow pre-calculation at lower levels and/or allow for restricted “likely-rate” ranges as a first pass, only going to a wider, slower range for outliers.

You’ll notice a definite lag when filtering the example, made worse by the use of CUBE functions which are not as efficient as single Pivot Table callbacks. So, irregardless of how correct or not the measure is, it’ll be a performance non-runner unless properly tuned. In fact, using the callback to Excel method as the solution rather than as the test, may end up as the only practical way to do this.

Here’s the Excel 2013 version, and here’s an Excel 2010 one.

NOTE: CUBE formulas used with pre-2013 PP Models reference “PowerPivot Data”, this will not be updated to “ThisWorkbooksDataModel” when the workbook is subsequently auto-upgraded to the Excel 2013 Data Model.

Below, is the [xirr] measure, with one supporting [npv] measure and a supporting calculated column. There’s also a calculated column [StandardisedDate] for use by the VBA UDF.

xirr:=IF(
ISBLANK(SUM(Cashflow[Cashflow])),
BLANK(),
CALCULATE(
AVERAGE(RATES[Rate] ),
FILTER(
RATES,
ABS(Cashflow[npv])
=
MINX(
RATES,
ABS(Cashflow[npv])
)
)
)
)

npv:=SUMX(
SUMMARIZE(
Cashflow,
Cashflow[DaysSince],
"grouped CashFlow",
sum(Cashflow[Cashflow])
),
[grouped CashFlow] /
IF(
Cashflow[DaysSince]=0,
1,
POWER(
1+ AVERAGE(RATES[Rate]),
(Cashflow[DaysSince]/365))
)
)

Cashflow[DaysSince]= trunc([Event Date]) - trunc(minx(FILTER(cashflow,( [Asset] = earlier([Asset]) && [Cashflow] <0 )),[Event Date]))

Cashflow[StandardisedDate] = =Date(1901,1,1)+[DaysSince]

Have fun.

Advertisements

SAP RFC_READ_TABLE functionality in HAMMER

The code below is a typical VBA routine used to fetch data from SAP into Excel.

It uses the “SAP.Functions” COM object as exposed by the SAP GUI Client, fetching the data via RFC_READ_TABLE; an automated SE16 in effect.

The credentials required are the same as those you would use to log into your desktop client, and whatever internal tables you can see via SE16, those same tables will be fetchable via RFC_READ_TABLE.

This automated fetching of data is ideal when some self-service reporting is a requirement (you know, standard DW extracts offer most of what you need, but there’s always something missing 🙂 ).

I figured this would be a good candidate as a HAMMER command. Not just to take advantage of HAMMER’s natural table handling but also its multi-threading capability. Being able to spawn one or more background threads (or delegate to HAMMER.exe command line process(es) ) would be very handy for SAP datasmiths.

Problem is, the code below works, and I’ve converted it to VB.NET, made it more generic and added it as a HAMMER command; but I can’t test it, as I no longer have access to a SAP R3 Instance!

The command SAPREADTABLE takes three parameters:

  • a CSV list of SAP logon credentials: System,Client,User,Password,Language
  • a CSV list of table information, 1st argument the table name, the rest field names e.g. KNA1,KUNNR,NAME1,NAME2,LAND1
  • a filter statement (like a SQL where) e.g. LAND1 in (‘DE’,’NL’)

Example:

“Test SYS,600,tom,pwd,EN”,”KNA1,KUNNR,NAME1″,”LAND1 = ‘DE'”

UPDATE: April 29, 2012

Could somebody with access to SAP R3 test this out for me?  Done, tested (found a small bug, now fixed) and working (thanks to a kind person who allowed me access to a test server, you know who are, thanks again).

Fetch the modified latest version below (fixed bug that produced an extra blank column and extra blank row in result table, my typical “1 off” bug when converting from VBA to VB.NET, obviously I’ll never learn 🙂 )

If you get a scary “ABEND – SYSTEM FAILURE” error, don’t panic you haven’t broken the company ERP system, it’s usually due to a malformed filter statement e.g. LAND1=’NL’ (no spaces) rather than LAND1 = ‘NL’.

To download the latest version of the code, go to this page on my website.

Follow the HAMMER tag on  this blog for information on commands and examples (best start with the oldest and work forward …)

Need a pure VBA version, here it is :

SAP RFC_READ_TABLE VBA Example:


Option Explicit
Option Base 0

Public Function RFC_READ_TABLE(tableName, columnNames, filter)

Dim R3 As Object, MyFunc As Object, App As Object

' Define the objects to hold IMPORT parameters
Dim QUERY_TABLE As Object
Dim DELIMITER   As Object
Dim NO_DATA     As Object
Dim ROWSKIPS    As Object
Dim ROWCOUNT    As Object
' Where clause
Dim OPTIONS As Object
' Fill with fields to return.  After function call will hold
' detailed information about the columns of data (start position
' of each field, length, etc.
Dim FIELDS  As Object
' Holds the data returned by the function
Dim DATA    As Object
' Use to write out results
Dim ROW As Object

Dim Result As Boolean
Dim i As Long, j As Long, iRow As Long
Dim iColumn As Long, iStart As Long, iStartRow As Long, iField As Long, iLength As Long
Dim outArray, vArray, vField
Dim iLine As Long
Dim noOfElements As Long

'**********************************************
'Create Server object and Setup the connection
'use same credentials as SAP GUI login
On Error GoTo abend:
  Set R3 = CreateObject("SAP.Functions")
  R3.Connection.SYSTEM = ""
  R3.Connection.Client = ""
  R3.Connection.User = ""
  R3.Connection.Password = ""
  R3.Connection.Language = "EN"

  If R3.Connection.logon(0, True) <> True Then
   RFC_READ_TABLE = "ERROR - logon to SAP Failed"
   Exit Function
  End If
'**********************************************

'*****************************************************
'Call RFC function RFC_READ_TABLE
'*****************************************************

  Set MyFunc = R3.Add("RFC_READ_TABLE")
   Set QUERY_TABLE = MyFunc.exports("QUERY_TABLE")
   Set DELIMITER = MyFunc.exports("DELIMITER")
   Set NO_DATA = MyFunc.exports("NO_DATA")
   Set ROWSKIPS = MyFunc.exports("ROWSKIPS")
   Set ROWCOUNT = MyFunc.exports("ROWCOUNT")

   Set OPTIONS = MyFunc.Tables("OPTIONS")
   Set FIELDS = MyFunc.Tables("FIELDS")

   QUERY_TABLE.Value = tableName
   DELIMITER.Value = ""
   NO_DATA = ""
   ROWSKIPS = "0"
   ROWCOUNT = "0"
   OPTIONS.Rows.Add
   OPTIONS.Value(1, "TEXT") = filter ' where filter

    vArray = Split(columnNames, ",") ' columns
    j = 1
    For Each vField In vArray
        If vField <> "" Then
            FIELDS.Rows.Add
            FIELDS.Value(j, "FIELDNAME") = vField
            j = j + 1
        End If
    Next

   Result = MyFunc.CALL

   If Result = True Then
     Set DATA = MyFunc.Tables("DATA")
     Set FIELDS = MyFunc.Tables("FIELDS")
     Set OPTIONS = MyFunc.Tables("OPTIONS")
     R3.Connection.LOGOFF
   Else
     R3.Connection.LOGOFF
     MsgBox MyFunc.EXCEPTION
     Exit Function
   End If

  noOfElements = FIELDS.ROWCOUNT
  iRow = 0
  iColumn = 0
  ReDim outArray(0 To DATA.ROWCOUNT, 0 To noOfElements - 1)
  For Each ROW In FIELDS.Rows
    outArray(iRow, iColumn) = ROW("FIELDNAME")
    iColumn = iColumn + 1
  Next

'Display Contents of the table
'**************************************
iRow = 1
iColumn = 1

For iLine = 1 To DATA.ROWCOUNT

       For iColumn = 1 To FIELDS.ROWCOUNT
         iStart = FIELDS(iColumn, "OFFSET") + 1
    '       If this is the last column, calculate the length differently than the other columns
         If iColumn = FIELDS.ROWCOUNT Then
            iLength = Len(DATA(iLine, "WA")) - iStart + 1
         Else
             iLength = FIELDS(iColumn + 1, "OFFSET") - FIELDS(iColumn, "OFFSET")
        End If
    '       If the fields at the end of the record are blank, then explicitly set the value
        If iStart > Len(DATA(iLine, "WA")) Then
             outArray(iRow, iColumn - 1) = Null
        Else
            outArray(iRow, iColumn - 1) = Mid(DATA(iLine, "WA"), iStart, iLength)
        End If

       Next

       iRow = iRow + 1
Next

RFC_READ_TABLE = outArray
Exit Function

abend:

RFC_READ_TABLE = Err.Description

End Function

Public Sub Paste_sheet1()
Dim lArray
Dim lAdjust As Long

lArray = RFC_READ_TABLE("KNA1", "KUNNR,NAME1,NAME2", "LAND1 = 'DE'")
If TypeName(lArray) = "String" Then
    MsgBox "Problem calling RFC is it here " & CStr(lArray)
Else

    ' adjust if zero based array
        If LBound(lArray, 1) = 0 Then lAdjust = 1 Else lAdjust = 0

    [Sheet1!A1].Resize(UBound(lArray, 1) + lAdjust, UBound(lArray, 2) + lAdjust) = lArray

End If

End Sub

PowerPivot VBA Refresh Code – Bug Fix

Just a quick post to alert those of you using my PowerPivot Refresh code to a bug in its “refresh a single table” logic. Under certain circumstances, linked tables (i.e. those on the “many” side of a relationship) will fail to refresh if specified individually (fine when part of a refresh all). See here for the details behind the bug, and thanks again to Rob Parker for bringing it to my attention. I’ve updated the sample code with the fix, download it here.

I’ve also tested the code against the just released SQLServer 2010 RC0 version of PowerPivot and it appears to work.

I’ve updated the equivalent PPREFRESH code in HAMMER and this is now part of the tool’s latest release (V1.3.4(Beta)). My previous post, Excel as a Book of Record, previewed the most important new commands available in this release. Alongside those, I’ve also added the following:

  • ISTABLE, if the previous COMMAND’s result  or the previous argument is not a table, this will abend the command sequence.
  • ISARG, as above, but this time checks for an argument (a HAMMER parameter may either be a table, a command or an argument- aka, an ARG).
  • ISOK, previous argument must be the string value “OK”.
  • TABLESARETHESAME, will fail if the last two tables are not identical. Intended mainly for automated regression testing.
  • ARGSARETHESAME, as above. but this time for ARGS.
  • _GUID, will return a globally unique identifier.

Download the latest version of HAMMER from here …

Excel as a book of record.

In the past I’ve talked about Excel as a tool to develop Really Simple Systems. Such “systems” usually occupy the middle ground between continuing to do a task by hand or  investing time/money in using a packaged/bespoke “proper system”.

When such systems are primarily reporting in emphasis, the justification for using Excel is usually straight forward and compelling (and getting even more compelling with the appearance on the scene of Excel 2010’s  PowerPivot.) But, alarm bells sound across the world of professional IT when Excel is proposed as a “book of record” i.e. when it’s to be used to store and maintain a business critical dataset. And, with some considerable justification, the nightmare that is linked (or even worse, shared) workbooks is very real indeed. But yet, businesses continue to do so, and do so quite successfully.

I myself record my business as a series of Excel documents (Invoices, Timesheets, Expenses) in a set of folders (Financial Years subdivided into major customers).  Essentially a document-oriented database.

In the past I simply then used a VBA powered workbook to open the required “documents” and extracted the data necessary for whatever report I required (VAT, year-end etc.).  To better automate (i.e. less bespoke VBA) this task I’ve have made changes to HAMMER to help with this and with similar tasks for clients.

The following list of commands will be added to the next release of HAMMER. (In the meantime these new functions can be previewed here …)

LISTOFFILES

This command takes a single argument, the folder to search, and will return a table of files in that folder and in any sub-folders. The result can then be used to select a list of files for further processing.

Example:

=HAMMER(“C:\a\rss”,”LISTOFFILES”)

_XLTOJSONDOC

This command takes a list of workbooks, opens each one, checks for a list of named ranges and generates a JSON document. The command is intended to be called from within a VBA macro (as opening and closing workbook breaks the “no side effects” rule of UDFs). Most “_” commands such as “_MD5” etc. are likewise intended for “programming use”, but any command beginning with “_XL” must be restricted to macro (i.e. non-UDF) use.

See the example workbook FetchInventory for an example of this function in action. The function takes one argument (the name of the document to load) and expects a table where the last column is the full name of the workbook to open. Any columns in the source table will be copied to the new “JSON document” table with an additional column called “Document” which will hold a JSON document representing the key-name pairs and table(s) extracted from the workbook.

On opening a workbook, it is searched for a named range with the name of document concatenated with “_fields” (e.g. INVENTORY_fields). The value of this range is expected to be a CSV list of fields and tables to load. A single Excel “document” could contain multiple logical documents (each specified by its own “_fields” list) .

See the PartsInventory_bin4 for an example of a multi-document workbook (INVENTORY and EXAMPLE). The EXAMPLE document in this workbook also demonstrates the various types of tables handled.

Example:

lJSONObjects = oHammer.HAMMER(“C:\a\rss\StockTake1″,”LISTOFFILES”,”Select name,fullname from table2 limit 1″,”SQL”,”INVENTORY”,”_XLTOJSONDOC”)

lReturn = oHammer.HAMMERToRange(lJSONObjects,”Sheet2!A27″)

… will output

JSONDOCVIEW

This command is where the previous commands are leading to, i.e. extracting some real information value from your documents. It converts JSON documents into Excel friendly tables. It is, in essence, a Map function as in MapReduce. In a previous example I used a Python Map and a SQL Reduce, here, both Map and Reduce are via SQL (the command uses a series of SQL commands to perform its task).

Before I describe the function let me explain why I use an intermediate JSON format. I could just extract the data directly from each document and either store directly in Excel or create tables in SQLite of Access to hold this data. And in fact, that’s what I would have done in the past (seeExcel as a document-oriented NoSQL database). Now , however, I tend to favour using a free-format (i.e. no need for a fixed database schema) structure like a JSON document, so as the source documents evolve over time (which tends to happen not just during design stages but as the system matures) this will not break older documents.

So, for example, original Invoice workbooks might not have a backing time-sheet while newer Invoices do. As long as new and old documents share a core sub-set of data fields they can continue to be analysed together.

The command takes 5 arguments and a driving table (a record so far for HAMMER commands, most have a max of two arguments). The driving table’s last column is assumed to contain the JSON document to process, columns prior to this (if any) will be output unchanged for each resulting row.

The first argument specifies the name of the “inner” table to fetch (if any). Most real life documents consist of header details (the “outer”document) and one or more tables (“inner” details). Invoices, time-sheets,  stock-takes, all tend to follow this pattern. This command will effectively join each document’s outer details to a single inner table (if more than 1 inner table, a call for each one is required).

The second (field list in SQL format) and third (SQL where predicate format) arguments specify what inner fields to extract (if blank, then all) and what restrictions to impose (if any). So “InvNo, Date”,”InvNo > 12″ would only fetch documents where the InvNo > 12 and only include the InvNo and Date fields.

The fourth and fifth arguments do the same for the outer table (i.e. Header data).

If any of the columns specified  (inner or outer) can not be found, or if the predicates (inner or outer) result in no selection, no error is returned, the document simply returns no rows. Likewise if an inner table is specified and no such table exists, then no rows are returned for that document – in other words this is not an outer join, which is not usually a problem as in most cases a “header” without detail lines is meaningless. If an outer join is required, then extract the headers (outers) and details (inner tables(s)) separately and join using SQL.

Example:

=HAMMER(“Select Name,FullName,Document from invoice_docs”,”SQL”,”table_2″, “[PART NUMBER],QTY”, “QTY >30”, “Bin_Number”, “Bin_Number > 1”, “JSONDOCVIEW”)

would result in:

For more complex JSON objects use the JSON command to incrementally parse the text or use the VBA JSON module within microETL.  [UPDATE: Feb 2012 – … or use the JAVASCRIPT command]. But for most situations (especially if you control the expected format) JSONDOCVIEW should handle it.

As JSON is fast becoming the preferred transport format for web and mobile applications having the ability to parse and produce JSON form within Excel is very useful. It is possible, for example, to use a simple web technology such as http://robla.net/jsonwidget/ to craft another type of Really Simple System. This time with the collection happening on the web (most likely using AWS S3 pre-signed forms, so no HTML server required – keep it simple) but with the control and reporting remaining within Excel (a variation on my Steam Powered Server idea).

For an example of a really simple system  download this.

Latest version of HAMMER including the above commands now released …

HAMMER Alongside, as a COM Server

Although it has always been possible to call HAMMER from within VBA via the Application.Run method, this is a somewhat clunky way of doing so and it can also be very inefficient, particularly for tight loops. But now, with this release (V 1.2.0 (Beta) ) core functionality is exposed as a COM Server, which means easier and more efficient interfacing between VBA and the .NET multi-threaded enabled world of the datasmith’s HAMMER.

I call this “HAMMER Alongside”, to differentiate it from HAMMER Inside where I use HAMMER internals to craft stand alone XLLs. With this COM Server method, the standard HAMMER add-in (installed or just-in-time registered) is needed alongside either a VBA add-in or a VBA enabled workbook. The extra “moving parts” are easily justified by the extra flexibility that this method allows (particularly to those with a reasonable grasp of VBA, but perhaps lacking any familiarity with the .NET world).

By learning a small amount of IronPython (here’s a good starting place) it’s relatively easy to hook up VBA to any .NET library through HAMMER, without having to invest time and money in learning heavy duty development environments such as Visual Studio. Using “Internal Threads, it’s also possible to take advantage of .NETs multi-threading capability from VBA. (Note: both these options require .NET4).

So with VBA becoming a first-class language with regards to HAMMER, why offer Python as an alternative scripting choice? Well, one reason is to offer a means to access .NET’s power as per the last paragraph. But, the primary reason, is related to the ability of HAMMER transformations to be “detached” from Excel and run via HAMMER’s command line tools. The .NET 4 version of the command line tool supports Python, so it is possible to initially build out a micro-ETL transformation within Excel (using Python to perform the business logic alongside SQL) and then transfer that logic to the command-line tools with a minimum of modification.

This could be useful , for example, when a PowerPivot model moves to the server, its related HAMMER-powered micro-ETL processing could do likewise (most enterprise ETL tools support call-outs to command-line processes).

The COM server can be accessed from VBA only via late-binding like so:

Set comServ = CreateObject(“hammerCOMServerV1”)

The server’s methods are:

  • hammer(…) – works the same way as the UDF version.
  • hammerVersion() – returns HAMMER version, again the same as UDF version.
  • hammerVersionOK(version) – e.g.  isOK= comServ.hammerVersionOK(120) will return TRUE if the current version is >= V1.2.0
  • arrayResize(anArray) enables the creation of “toFit” UDFs.
  • arrayToSheet(anArray) likewise for “toSheet” UDFs.
  • arrayToRange(anArray,pasteToWhereString) enables “toRange” UDFs.
  • hammer_ppRefresh_inline(optional table,optional timeout) – refreshes a PowerPivot model, again the same as its UDF equivalent.

Download the latest version of HAMMER from here …