Microsoft Excel 2010 : UDFs

Programmability Improvements in Excel 2010

Joseph Chirilov — Thu, 28 Jan 2010 06:01:00 GMT

Thanks to Danny Khen for putting together this post.

What’s new?

Excel has been a platform for developing business solutions for a long time now. Developers write code to customize and extend Excel in many ways – automating Excel operations, user interface customizations, data entry forms and extending Excel formulas with user defined functions, to name some key ones. Many developers create very complex applications, which have demanding requirements and push Excel to its limits, so developers are always looking for more ways to get the job done.

With Office 2010 right around the corner, we thought it would be useful to mention the various ways we’ve improved Excel’s extensibility this time around.

I’ll touch the following in this post:

Asynchronous UDFs (user-defined functions)
Running UDFs remotely on an HPC (high-performance computing) cluster
Migrating Excel 4 Macros to VBA
Macro recording for charts and shapes
64-bit support and code compatibility
Open XML SDK improvements

Asynchronous UDFs (User-Defined Functions)

Say you have a UDF that takes a long time to complete, but does not actually perform local intensive processor operations. For example, a UDF that brings back a stock quote from a public web service. An Excel calculation thread calls UDFs in a serial fashion, one after the other, waiting for each call to complete before proceeding down the calculation chain. Therefore, if the UDF is called many times on a worksheet, a calculation cycle could needlessly take a very long time – an order of N of the time of a single call completion. Multithreaded recalculation will not do the trick either. It cannot scale very well for this case, because multiple threads have their overhead, and therefore it is not practical to define as many threads as you have calls to the UDF on the sheet.

Excel 2010 enables you to author these non-processor-intensive UDFs as asynchronous. This ability is supported in XLL add-ins, and the new Excel 2010 SDK has all you need for writing asynchronous UDFs. Essentially, you break your UDF in two: a synchronous function call, which sets up the asynchronous calculation, data request, external web service call etc., and returns immediately; and an asynchronous part, which returns the result to Excel when it is ready. Excel tracks pending UDF calls that have not yet completed, and continues independent parts of the calculation. At some point, the UDF call result becomes available to the add-in (this is typically triggered by an event, such as an incoming web service result, an asynchronous I/O operation completion, etc.). The add-in then calls back into Excel with the UDF result.

Sounds complex, but it’s really not that hard (assuming you know your way around XLL development). Let’s say your XLL add-in has a “normal”, synchronous UDF, which takes one pointer to an XLOPER12, and returns the same:

LPXLOPER12 WINAPI MyNormalFunc(LPXLOPER12 oper)

In order to turn it into the first (callable) part of an asynchronous UDF, you remove the return value (since the result is going to be returned asynchronously later on), and you add a trailing parameter (we will explain its purpose in a bit):

void WINAPI MyAsyncFunc(LPXLOPER12 oper, LPXLOPER12 asyncHandle)

To register this asynchronous UDF, you designate the return value type (void) with a “>” in the function’s type string – the string where a character code specifies the type of each parameter:

...

LPXLOPER12 pxTypeText = L">QX";

...

Excel12(xlfRegister, &xResult, 5, &xModuleText, pxProcedure,

pxTypeText, pxFunctionText, pxArgumentText);

The function’s new trailing argument is an XLOPER of type xltypeBigData and is used for an asynchronous call handle. It is invisible on the UDF call in the Excel formula. The Excel model author calls it with a single argument, just like the original synchronous function used to be called. Excel passes a unique handle to the function for each call during a calculation cycle, and uses the handle to track its progress. Your add-in should keep track of each instance of this handle. Later on, when the result of this particular UDF call is ready, your add-in code uses the handle to call back into Excel, using the new callback xlAsyncReturn, in order to provide the result (and after this point, the add-in can discard the handle). In our example, this should look something like the following:

XLOPER12 xlResult;

LPXLOPER12 pxHandle; // make it point to the async handle tracking the call

LPXLOPER12 pxResult; // make it point to your UDF call result

...

Excel12(xlAsyncReturn, &xlResult, 2, pxHandle, pxResult);

The benefit? A big boost in calculation speed – an order of one calculation time, instead of an order of N. If a single stock quote takes 1 second to come back from a web service, and you have 100 calls (assuming abundant resources on the web service side), this means 1 sec instead of 100 sec – really significant.

The beta version of the new Excel 2010 XLL SDK is available online here. It has all the source files and documentation you need to create asynchronous UDFs.

Running UDFs Remotely on an HPC (High-Performance Computing) Cluster

Many organizations with high computation power demands have high-performance compute clusters. These are large arrays of processors (“nodes”), with infrastructure to submit computation jobs to the cluster nodes and collect results. Highly concurrent calculation models can greatly benefit from HPCs.

Say you have a UDF with a very heavy calculation, called many times on some Excel worksheets, with the calls mostly independent from one another. Turning the UDF into asynchronous will not really help calculation speed; all calls still need to calculate on however many processors your computer has, whether synchronously or not. But if you have an HPC cluster at your disposal, and if you could send those function calls off to be calculated on the cluster, it would greatly boost the calculation speed of your sheet. Creating and managing HPC jobs, however, is not an easy task and could require nontrivial coding.

Enter Excel 2010. HPC admins can now deploy XLL add-ins with supported UDFs (a word on that in a bit) to the cluster’s compute nodes. Excel users can configure a compute cluster to be used in calculations, using simple UI in Excel’s options:

The user turns on “Allow user-defined XLL functions to run on a compute cluster”, selects the type (typically there would be one – the type of HPC cluster that the organization has), and, in the “Options” dialog, sets the host name for the cluster’s head node.

With that in place, Excel automatically sends UDF calls off to the cluster asynchronously, waits for results, and plugs them back into the Excel model – all without the need to modify anything in the Excel model!

Supported UDFs are XLL UDFs, which are registered by the add-in as “cluster-safe,” meaning that they are essentially independent calculations – they are stateless, and do not call back into Excel. A cluster-safe UDF should not perform any operations that rely on the state of the local machine (storing off-process data, open connections, etc.). Once a developer verifies that a UDF is cluster-safe, registering the UDF as such is just a tiny modification to the add-in code – adding another flag to the registration call. There is no need to modify anything in the function code itself to support communication with the HPC cluster or management of the remote call. All there is to do is to add an “&” to the registration’s type string:

...

LPXLOPER12 pxTypeText = L"QQ&";

...

Excel12(xlfRegister, &xResult, 5, &xModuleText, pxProcedure,

pxTypeText, pxFunctionText, pxArgumentText);

Obviously, if your existing UDF is not cluster-safe, you’d need to rewrite it in such a way that it is, in order to register it as cluster-safe and take advantage of automatic call offloading to HPC clusters.

To summarize: Cluster-safe UDFs are offloaded to a high performance compute cluster, without the need to modify the Excel model, and without the need to rewrite the UDFs to use a cluster. Pretty neat!

Look for a dedicated post about this feature on this blog in the near future.

Migrating Excel 4 Macros to VBA

Prior to the introduction of VBA into Excel, we had our own macro facility – known as Excel 4 macros, used in Excel macro sheets. Most people have long since migrated their Excel 4 macros to VBA; however, some Excel 4 macro capabilities were missing from VBA, which made this migration difficult.

In Excel 2010, one of our goals was to remove any remaining barriers that people had to complete the migration of Excel 4 macros to VBA. This work generally fell into two categories:

Adding new objects, methods and properties to Excel’s object model for the few remaining Excel 4 macro functions that did not have equivalents in VBA.
Performance Improvements for Page Setup operations in VBA to provide similar performance to PAGE.SETUP() functionality in Excel 4 Macros.

We have completed this work in Excel 2010 and will be devoting an entire blog post to this subject in the near future.

Macro Recording for Charts and Shapes

When Excel 2007 introduced a new charting engine into the product, the focus was on modernizing chart and shape rendering capabilities. This was a big investment, and we had to make some painful prioritization decisions. This resulted in Excel 2007 losing some compatibility with Excel 2003 in terms of the chart and shape object model, and as a result, macro recording capabilities.

In Excel 2010, macro recording is alive and well again for chart formatting. The vast majority of chart formatting recording capabilities from Excel 2003 are back in, with very few exceptions (axis text formatting being one of them).

We’ve posted several entries in this blog about charting enhancements in Excel 2010.

64-bit Support and Code Compatibility

Office 2010 will ship in both 32- and 64-bit versions. 64-bit is particularly significant to some Excel applications, which hit a wall today in terms of available memory address space. This is an important topic in itself, which we have covered here.

Excel workbooks can be freely opened and edited in both 32- and 64-bit Excel; there is nothing architecture specific in a saved workbook. For custom code solutions, however, 64-bit Excel introduces some challenges:

ActiveX controls need to be ported – they need a 64-bit version to work in a 64-bit process. This includes Microsoft’s own controls, of which many have been ported. We are evaluating the popularity and criticality of the remaining ones for possible porting.
COM add-ins, similarly, need to be compiled for 64-bit in order to work in 64-bit Excel.
XLL add-ins also need to be compiled for 64-bit, and the new Excel 2010 XLL SDK supports that.
VBA: Embedded VBA code gets re-compiled when the containing Excel workbook is opened on a new platform; 64-bit Excel now includes a 64-bit version of VBA. So most VBA code just works in 64-bit. However, a subset of VBA solutions needs some tweaking. It has to do with declarations and calls to external APIs with pointers/handles in the parameter list. VBA7, the new version of VBA that ships with Office 2010, supports the development of code that can run in both 32- and 64-bit Office.

To give you a taste of what you may need to modify in VBA code that calls external APIs:

The old 32-bit-only compatible declaration:

Declare Function RegOpenKeyA Lib "advapi32.dll" (ByVal Key As Long, ByVal SubKey As String, NewKey As Long) As Long

Needs to change to this 32- and 64-bit compatible declaration, if you want the code to be used in 64-bit Excel:

Declare PtrSafe Function RegOpenKeyA Lib “advapire32.dll” (ByVal hKey as LongPtr, ByVal lpSubKey As String, phkResult As LongPtr) As Long

MSDN has a detailed article about Compatibility Between the 32-bit and 64-bit Versions of Office 2010.

In addition, we now have a tool that looks for and flags possible points of incompatibility in your code – the Microsoft Office 2010 Code Compatibility Inspector. The tool, among other things, flags 64-bit compatibility issues in your code and hints at the solutions.

Open XML SDK Improvements

Open XML is the default file format of core Office applications starting with Office 2007. The Open XML SDK lets developers create and modify Office files in independent code, without loading the actual Office programs or using their OM. This has significant advantages in many cases, notably for server-based applications.

In April 2009, Office released the first CTP of the Open XML SDK Version 2, which bore important additions over V1 – strongly typed classes and objects; content construction, search, and manipulation; and Office file format schema validation.

In December 2009, leading to the release of Office 2010, we have released the 4th CTP for the Open XML SDK 2.0 for Microsoft Office. There were four major improvements made to the SDK:

Full support for the Office 2010 Open XML formats
Office 2010 schema and semantic level validation
General improvements based on a recent Open XML SDK usability study
Open XML SDK tools improvement

Resources

Charting-related posts on the Excel blog.
Excel 2010 – Now With More Bits! Discusses 64-bit support in Excel.
Compatibility Between the 32-bit and 64-bit Versions of Office 2010 expands on the matter.
Microsoft Office 2010 Code Compatibility Inspector is a very useful tool for finding potential issues in your code when migrating to Office 2010.
Excel 2010 XLL SDK is where you can download the beta version of the XLL SDK.
Open XML SDK Version 2 describes the improvements in the Open XML SDK relative to its 1^st release.
The 4th CTP for the Open XML SDK 2.0 for Microsoft Office describes the changes in the latest CTP of the SDK V2, leading to the release of Office 2010, and has download information.

Calculate the ISO Start of Year date

FredK — Fri, 18 Sep 2009 02:32:53 GMT

Today’s author is Shane Devenshire, who has been an Excel MVP for many years and who regularly contributes to Excel Newsgroups. He has published over 300 articles in different computer magazines, and co-authored numerous books. Excel might be better today because of the many bugs he posted when he was a Beta tester! This post shows you how to use a formula or custom VBA function to calculate the ISO Start of Year date.

Here is a formula for calculating the ISO Start of Year date for any legal Excel date.

=("1/4/"&YEAR(B2))-MOD("1/2/"&YEAR(B2),7)

This assumes the date you are entering is in B2. With this formula you enter any date in a year and Excel returns the ISO Start of Year date. For example if you enter 9/12/09 in B2 the result of this formula is 12/29/08.

You can create a custom user-defined function (UDF) using VBA to perform the same calculation. To do that, open the Visual Basic editor, click Module on the Insert menu, and then copy the following function into the module. After adding this UDF to your workbook, you can use it like a built-in function =ISOYS(B4):

'Returns Start of Year date (1st Monday) based on an input date
Public Function ISOYS(mydate As Date) As Date
    Dim D as Long

    D = CDate("1/2/" & year(mydate))
    ISOYS = D + 2 - D Mod 7
End Function

For more information on ISO dates, see the Power Tips post: Week Numbers in Excel.

Week Numbers in Excel

MarkRoberts — Tue, 30 Jun 2009 10:00:00 GMT

Today’s author is, Ron de Bruin, an Excel MVP. You can find more useful tips from Ron and links to Excel add-ins at his website: http://www.rondebruin.nl/

There are four primary week numbering systems in use worldwide. Each system has subtle differences that you should be aware of. Excel can work with any of these systems:

1) ISO Week number: The International Organization for Standardization (ISO) ISO8601:2000 Standard.
All weeks begin on a Monday. Week one starts on Monday of the first week of the calendar year with a Thursday.
2) Excel WEEKNUM function with an optional second argument of 1 (default).
Week one begins on January 1st; week two begins on the following Sunday.
3) Excel WEEKNUM function with an optional second argument of 2.
Week one begins on January 1st; week two begins on the following Monday.
4) Simple week numbering.
Week one begins on January 1st, week two begins on January 8th, and week 53 has only one or two days (for leap years).

Note: Excel does not have a standard worksheet function for the ISO week number and simple week numbering system.

Worksheet Formulas for Week Numbers

The following sections assume that you have a date in cell B4 for testing the week number formulas.

ISO Week Numbers

There is no built-in worksheet function for ISO week numbers in Excel. Copy the following formula and paste it in a worksheet cell to return an ISO week number:

=INT((B4-DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3)+WEEKDAY(DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3))+5)/7)

Alternatively, you can open the Visual Basic editor, click Module on the Insert menu, and then copy this user-defined function (UDF) into the module. After adding this UDF to your workbook, you can use it like a built-in function =IsoWeekNumber(B4)

Public Function IsoWeekNumber(d1 As Date) As Integer
' Attributed to Daniel Maher
    Dim d2 As Long
    d2 = DateSerial(Year(d1 - WeekDay(d1 - 1) + 4), 1, 3)
    IsoWeekNumber = Int((d1 - d2 + WeekDay(d2) + 5) / 7)
End Function

In VBA you could also use:

DatePart("ww",date,vbMonday,vbFirstFourDays)

However, because there is a bug with the VBA DatePart function with respect to ISO week numbers, it is not a good option to use it. Use the VBA IsoWeekNumber function above to avoid problems. For more information on the DatePart method bug, see this KB article: BUG: Format or DatePart Functions Can Return Wrong Week Number for Last Monday in Year.

You can find more information about ISO dates and week numbers on this page: http://www.rondebruin.nl/isodate.htm

The Excel WEEKNUM Function

Reliance on the Analysis Toolpak in Excel versions before Excel 2007 can create problems because the add-in may not
be installed by users of your spreadsheets (a default Excel installation has it unchecked in setup). Also, there are difficulties for international users when you use Analysis Toolpak formulas because these formulas are not translated by Excel if you open the workbook in a different Excel language version.

Note: In Excel 2007 WEEKNUM is a standard built-in worksheet function, so you will not have the problems above if you share your workbook between different Excel 2007 language versions.

Important: If you do not have Office 2007 SP2 installed, read the information on this page: http://www.rondebruin.nl/atp.htm

You can use these two replacement functions from Daniel Maher to avoid problems.

Replace =WEEKNUM(B4,1) with:

=1+INT((B4-(DATE(YEAR(B4),1,2)-WEEKDAY(DATE(YEAR(B4),1,1))))/7)

Replace =WEEKNUM(B4,2) with:

=1+INT((B4-(DATE(YEAR(B4),1,2)-WEEKDAY(DATE(YEAR(B4),1,0))))/7)

Simple Week Numbers

There is no built-in worksheet function for simple week numbering in Excel. Copy the following function and paste it in a worksheet cell to return simple week numbers:

=INT((B4-DATE(YEAR(B4),1,1))/7)+1

The Week Calendar File

The week calendar file shows you all the dates and week numbers from a certain year on one printable page. If you want to have a week calendar from another year you only have to change one cell (the year). There is a separate sheet for each of the four week numbering systems listed at the start of this post.

Download the Calendar file
Week Numbers Calendar.zip (File date : 27-Feb-2005)

Acknowledgements

I would like to acknowledge general reference on all date issues to:
Chip Pearson: http://www.cpearson.com/excel/topic.aspx
Dave McRitchie: http://www.mvps.org/dmcritchie/excel/xlindex.htm
Daniel Maher has also published numerous simplifications of date formulas some of which were used or adapted in producing the calendar.
The late Frank Kabel Frank created the ISO week number worksheet function on this page.
The base formula used for the ISO year start in the calendar file were derived from a UDF written by John Green, Sydney.

More Information
ISO Date Representatation and Week Numbering:
http://www.rondebruin.nl/isodate.htm
You should refer to Chip Pearson's web site for an exposition on Week Number implementation:
http://www.cpearson.com/excel/weeknum.htm
Implementing Week-Numbering Systems and Date/Time Representations:
http://msdn.microsoft.com/en-us/library/bb277364.aspx

Building an Asset Tracking Application in Excel Services – Part 3b of 5

Joseph Chirilov — Tue, 01 Jul 2008 08:34:00 GMT

Today's author, Dan Parish, continues his discussion on building an Excel Services solution.

In Part 3a I walked through how to create the SharePoint List that stores the data users enter. In this post, I will walk through how to create the Excel Services User Defined Function (UDF) that will pull this information from the SharePoint List into Excel Services.

Overview

Out of the box, Excel Services does not support pulling in data stored in SharePoint Lists. However, you can add this functionality using one of Excel Services' extensibility methods: User Defined Functions.

User Defined Functions (UDFs) are custom worksheet functions that can be used to extend the capabilities of Excel Services. Once implemented they work just like any other function, except that they run your own code. For more information on UDFs, see the blog posts Excel 2007 investments in UDFs Part 1, Part 2, and Part 3.

While you can build UDFs that work on both the client and server, for this post we are just going to create one that works on the server and that fetches the information from the SharePoint List and returns it into the Excel workbook.

The code for the UDF and the workbook is included at the end of this post.

Writing the code

Before you can write any code, the first thing you'll need to do is to create a Visual C# Class Library in Visual Studio. Once you've done that, you'll need to add references to Microsoft.Office.Excel.Server.Udf.dll, and Microsoft.SharePoint.dll (both of which are located your Office SharePoint Server's ISAPI folder).

When writing a UDF, the first step is to mark the class as a UDF class, like so:

…

[UdfClass]

public class Class1

{

…

This ensures that Excel Services knows that there are UDF functions within this class. If you remember back to Part 2 of this series, the function that we want to create is called GetMachines, and it takes as a parameter a string that specifies whether we want data for laptops or for desktops. It returns an array of values which we then input into our spreadsheet using an array formula. The function definition then looks like:

…

[UdfMethod(IsVolatile = true, ReturnsPersonalInformation = true)]
public object[,] GetMachines(string MachineType)
{
}

…

You'll notice that I've marked it as a UDFMethod which tells Excel Services that this is the function that will be usable in your worksheet. Setting it as volatile ensures that it calculates all the time and won't have its values cached by Excel Services, while saying that it returns personal information is required to impersonate the user that is loading the workbook, which we do in order to access the SharePoint List as them, and not as the Excel Calculation Services' process account.

To impersonate the user, we'll use a simple function called impersonateUser(). All it does is try to get the Windows identity of the user accessing the workbook, and if it can, impersonate them. The function is:

// Causes the UDF to impersonate the user that loaded it through the EWA
private WindowsImpersonationContext impersonateUser()
{

WindowsIdentity wi = Thread.CurrentPrincipal.Identity as WindowsIdentity;
if (wi == null)
throw new InvalidOperationException("Can't get Windows identity.");
return wi.Impersonate();

}

Using that function, the first thing we need to do in our GetMachines function is impersonate the user like so:

…

// Impersonate the current user using the EWA and not the ECS
using (WindowsImpersonationContext wiContext = impersonateUser())
{

…

Now that we've created the class and method and impersonated the user, we need to go ahead and get the requested data from the SharePoint List:

…

try
{

// Declare constants
string serverName = http://server/;
string siteName = "site";
string listName = "list";
string viewName = "";

if (MachineType.Equals("Laptops"))

viewName = "Laptops";

else

viewName = "Desktops";

// Get objects corresponding to the SharePoint Site, List, and List View
SPSite site = new SPSite(serverName);
SPList list = site.AllWebs[siteName].Lists[listName];
SPView view = list.Views[viewName];

// Get a collection of the values in the specified List View
view.Update();
SPListItemCollection values = list.GetItems(new SPQuery(view));

// Create an object array to return to the grid
object[,] toExcelGrid = new object[values.Count, view.ViewFields.Count];

…

In this example I am hard coding the server name, site name, and SharePoint List name. These could just as easily be passed in as arguments to the function, but this approach eases the usage for the end user. Also note that I use the MachineType parameter to determine the view that we will be getting list items from. We created the views when creating the SharePoint List in Part 3a, and the views only contain items for the type of machine specified by the MachineType parameter (laptops or desktops).

Now we just need to iterate through the list items, put them into the toExcelGrid object, and then return all that data to the Excel workbook:

…

// Iterate through each of the SPListItems in the view
int a = 0;
foreach (SPListItem currVal in values)
{

// Add every field value of current item to the array that will be returned
for (int i = 0; i < view.ViewFields.Count; i++)

{

// Get the current field
SPField field = currVal.Fields.GetFieldByInternalName(view.ViewFields[i]);

// Add the item to the array
toExcelGrid[a, i] = field.GetFieldValueAsText(currVal[field.Id]);

}

a++;

}

return toExcelGrid;

}

…

Finally, we need to add our catch event to return an error message if something goes wrong:

…

catch (System.Exception ex)
{

object[,] error = new object[1, 1];
error[0, 0] = ex.Message;
return error;

}

…

That's it!

For more details on UDFs, and a step by step walkthrough of how to create one, see the Excel Services User-Defined Functions article on MSDN.

Deploying your UDF

In order to ease management of your UDF files, start by creating a special folder for them on your server, something like C:\UDFs. Next, copy the Debug DLL that you just created into that folder. Now all we need to do is tell Excel Services about it and enable it.

To get to Excel Services' administration pages, click Start > Administrative Tools > SharePoint 3.0 Central Administration. Your default web browser will launch showing Central Admin. Click on the name of your shared service on the left (SharedServices1 by default), and then in the Excel Services Settings section, click on User-defined function assemblies.

This will take you into the UDF management page. Click Add User-Defined Function Assembly to add the one we just created. Enter the file local path to the UDF (C:\UDFs\GetMachines.dll), and pick File Path as the assembly location. Click Ok, reset your server (using iisreset), and your UDF is ready to go!

Testing your UDF

If you've created the SharePoint List and updated the static variables in the UDF correctly, then all you should need to do is upload the workbook we built in Part 2 and you should start see the values from the SharePoint List in your Excel workbook when it is rendered using Excel Services.

Building an Asset Tracking Application in Excel Services – Part 2 of 5

Joseph Chirilov — Tue, 03 Jun 2008 21:45:00 GMT

Today's author, Dan Parish, a Program Manager on the Excel Services team, continues on with his multi-part post about building an application using Excel Services.

In my last post I gave an overview of the asset tracking application that I am going to spend the next four posts describing how to build.

The core of the application is the workbook it is based on. The workbook is what really makes this application work, since it is what pulls in the data users entered, figures out who has and hasn't filled out their machine information, and applies all of the conditional formatting that makes analyzing the data so easy.

This post will detail how the workbook is constructed, but won't get into how the machine information entered by users is pulled into the workbook from SharePoint. That's the next post.

The workbook is available for download in the attachments at the bottom of this post.

Structure of the workbook

The workbook itself consists of three sheets: Employees, Machines, and Data. The Employees sheet has references to the Machines sheet, and the Machines sheet has references to the Data sheet. So, in order to best describe how everything in the workbook works together, I'm going to explain the sheets in reverse order, up the dependency chain.

The Data sheet

The Data sheet is where the data in the SharePoint List is pulled using a User Defined Function (UDF). However for now, since we aren't building that until the next post, I've entered some fake data in for Laptops so you can see the spreadsheet working.

Here's what the sheet looks like with some data in it:

The Laptops portion of the sheet is exactly what it will look like when it is pulling in live data in Excel Services from the SharePoint List using the UDF. The Desktops section shows you what the workbook will look like in Excel.

Both the Laptops section and the Desktops sections are array formulas that call the GetMachines UDF. GetMachines takes one parameter, which is the type of machine to fetch information for. So, for Laptops, the function is {=GetMachines("Laptops")} and for Desktops the function is {=GetMachines("Desktops")}. I'll get into more detail regarding how the UDF works in the next post, but for now, the important thing to note is that even when it is bringing in data (as shown for Laptops), rows that don't have any data will show #N/A.

That's the Data sheet. It pulls in data from the SharePoint List using a UDF, and is then referenced by the Machines sheet.

The Machines sheet

The Machines sheet does three things:

References the data on the data sheet, but eliminates the errors and adds conditional formatting
Averages all of the laptop data and desktop data
Averages the laptop and desktop data for the current user

Here's what the Machines sheet looks like, continuing with the sample data entered above:

The Laptops and Desktops sections on the bottom simply reference their corresponding cells on the Data sheet, but wrap them in IFERROR functions that show blank if there is an error. A sample formula from these sections is the following, for Employee from cell B16: =IFERROR(Data!B5, "").

These sections are shown on the admin web part page, so this helps to keep the display looking nice. There is also a custom conditional formatting rule applied to the data in these sections. The rule colors anything that is < 50% of the average value for all machines red, since if a machine is at or above average it doesn't need upgrading, and this helps our admin to easily spot which components of certain machines are in need of an upgrade.

The Laptops and Desktops sections at the top of the page are shown on the page end users see before and after they enter their machine information. Each of the averages in the Division Averages portion simply uses the AVERAGE function to average all of the values in the corresponding section below. It is wrapped in an IFERROR formula in case there are no values, in which case it shows "No Laptops" or "No Desktops". A sample formula for Average CPUs in cell C5 is the following: =IFERROR(AVERAGE(D16:D30), "No Laptops").

The Your Averages portion averages all of the machines where Employee Name matches the name of the person viewing the web part page that is passed into a cell on the Employee sheet (which I'll discuss in the next section). To do this, it simply uses an AVERAGEIF function, again wrapped in an IFERROR formula in case there are no values. A sample formula for Average RAM in cell F9 is: =IFERROR(AVERAGEIF($B$16:$B$30, EmployeeName, $F$16:$F$30), "No Laptops").

That's all that the Machines sheet does. It references the Data sheet to present its data nicer and with conditional formatting, and does some quick reporting on the data using the AVERAGE and AVERAGEIF formulas.

The Employees sheet

The Employees sheet does two things:

Takes a parameter input for the name of the current user and generates the welcome message shown on the first page
References the Machines sheet to calculate which users have entered their machine information and which haven't

Here's what the Employees sheet looks like, continuing with the sample data from above:

When Excel Services is rendering a workbook in a web part page, you can set it up to receive values into cells specially marked as 'parameters' from other web parts. You can see a previous post of mine titled Using Parameters in Dashboards to understand how to create parameters and connect them to other web parts. I'll also walk through some of that in Part 3 of this series.

On the Employees sheet, the named range EmployeeName, which is located in cell E3, is a parameter. It will be connected to another web part that will send in the name of the current user. By default, the value of the cell is set to Mr. Nobody. You can change that cell to see the workbook start working. For example, enter "Employee #2" and you'll see the welcome message change to "Welcome Employee #2!". If you then switch back to the Machines sheet, you'll see that there are now values in the Your Averages portion of the Laptops section.

The welcome message is created by simply concatenating 'Welcome' with EmployeeName, and then with '!' using the following formula: =CONCATENATE("Welcome ", E3, "!").

The Employee Information section below has three columns: people who have completed the survey, people who haven't, and the full list of employees. The first two columns are shown on the admin's web part page.

The full list of employees is just data entered directly into the spreadsheet. It could be pulled from an external data source, but for this example, I just entered it in manually. Note that it is important that this data is entered in alphabetical order. The list of employees that have completed the survey is simply a unique list of all of the employees that have entered either a laptop or a desktop, and is created by examining the two Employee columns (one in the Laptops section and one in the Desktops section) on the Machines sheet. The list of employees that haven't completed the survey is then the list of employees that are in the full employee list but that aren't in the list of employees that have completed the survey.

Now, if I wasn't as concerned with the presentation of this information, I could stop here. However, to get the presentation I wanted, which has no duplicate entries and no spaces between entries, I needed to do some more advanced formula manipulation.

In order to not clutter up the UI that is shown to the admin, several columns are hidden. When unhidden, you can see that Column A contains a list titled 'Uber List' and Column B contains a list titled 'No Dups'.

The Uber List is the listing of all employee names that are in the Employee columns in the Laptops and Desktops sections on the Machines page. This means that names are repeated, and that there are blank cells followed by cells later that have names. For example, cell A8 contains the following formula: =IF(Machines!B18=0, "", Machines!B18). So, if there is a value in Machines!B18, it fills in the same value, otherwise it fills in a blank space.

The No Dups list is the Uber List with all of the duplicate names removed. This works by only filling in an employee name if that name has only appeared once in the list before it. The following table demonstrates how this works:

Uber List Value	Count of this name earlier in the list	Dup List Value
Dan Parish	1	Dan Parish
Dan Parish	2
Joseph Chirilov	1	Joseph Chirilov
Kerry Westphal	1	Kerry Westphal
Kerry Westphal	2

Each cell in the Dup List column simply counts the occurrences of the cell next to it in the Uber List, and if the number of occurrences is one, it shows the value. If it is anything else, it shows nothing. For example, the formula in cell B10 is: =IF(COUNTIF($A$8:A10, A10)=1, A10, "").

Finally, I wanted to show the Completed Survey column without the blank cells. To remove the blanks cells, I used a technique shown excellently here. This also meant that the Completed Survey column ended up with a named range of CompletedSurveyNoBlanks.

The next thing I needed to do was create the inverse list: the list of people who are in the Full List of Employees, but who aren't in the Completed Survey list. This is done in the hidden Column F. The formula used does exactly what I just described. For each employee in the Full List of Employees, it looks to see if they are listed in the Completed Survey list (using the VLOOKUP function). If they are, nothing is filled into the cell. If they aren't, then the employee's name from the Full List of Employees is filled in. For example, the following formula is used in cell F8: =IF(IFERROR(VLOOKUP(I8,CompletedSurveyNoBlanks,1,FALSE), "") = "", I8, "").

The list we just created of people that haven't filled out the survey has blank cells though. Like for the Completed Survey list, I wanted the Haven't Completed Survey list to not have any blank cells. So, to remove those blank cells I used exactly the same method I used to remove the blank cells for the Completed Survey column.

I then hid Column A-C and Column F, producing the sheet shown above.

Conclusion

As I stated at the beginning of this post, this workbook is really the core of the asset tracking application. The vast majority of the workbook is quite simple to build, and can be maintained by a business user rather than a developer since the entire model is in Excel.

In my next post I'll be writing about how to build the SharePoint List that users will enter their machine data into, and how to write the UDF that pulls that information from the SharePoint List into Excel Services.

Building an Asset Tracking Application in Excel Services – Part 1 of 5

Joseph Chirilov — Wed, 28 May 2008 08:55:00 GMT

Today's author, Dan Parish, a Program Manager on the Excel Services team, will discuss over the next few posts how he built an asset tracking application using Excel Services.

At the SharePoint Conference in Seattle in March, I did a presentation entitled "Leveraging Excel Services to Build Rich SharePoint Applications". The talk included a demonstration of how to build a SharePoint application that used Excel Services to solve a data collection scenario in which group administrators had to maintain a list of all the machines in their ever changing organization.

I promised at the end of the talk that I would put the source code that I showed online, but I've decided to take it one step further and create a series of five blog posts that will describe how to build this solution from the ground up. The last post will also include all the code for all five posts. The five posts will be:

The Overview (this one)
Creating the Workbook
Creating a SharePoint List and a User Defined Function
Building the Web Part Pages
Creating the Archive Web Part

The solution: Data collection of an organization's machine information

In our group it can be challenging keeping track of who currently owns a given machine. While this may be a problem specific to the way we work, it is generally applicable to everyone since it really boils down to a data collection problem.

If I have a machine that I'm not using anymore (maybe I got a new one, or my responsibilities changed) I can just give it to someone else in my group who may need it instead. This exchange happens all the time for various reasons, and is great for making sure everyone has the equipment they need. However, for our group administrators, this creates a problem: they need to know where all those machines are, and they need to know if they need replacing.

There have been many solutions to this in the past: we've emailed details of all our current machines to our admin, we've entered data into SharePoint Lists, and we've even filled out InfoPath forms sent through email. There are two problems with all of these solutions though. First, there is little motivation to fill them out quickly, and second, once our admin has all of the data, they still need to put it all together and analyze it, which they usually do in Excel.

This solution addresses both of those problems by leveraging the power of SharePoint combined with Excel Services. With this solution, our admin simply needs to send out an email with a link and tell everyone to follow it and fill out their machine information. After filling out our machine information, we get feedback on how our machines compare to the rest of the organization's machines. Our admin gets her own web part page that shows who has and hasn't filled out the survey, and a listing all of the machines that have been entered, color coded to show which need upgrades and which don't. There is no need to collect and analyze the information, it's all done automatically. Further, our admin can at any time archive a copy of the report for future record keeping.

Here is what the first page looks like:

Here is what the second page looks like:

In my next post I'll detail how to create the workbook that is displayed both to the people that fill out the form and to the admin, and to analyze the machine information and display the results, both to the people that fill it out and to the admin.

Ten Tips for Using Excel Services

Joseph Chirilov — Fri, 23 May 2008 23:58:00 GMT

A new MSDN article about Excel Services was just released. If you do any kind of work with Excel Services, whether you are just getting started with it or trying to do something a bit more advanced, this article is a pretty good read because it covers some of the more common pitfalls that we hear from customers. The following topics are discussed:

Making an Excel Workbook Compatible with Excel Services
Configuring External Data Connections
Configuring Excel Services to Work with Kerberos
Saving an Excel Workbook to a SharePoint Document Library Programmatically
Avoiding Exceptions in a UDF When Accessing the Caller's Identity
Handling Dimension Issues for UDFs That Return Arrays
Calling 32-Bit Native DLLs from UDFs on 64-Bit Operating Systems
Quick Tips for Common Excel Services Issues

The article can be found here.

Christian Stich on Excel Services: Financial option valuation implemented with Monte Carlo simulation using multithreaded User Defined Functions (UDFs)

David Gainer — Wed, 15 Aug 2007 06:02:00 GMT

Today’s author: Christian Stich, a program manager on the Excel Services team who likes to combine his finance and engineering backgrounds. Christian is going to talk about building a multithreaded UDF for Monte Carlo simulations and using it for options pricing.

The example code and workbook in this post demonstrate the implementation of a European Put/Call Option valuation model using a high performance Monte Carlo simulation. Readers with a finance background realize that European options can be valued using the Black-Scholes options pricing model – the sample workbook also includes a calculation of the options values using Black-Scholes for comparison. This example is only meant to demonstrate the approach and implementation and does not intent to solve options valuation in the most efficient manner. The same basic Monte Carlo approach can be extended to price all kinds of derivatives/options (including American and Asian options as well as exotic options); moreover, the underlying distributions are typically not normal/log-normal. This is due to the fact that extreme price fluctuations are not as rare in reality as predicted by normal/log-normal distributions. Financial options are extremely important in risk management and mitigation, hedging and arbitrage and thus accurate and rapid pricing of options is very desirable.

The source code for the UDF and the workbooks can be downloaded at:

http://officeblogs.net/excel/MonteCarlo-Source Code+Workbooks.zip

Why use Monte Carlo Simulations?

Monte Carlo simulations are extremely useful in those cases where no closed form solutions to a problem exist. In some of those cases approximations to the solution exist; however, often the approximations do not have sufficient accuracy over the entire range. The power of Monte Carlo simulations becomes obvious when the underlying distributions do not have an analytical solution. Monte Carlo simulations are extensively used in the physical and life sciences, engineering, financial modeling and many other areas. In fact, significant amounts of processing times on supercomputers across the globe are used for Monte Carlo simulations.

Advantages of using Excel and Excel Services for financial, engineering or scientific simulations

Excel and Excel Services provide a great platform for modeling of complex scenarios. The UDF is used to generate the inverse cumulative distribution values which are used to calculate the options values using formulas in the Excel workbook. Keeping the formulas for the option pricing model in Excel (rather than incorporating them into the UDF) provides a number of advantages. It allows subject matter experts (in this case financial analysts) to easily and quickly modify the model without having to resort to coding in C# or other programming languages. Moreover, the fact that the UDF source code does not need to be shared helps protect companies’ intellectual property. This is also very important to 3^rd party companies that provide plug-in solutions for Excel and Excel Services. Similarly, the fact that access to the models in the Excel Services workbooks can be tightly controlled and is not required for the coding of the UDF also helps the companies’ safeguard intellectual property.

Advantages of using UDFs with Excel Services

UDFs allow programmers to write code that is compiled into machine language and not interpreted/evaluated, resulting in potentially significant performance gains. UDFs are well suited for implementations of Monte Carlo simulations as they are very computationally intensive. Data transfer between UDFs and Excel Services is very fast – depending on the server hardware several hundred thousand cells in the workbook can be read and written by the UDF per second. This allows creating models in Excel Services workbooks that exchange large amounts of data with the UDF without incurring a heavy penalty due to the data transfer.

Advantages of multithreading

CPU manufacturers are rapidly moving toward multi-core CPU architectures, increasing performance through parallelism in addition to the continuing increases in processing speeds. Today’s mainstream CPUs are dual- or quad-core with hyperthreading whereas a few years ago most CPUs contained a single non-hyperthreaded core. The number of processing cores is expected to rise steeply in the near future – harnessing the processing power of those CPUs requires programs to be multithreaded to make efficient use of the parallelism. Furthermore, servers are nowadays available with more than two CPUs, each of which contains multiple hyperthreaded cores itself. The approach taken in this example Monte Carlo simulation is the essentially a non-recursive divide and conquer algorithm. The simulation is divided into a number of chunks which are then calculated on the different processing cores in parallel. This type of simulation is very well suited to be parallelized as each individual part of the simulation is independent.

The code

For additional information about using UDFs with Excel Services take a look at Shahar Prish’s How UDFs work in Excel Services - a Primer, Danny Khen’s posts on UDFs - Excel 2007 investments in UDFs #1 & Excel 2007 investments in UDFs #2: Existing UDFs, and Excel 2007 investments in UDFs #3: client/server solution using a core library.

Overview of the code

The code consists of a number of declarations and four methods. The four methods are MonteCarloNormalDist, CalculateDistribution, MultithreadedCalculateDistribution, and CalculateDistributionThread. The MonteCarloNormalDist methods purpose is to act as the interface between Excel Services and the actual Monte Carlo simulation which resides in the remaining methods. The CalculateDistribution method splits the simulation into a number of equal sized chunks and calls on the MultithreadedCalculateDistribution method. The MultithreadedCalculateDistribution method itself calls the CalculateDistributionThread method which performs the actual Monte Carlo simulation.

Declarations

First we create a new class which we call MonteCarlo. We mark the class as a UDF class in order to make it available to Excel Services. We also define a class called ThreadWorkItem – it contains the variables for the individual worker threads (more about those further down in the code).

using System;

using System.Threading;

using System.Collections.Generic;

using System.Text;

using Microsoft.Office.Excel.Server.Udf;

namespace MonteCarlo

{

[UdfClass]

public class MonteCarloClass

{

public class ThreadWorkItem

{

public double[] Result;

public int thread;

public int count;

public int iter;

public double mean;

public double stdDev;

public int distribution;

public double minVal;

public double maxVal;

public ManualResetEvent ManualEvent;

}

MonteCarloNormalDist

Next we implement the MonteCarloNormalDist method and mark it as a UDF method and as volatile. This method supports array formulas as it determines the number of cells that are passed to the UDF. It then calls the CalculateDistribution method with the number of cells, desired number of threads, and the parameters that define the normal or log-normal distribution. If a cumulative distribution function is to be returned the method adds the values of the probability density function in order to arrive at the cumulative distribution function. Furthermore, this method can also generate an inverse cumulative distribution distribution which is needed for our options pricing model. Finally, the MonteCarloNormalDist method returns in calculated values to Excel Services.

[UdfMethod(IsVolatile = true)]

public object[,] MonteCarloNormalDist(

object[,] cellArray,

int iterations,

int threads,

double mean,

double stdDev,

int distribution,

double minVal,

double maxVal)

{

// get size of array

int cellArrayCount = cellArray.GetLength(0);

int i = 0;

int j = 0;

// get number of processor cores present in the system

int processorCores = Environment.ProcessorCount;

object[,] result = new object[cellArrayCount, 1];

double[] tmp = new double[cellArrayCount];

double cumulativeSum = 0.0;

double stepVal = 0.0;

double curVal = 0.0;

if (iterations > 0)

{

// round UP to nearest even number

if (iterations % 2 != 0)

{

iterations++;

}

// run number of threads as specified upto number of 'cores'

// use "if (threads == 0)" in order to allow for more threads than 'cores'

if (threads == 0 || threads > processorCores)

{

// 0 threads indicated default -> use number of 'cores'

threads = processorCores;

}

tmp = CalculateDistribution(

cellArrayCount,

iterations,

threads,

mean,

stdDev,

distribution,

minVal,

maxVal);

// bit 0: 0 -> normal, 1 -> lognormal

// bit 1: 0 -> probability, 1 -> cumulative

// bit 2: 0 -> 'regular', 1 -> inverted cumulative

for (i = 0; i < cellArrayCount; i++)

{

if ((distribution & 0x6) != 0)

{

// cumulative distribution function

cumulativeSum = cumulativeSum + tmp[i];

result[i, 0] = cumulativeSum / (double)iterations;

}

else

{

// probability density function

// tmp[i] = tmp[i] / (double)iterations * (double)cellArrayCount / (maxVal - minVal);

tmp[i] /= (double)iterations;

tmp[i] *= (double)cellArrayCount;

tmp[i] /= (maxVal - minVal);

result[i, 0] = tmp[i];

}

if ((distribution & 0x4) != 0)

{

// inverse cumulative

// divide the cumulative sum into cellArrayCount slices

stepVal = (double)result[cellArrayCount-1,0] / (double)(cellArrayCount + 1);

curVal = stepVal / 2.0;

j = 0;

i = 0;

tmp[j++] = 0.0;

// invert the distribution

while (i < cellArrayCount)

{

if ((double)result[i, 0] < curVal)

{

i++;

}

else

{

j++;

if (j < cellArrayCount)

{

tmp[j] = i;

}

curVal = curVal + stepVal;

}

tmp[cellArrayCount - 1] = cellArrayCount;

for (i = 0; i < cellArrayCount; i++)

{

// tmp[i] = tmp[i] / (double)cellArrayCount * (maxVal - minVal) + minVal;

tmp[i] /= (double)cellArrayCount;

tmp[i] *= (maxVal - minVal);

tmp[i] += minVal;

result[i, 0] = tmp[i];

}

return result;

}

CalculateDistribution

The following section of the code contains the CalculateDistribution method. Its purpose is to divide the simulation into equal sized chunks. The ‘last’ thread is assigned a few more iterations if the number of iterations does not evenly divide by the number of threads. The method also sets the variables for the individual worker threads prior to spawning the multiple threads. Subsequently CalculateDistribution waits for all worker threads to complete and then aggregates the results from those threads.

public double[] CalculateDistribution(

int cellArraycount,

int iterations,

int threads,

double mean,

double stdDev,

int distribution,

double minVal,

double maxVal)

{

int i = 0;

int j = 0;

int remainingIterations = iterations;

int iterationsPerThread = 0;

double[] tmp = new double[cellArraycount];

ThreadWorkItem[] twi = new ThreadWorkItem[threads];

ManualResetEvent[] mre = new ManualResetEvent[threads];

// iterations divided by the number of threads

iterationsPerThread = iterations / threads;

// set variables for worker threads

for (i = 0; i < threads; i++)

{

twi[i] = new ThreadWorkItem();

twi[i].thread = i;

twi[i].count = cellArraycount;

if (i != threads - 1)

{

if (remainingIterations >= iterationsPerThread)

{

// assign iterations to the all but the last thread

twi[i].iter = iterationsPerThread;

}

else

{ // threads will only get 0 iterations if the number of iterations is less than 2*threads

twi[i].iter = 0;

}

// subtract the assigned iterations from the remaining iterations

remainingIterations = remainingIterations - twi[i].iter;

}

else

{

// assign "left over" iterations to the last thread

twi[i].iter = remainingIterations;

}

twi[i].ManualEvent = new ManualResetEvent(false);

twi[i].mean = mean;

twi[i].stdDev = stdDev;

twi[i].distribution = distribution;

twi[i].minVal = minVal;

twi[i].maxVal = maxVal;

}

// create new threads and wait for their completion

for (i = 0; i < threads; i++)

{

ThreadPool.QueueUserWorkItem(

new WaitCallback(MultiThreadedCalculateDistribution),

twi[i]);

}

for (i = 0; i < threads; i++)

{

mre[i] = twi[i].ManualEvent;

}

WaitHandle.WaitAll(mre);

// aggregate returned values from the individual worker threads

for (j = 0; j < cellArraycount; j++)

{

for (i = 0; i < threads; i++)

{

tmp[j] += twi[i].Result[j];

}

return tmp;

}

MultithreadedCalculateDistribution and CalculationDistributionThread

This section of the code contains the actual parallel Monte Carlo implementation. The CalculateDistributionThread method uses the Box-Muller transform in order to efficiently calculate numbers that are normally or log-normally distributed.

void MultiThreadedCalculateDistribution(object state)

{

ThreadWorkItem twi = (ThreadWorkItem)state;

twi.Result = CalculateDistributionThread(

twi.thread,

twi.count,

twi.iter,

twi.mean,

twi.stdDev,

twi.distribution,

twi.minVal,

twi.maxVal);

twi.ManualEvent.Set();

}

double[] CalculateDistributionThread(

int thread,

int count,

int iter,

double mean,

double stdDev,

int distribution,

double minVal,

double maxVal)

{

int i = 0;

int index = 0;

double x1 = 0.0;

double x2 = 0.0;

double y1 = 0.0;

double y2 = 0.0;

double z = 0.0;

double range = maxVal - minVal;

double rangeCount = (double)(count) / range;

double[] tempArray = new double[count];

// to allow timer to advance for new random seed add "Thread.Sleep(1*thread);"

// also, depending on the requirements for the simulation a different method for

// generating the seed or a different random number generator should be used

Random rnd = new Random(thread);

// this thread may have 0 iterations, if overall number of iterations is very small

if (iter > 0)

{

// 2 random numbers are generated during each loop

for (i = 0; i < iter; i = i + 2)

{

x1 = 1.0 - rnd.NextDouble() * 2.0;

x2 = 1.0 - rnd.NextDouble() * 2.0;

z = x1 * x1 + x2 * x2;

} while ((z >= 1.0) || (z == 0.0));

// 1 - Pi/4 random numbers are discarded but this polar transform does not

// require trigonometric functions as does the cartesian version

z = Math.Sqrt(2.0 * (-Math.Log(z)) / z);

y1 = x1 * z;

y2 = x2 * z;

// divide by standard deviation and add the mean

y1 = y1 * stdDev + mean;

y2 = y2 * stdDev + mean;

// if log-normal distribution is to be returned

if ((distribution & 0x1) != 0)

{

y1 = Math.Exp(y1);

y2 = Math.Exp(y2);

}

// first random number

index = (int)Math.Round((y1 - minVal) * rangeCount);

// discard values outside the range

if (index >= 0 & index < count)

{

tempArray[index] = tempArray[index] + 1.0;

}

// second random number

index = (int)Math.Round((y2 - minVal) * rangeCount);

// discard values outside the range

if (index >= 0 & index < count)

{

tempArray[index] += 1.0;

}

return tempArray;

}

Testing the UDF using an Excel Services workbook

We now want to verify that the UDF properly calculates the requested distribution functions. An easy and visual way of verifying the functionality is to use Excel Services’ charting capabilities. The attached workbook contains a UDF array formula spanning 1,000 cells. The values returned by the UDF are plotted on an Excel X-Y scatter chart. We decide to plot the standard normal distribution probability density function with mean=0 and standard deviation=1 over the range from -3 to +3. Initially we use 50,000 iterations and we set the number of threads equal to the number of processing cores. Since we only used 50,000 iterations (resulting in an “average” of 50 iterations per calculated cell), the distribution is not “smooth” as can be seen below.

We now increase the number of iterations to 50 million. Running the simulation may take less than 1 second to a few seconds depending on the number and speed of the processing cores present in the server. We can immediately see that the distribution is now “smooth.” As mentioned previously, Monte Carlo simulations typically require a very large number of iterations in order to arrive at the desired accuracy.

Finally we want to verify that the inverse cumulative normal distribution function works as it is required for the European options pricing model. The chart below shows the generated X-Y scatter chart.

Implementing the European options pricing model using an Excel Services workbook in combination with the UDF

Now we are ready to implement the European options pricing model. The attached workbook contains a UDF array formula spanning 100,000 cells that returns the inverse cumulative normal distribution. This distribution is then used to calculate the values for the European Call and Put options.

Again we are using all of the processor cores (setting Threads=0). With 50,000 iterations the Monte Carlo simulation results are within 0.06%for the Call option and within 3.4% for the Put option of the results achieved using the Black-Scholes option pricing model.

Now we are going to use 1 billion iterations. The Monte Carlo simulation results are within 0.0004% for the Call option and within 0.02% for the Put option of the results achieved using the Black-Scholes option pricing model. As stated above, it makes sense to use the Black-Scholes option pricing model for European options, however, Monte Carlo simulations are a good choice for other types of options. Moreover, as can be seen from this example, large increases in the number of iterations are required in order to significantly increase the precision of the result. Thus, multithreading Monte Carlo simulations very effectively decreases the required simulation times.

Performance Improvements

The operating system and other applications are also using CPU processing time at the same time that the Monte Carlo simulation is running. This results in an uneven load on the different processing cores, resulting in those threads which are running on processing cores that have less of a load from other sources finishing sooner than those on heavily loaded processing cores. The goal for shortest simulation times should be to use all CPU resources evenly – with all in parallel running threads finishing more or less simultaneously. The implementation in the given example works well if the server is lightly loaded due to the fact that the processing times of

Approaches to even out CPU core usage include the use of a dispenser and increasing the number of threads beyond the number of processing cores.

Increasing the number of jobs to be much larger than the number of cores:

For this scenario we would simply increase the number of jobs beyond the number of processing cores. This way the system’s thread pool would effectively act as a dispenser, creating threads as processing cores become available. The advantage of this approach is that it works well if the speed with which the processing cores process the chunks varies. The difference in the time required to process a chuck is due processing requirements of other applications and the operating system and/or due to variability in processing complexity (unlike in this code example where each chunk takes the same CPU time to process). The disadvantage of this approach is the additional overhead introduced due to the increased number of jobs.

Using a dispenser:

In this scenario the number of threads would be equal (or less) than the number of processing cores. However, instead of dividing the simulation into n large chucks (n = number of processing cores) as in the provided coding example one would divide the simulation into a much larger number of smaller chunks. The dispenser would hand out chunks to be processed to the individual worker threads. Once a thread completes its work on a chuck it would request another chunk from the dispenser to work on. This approach provides the same advantage as increasing the number of jobs. The disadvantage of this approach is the added complexity and the overhead introduced due to the use of the dispenser.

Summary

Using Excel Services in conjunction with User Defined Functions allows high performance modeling of complex problems. Offloading the Monte Carlo simulation to the UDF allows for very efficient usage of CPU resources – resulting in very high simulation rates, yet keeping the actual model in Excel results in an easy to maintain and modify model. The approach shown can be adapted to much more complex models in finance (options pricing, risk modeling, etc.) and also lends itself well to modeling in a large number of very diverse subject areas ranging from the physical sciences and various engineering disciplines, life sciences, social sciences to project management and more.

Excel 2007 investments in UDFs #3: client/server solution using a core library

David Gainer — Fri, 05 May 2006 23:00:00 GMT

In the previous post I showed how an existing set of UDFs, defined in an Excel addin, can be used with Excel Services by wrapping them with a managed UDF assembly.

Some companies develop calculation libraries in a modular way. They want to use their library functions for various purposes on various platforms, and Excel UDFs is just one of those purposes. So they develop a function library, say in C or C++, which is independent of any of those platforms, and does not assume any application-specific interface. Then, to use the function library with Excel, they wrap it with an Excel addin, typically an XLL.

If you have such a solution, and now you wish to extend it to Excel Services as well, then all you need to do is develop the extra managed wrapper for the serer side. Since you already have a core function library, you don’t need to mess with wrapping an XLL; you can wrap your core library directly, and in many cases you can even embed it within the managed DLL – making server deployment much easier.

Let’s see how this is done.

“Wish Excel Had” – version 3

Let’s (ab)use the “Wish Excel Had” code sample from the previous posts once again. This time, the sample I am attaching is based on a core C++ library, which implements the static Word and Wordcount methods in a simple C++ class. Here is the sample code, and here is a workbook to use with the code.

You will find an XLL addin, that wraps those two C++ methods with UDFs for Excel client – WehWord and WehWordcount. In addition, you will find a managed UDF assembly, implemented in C++/CLI (“Managed C++”), which exposes the same two functions as managed UDFs for Excel Services. This time, the wrapper is much simpler; it does not have to deal with mapping .NET types to Excel’s specific interface. E.g. it does not deal with byte-count strings, it does not need to parse XLOPER structures and isolate error cases, and it does not need to handle the XLL way of allocating and releasing memory.

In this sample, the UDF assembly DLL actually embeds the core native function library, so all you need to deploy to the server is this single DLL.

More Excel Services UDF documentation, tips and tricks

• Shahar Prish has lots of good stuff in his blog. He is a distinguished member of the Excel Services developer team, and he really knows what he’s talking about (he actually implemented much of this). Very good reading material. Look under the Excel Services category.

• Office 2007 Beta program participants can find the Office SharePoint Server 2007 SDK documentation on the beta web site. The SDK covers the development of UDFs for Excel Services.

Excel 2007 investments in UDFs #2: Existing UDFs

David Gainer — Fri, 05 May 2006 01:25:00 GMT

Here is the second post from Danny Khen, a program manager on the Excel Services team.

In the previous post I explained about the investments we made around UDFs in Excel 2007 and in the Excel Services. I also showed how Excel Services use a different UDF technology than those used by the Excel client – managed UDFs. “Whoa”, you may be asking – “so what about all my investment in Excel UDFs – can’t I take them to the server as well?”

Well, you actually can, with some limitations. And we believe that many implementations of UDFs transfer just fine.

Can my existing UDFs be used on the server?

Or, to be more precise: Can my Excel workbooks call my UDFs on both client and server, and get the same results?

The answer is yes – if your UDFs have the following qualities:

The UDF’s entire interface with the Excel sheet is done thru the method call signature; arguments are passed into the function from the Excel formula, and return values are passed back into the formula. Excel Services don’t have an OM like Excel’s, so UDFs that make use of the Excel OM cannot run on the server.
The workbooks don’t try to pass one type of data into a different type of UDF argument. (But there’s a workaround if they do, if you’re willing to make a change to the original UDF – use Variants.)
There’s no need to distinguish between different error cases in the returned values; #VALUE! is used for all calculation errors as the returned value.
Your UDFs are thread-safe. Excel Services run different user sessions in different threads, so a UDF must be thread-safe to be deployed with the server.

If your UDFs comply with those requirements, or if you’re willing to invest in making them compliant – I’ll show you how you can use them with Excel Services.

Wrap them

The basic principle is simply “wrapping” your existing UDFs with managed code UDFs, which expose an equivalent interface. These wrappers, together with the native UDFs they wrap, are deployed to the server; when a workbook formula makes a UDF call, Excel Services uses the UDF method in the managed wrapper, which in turn calls your original UDF and returns its result into the sheet. All of this is transparent to the workbook users – as far as they are concerned, the workbook simply keeps working on the server and presents the same results.

I will focus on UDFs in XLL addins to demonstrate this concept, and briefly mention COM addins as well.

XLL addin UDFs

In the previous post, I attached a code sample to an Excel Services UDF assembly – a couple of methods we “wish Excel had”.

Today I am attaching another implementation of the same couple of methods – this time a cross client/server implementation. I am using the “X” suffix for all names here, to designate that this solution is XLL-based.

What you will find inside is an XLL implementation of two methods – WehWordX and WehWordcountX – that do exactly what their managed equivalents from the previous post did. In addition, you will find a managed assembly, implemented in C++/CLI (“Managed C++”), which exposes a class with two methods by the same name, each of which calls one of the XLL UDFs.

The wrapper functions do the following:

String conversion (managed to native; Unicode to ASCII; zero-terminated to Pascal byte-count strings; memory allocation handling).
Managed/native interop (we don’t really explicitly do anything in the code to achieve this; C++/CLI does it automagically for us).
XLOPER-to-Exception error mapping.

The attached Excel workbook calls the two UDFs and can be used to test them. It can be used as is on the client; it needs to be saved as an XLSX file to the server.

This looks pretty complex…

And it is – by nature of the beast. XLLs have an Excel-specific interface, designed to make them integrate well with Excel and run efficiently; it is somewhat of a challenge to map this interface to a generic, non-Excel function interface.

Next time I will show a more structured client/server UDF solution, which is based on a core function library that was not designed around the Excel interface. I will show how two wrappers are used around the core library – one for Excel client, the other for Excel Services.

COM addin UDFs

If your UDFs are implemented in a COM addin, you can take advantage of Visual Studio’s ability to reflect against your COM interface and generate an interop assembly – a managed DLL with methods that cross the managed/native boundary and call your native UDFs. You then write a thin layer around the interop assembly, with classes and methods that use the UDF attributes that Excel Services requires, and call the interop assembly methods. Your UDF wrappers will also take care of throwing exceptions whenever the original UDF returns a cell error – just like the XLL wrapper sample shows.

Excel 2007 investments in UDFs #1

David Gainer — Wed, 03 May 2006 16:14:00 GMT

For the next few posts, we have a “guest post” from Danny Khen, a program manager on the Excel Services team. Danny is going to talk about UDFs in Excel and Excel Services. Enjoy.

UDFs are user-defined worksheet functions – custom functions that you create to supplement Excel’s set of intrinsic worksheet functions. UDFs are used to create calculation libraries, or to import data into Excel sheets in custom ways.

In Excel 2007, we’ve made a number of key investments around UDFs. They revolve around two main areas: allowing UDFs to take advantage of important Excel improvements, and extending UDF-based Excel solutions to the server side with Excel Services.

Updated XLLs

A while back Dave mentioned that we updated XLLs (Excel’s addins based on the C-API) to give developers access to new Excel functionality. As that post explained, one of the common things that developers do in XLLs is to implement UDFs. We wanted to make sure that XLL authors can make use of some great new features of Excel 2007 itself in the UDFs they create. To recap, XLLs have support in Excel 2007 for:

The bigger grid
More function arguments
Multi-threaded calculation

There are many more detailed about those improvements in that other post.

Server-side UDFs

Dave has also posted a number of entries about Excel Services – the new feature in the Office SharePoint Server 2007 that enables calculation, display, and exploration of Excel workbooks on the server. These posts cover many aspects of Excel Services.

Much like Excel’s ability to be extended by writing UDFs in Excel addins, Excel Services also has an extensibility mechanism for writing UDFs. I’d like to use the rest of this post to explain and demonstrate Excel Services UDFs. In a follow-up couple of posts, I will show how you can create Excel solutions that use UDFs and can run both on a client machine using Excel 2007 and in a server environment using Excel Services.

They’re managed

Server-side UDFs are implemented as methods .NET 2.0 assemblies. That is to say, Excel Services directly supports only managed code UDFs. Existing native function libraries and Excel UDFs can be used with Excel Services by “wrapping” them with the new style of server managed UDFs; I will show how in the follow-up posts.

But why did we actually “go managed”? Excellent question. Managed code for enterprise-level solutions is becoming more and more popular, because of the many advantages that .NET code has to offer. Robustness and security are among the important advantages. Some of you may already be engaged in developing managed UDFs or other types of managed solutions. With Excel Services, we focused specifically on server stability, and we felt that using .NET as the basis for our extensibility would be the right thing to do in this respect.

They’re part of a V1 feature

In Office SharePoint Server 2007 we introduce the first implementation of Excel Services and of its extensibility mechanism. To start with, we simply had to prioritize the support for various features in Excel Services (even regardless of UDFs), and not everything made it for this first version. Moreover, we know that going forward we will be doing a lot of thinking around managed interfaces and extensibility for Excel – both on the client side and on the server. We want to make sure that customers' initial investment in managed UDFs is secured, and that at the same time we are not constrained in any way when we design the best possible infrastructure in the future. For those reasons, there are certain restrictions with the first version of server UDFs:

No Excel OM: the entire interface with the Excel sheet is done thru the UDF call signature; arguments are passed into the function from the Excel formula, and return values are passed back into the formula.
More restrictive than Excel addins with

Type conversion and supported data types.
Error handling: all exceptions thrown by the UDF code are returned into the Excel sheet as #VALUE errors.

Simple load / runtime behavior: for example, all UDFs currently run together with Excel Services code in the same .NET application domain.

We believe that lots of useful solutions can be created under these restrictions; in fact we see many existing Excel UDF-based solutions that could comply.

They need to be thread-safe

Excel Services is a server feature, and its calculation engine runs as a multi-threaded backend service. We have to require (and assume) that all UDFs are thread safe.

Security

A couple of things to mention about the way a server administrator can control the security of Excel Services UDFs.

First, Excel Services will not load and run just any old method in any old .NET assembly. An admin needs to register the assembly on a server list of trusted UDF assemblies.

On top of that, since these are .NET assemblies, an admin can make use of .NET’s CAS (Code Access Security) infrastructure to restrict UDF access to resources. For example, if a UDF package is simply a collection of math calculation functions, an admin can turn off its ability to access web services, external data, native code etc – and make the server environment safer for everyone.

So what does this thing look like?

Very simple, actually. All you need to know about (assuming you speak some .NET dialect) is two new attributes. Both of them are defined in the Microsoft.Office.Excel.Server.Udf namespace, and you need to reference an assembly that is shipped with Excel Services (Microsoft.Office.Excel.Server.Udf.dll) to get them.

The two attributes are UdfClass, which you use to mark a class where server UDFs are defined, and UdfMethod, with which you mark each individual public method to be considered a server UDF. The UdfMethod also has a boolean property – IsVolatile – used to declare the UDF as volatile, if you want the server to call it each time it recalculates the workbook, regardless of any change in its dependencies. The default is false, which means that the method is non-volatile; it gets called only when a value changes somewhere down the dependency chain of the formula that calls the UDF.

That’s really it. A typical UDF class will look something like this:

using Microsoft.Office.Excel.Server.Udf;

namespace YourNamespace
{
    [UdfClass]
    public class YourClass
    {
        [UdfMethod]
        public <return-type> NonVolatileMethod(<arguments>)
        {
  ...
        }
        [UdfMethod(IsVolatile=true)]
        public <return-type> VolatileMethod(<arguments>)
        {
  ...
        }
    }
}

Show us the goods!

Well, alright… Attached to this blog post is a fully developed code sample. It shows a UDF package by the name of WishExcelHad (because it implements a couple of functions that we wish Excel had…). The package has two method to manipulate text strings with words. The first method, WehWordM, takes the string, a required word position, and a delimiter string, and returns the word in that position after parsing the text with the given delimiter. The other method, WehWordcountM, takes a string and a delimiter, and returns the number of words in that string, when it is parsed with the given delimiter.

You can use the attached Excel workbook as an example that calls these two methods in formulas. You’ll need to save it to Excel Services as an XLSX file.

Why the weird “M” as the suffix for those method names? It designates “Managed” – these UDFs are implemented purely with managed code, and are intended to run with Excel Services. Stay tuned to the blog and look for the following posts, where I will show versions of the same UDFs, that can run across Excel 2007 and Excel Services.

Quick detour #3: Updates we have made to XLLs

David Gainer — Wed, 04 Jan 2006 01:48:00 GMT

In the past few months, I have written a couple of articles (big grid, multi-threaded calculation) that prompted comments and questions along the lines of “what about XLLs?” Since the email questions have actually been picking up in frequency (I guess a lot of readers are also XLL authors), I thought I would write a quick article that outlines the changes we are making in this area to support new functionality.

Stepping back for a minute, let me briefly explain XLLs. An XLL is a DLL that is written so that Excel can open it directly. XLLs can be used for a number of things, but (in my experience at least) the most common use of XLLs is creating user-defined worksheet functions (UDFs) to supplement Excel’s intrinsic functions. Developers who write XLLs do so for a number of reasons, the paramount being performance.

We have made three changes to XLLs in Excel 12, primarily to give developers access to new functionality. With Excel 12, XLL authors will have support for

The bigger grid
More function arguments
Multi-threaded calculation

Big grid and more arguments
When building an XLL today, developers can use a special Excel data type called an “XLOPER”. An XLOPER is essentially a structure that allows developers to pass data types like references, arrays, and error values to and from Excel (technically, XLOPERs can contain 12 possible data types – if you are curious, here is some documentation on MSDN). Currently, several of the data types (i.e. the one that can be used to communicate a reference to and from Excel) are not big enough to support the dimensions of the bigger grid size in Excel 12.

To address this, Excel 12 will implement a new XLOPER – which will be called something like XLOPER12 – which will have a larger reference (xltypeRef) data type and a larger array (xltypeMulti) data type which will support the entire big grid. In addition, XLOPER12 will support Unicode data and therefore strings larger than 255 characters. One happy by-product of the support for more characters is that XLLs written using XLOPER12 will be able to support 255 arguments, the same limit supported by Excel 12.

The Excel12 function
In order to use XLOPER12, developers will need to use a new C API function – Excel12 – instead of the existing Excel4 function. Excel12 is essentially the same function updated to handle the new XLOPER12, so if developers want to take advantage of any of the changes described above, they will need to update their code to call the new API. Note that existing code will continue to run as it always has, so developers will not face no backwards compatibility problems.

Updating XLL’s to take advantage of multi-threaded calculation
As discussed in a previous post, Excel 12 will support multi-threaded calculation. Since performance is very important for the bulk of XLL authors, we wanted to give developers who write UDFs using XLLs a way to allow their XLLs to participate in multi-threaded calculation (meaning Excel would calc multiple UDFs at once thereby improving calculation times).

To do so, XLL authors need to do two things. First, (the harder part) they need to make sure their XLL UDF (the code they wrote) is threadsafe (i.e. it must not make any non-thread-safe callbacks into Excel). Second, they need to make a minor update to their XLL to tell Excel 12 it is threadsafe. This is done in pretty much the same way that an XLL author would tell Excel that their function is volatile – by adding a special character to the “type text” entry in an XLL’s function table (the type text entry defines the data types for the functions’ return value and arguments).

Specifically, to indicate that an XLL function is safe for multi-threaded calculation, an XLL author needs to add a "$" to the type text entry in an XLL’s function table … so the type text entry for a UDF that had one argument would look like this “ RR$”. If this character is present, Excel will allow the UDF to participate in muti-threaded calc, just like Excel’s intrinsic functions; if the flag is absent, the reverse is true – all calls to that UDF will be run on a single thread. Note, the Excel4 API will also respond appropriately to the “$” character, so if an XLL author wants to take advantage of multi-threading, but doesn't care about the big grid, they do not have to use the Excel12 API.