Archive for the 'Excel' Category
VBA GSIterator
June 3rd, 2008VB Space API v1.2 is released.
A nice addition to the release is the native support for GSIterator, which enabled reading large sets of data in a manageable manner. The ReadWrite example file includes a usage scenario.
-Guy
Realtime Aggregations
May 30th, 2008
ActivePivot offloads pivot calculations and aggregations from the client process to a backend server. It exposes XMLA interface (among others), which means that if you are an Excel user, you simply define a pivot table and point the data source to the ActivePivot server. From that point onwards, Excel interacts with ActivePivot natively.
The result is a very thin Excel spreadsheet which only displays the aggregated result, and all the number crunching and aggregations take place at the server side. When the user interacts with the pivot table, Excel queries ActivePivot and display the next level. Very nice!
ActivePivot provides hooks for the correlation and aggregation execution, so custom logic can easily be applied.
ActivePivot stores the aggregated cube in memory, which allows it to update the cube and respond to market events, and by that provide intra-day aggregations.
The real time bit is where GigaSpaces fits in
Market data feeds are written into a space using one of GigaSpaces’ APIs (JMS, Remoting, JavaSpaces, etc’), and ActivePivot connect to the space and register for space events. When a tick is updated in the space, the space sends a notification to ActivePivot which in turn re-aggregates the relevant cube branch. This means that the aggregated cube is always updated and reflects the latest market state. In addition ActivePivot queries the space for the raw data when Excel sends a drill-down request, which allows ActivePivot a very quick response time.
ActivePivot can be applied at different areas and provide real time P&L, real time Risk, etc’
-Guy
GigaSpaces VB API Beta Release
May 7th, 2008GigaSpaces VB API version 1.0 is now available under OpenSpaces.org.
Up and above some enhancements and stability fixes, the main addition in this release is native notification support for VBA applications.
The distribution includes a comprehensive example of notifications, which includes also (as a side effect) interoperability with Java. It is a simulation of FXSpot Pricing System in which prices are updated by an external system (the Java feeder in our case), and at the other end there is a spreadsheet which is listening to the FXSpot updates.
Here are the basics of the example:
FXSpot POJO:
@SpaceClass(persist = false, fifo = false, replicate = false)
public class FXSpot {
private String fxName;
private double modified;
private float value;
@SpaceId(autoGenerate = false)
public String getFxName() {
return fxName;
}
public void setFxName(String _fxName) {
fxName = _fxName;
}
@SpaceProperty(nullValue = “0″)
public double getModified() {
return modified;
}
public void setModified(double _modified) {
modified = _modified;
}
@SpaceProperty(nullValue = “0″)
public float getValue() {
return value;
}
public void setValue(float _value) {
value = _value;
}
}
The counterpart PONO (I have omitted the COM methods):
namespace com.mycompany.trading.fx
{
[ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
public class FXSpot
{
private string _fxName;
private double _modified;
private float _value;
public FXSpot()
{
_fxName = null;
_modified = 0;
_value = 0;
}
[SpaceID(AutoGenerate = false)]
[SpaceProperty(AliasName = "fxName", NullValue = "")]
public string FXName
{
get { return _fxName; }
set { _fxName = value; }
}
[SpaceProperty(AliasName = "modified", NullValue = 0)]
public double Modified
{
get { return _modified; }
set { _modified = value; }
}
[SpaceProperty(AliasName = "value", NullValue = 0)]
public float Value
{
get { return _value; }
set { _value = value; }
}
}
}
Java feeder – After looking up the space, and initializing Random helpers and currency names, it looks like:
while(true)
{
FXSpot spot = new FXSpot();
spot.setFxName(fxNames[ccyRand.nextInt(10)]);
double modified = (double)System.currentTimeMillis();
// convert to Excel date
modified = (modified + 7200000) / 86400000 + 25569;
spot.setModified(modified);
spot.setValue(valuesRandom.nextFloat());
System.out.println(“Writing spot = “ + spot);
spaceProxy.write(spot, null, Lease.FOREVER);
Thread.sleep(sleepTime);
}
Up until now, nothing new really… Now begins the interesting bit!
After omitting the initialization parts of the VBA code, what we have is the following:
Registration function:
Function RegisterNotifications(ByVal sRng As String)
If GigaSpace Is Nothing Then
Call Init
End If
Dim rng As range
rng = range(sRng)
Dim Template As FXObjects.FXSpot
Template = New FXObjects.FXSpot
Template.fxName = rng.Value
Dim modifiers As Integer
modifiers = NotificationModifiers.Update
Call GigaSpace.RegisterNotification(Template, “Notify”, modifiers, sRng)
End Function
It is very easy to explain the code. If you have ever read the docs or used GigaSpaces’ notifications API, you should feel pretty comfortable here. Same rules apply… If you haven’t, a quick browse here will help understanding the API and terminology better.
You simply invoke GigaSpace.RegisterNotification method with a template to match, the name of your notify function, notify modifiers (take, write, update, all), and last argument which is not part of GigaSpaces notify API is called vbHint. It can hold any object the user wishes to receive back when the notify function is triggered for this specific registration (i.e. template match and operation match). In this example I have iterated through a list of currencies, and I pass the cell address of the specific currency to the registration so that when my Notify method is invoked, I can lookup the relevant cell easily.
My Notify function:
Function Notify(ByVal eventType As Long, ByVal pono As Object, ByVal vbHint As Object)
Dim sRng As String
sRng = vbHint
Dim rng As range
rng = range(sRng)
Dim spot As FXObjects.FXSpot
spot = pono
rng.Offset(0, 1).Value = spot.Value
rng.Offset(0, 2).Value = spot.Modified
End Function
Note the Notify signature. When registering to notifications, the notify function must have this signature in terms of the types it takes as arguments. The eventType represents the space operation which triggered the notification, and the pono is the object which matched the registration’s template and triggered the notify.
In this example, I take the values from the notification object, and update the relevant Excel cells (I use the vbHint for that).
The example includes a button to UnRegisterNotifications.
Public Sub UnReg()
If Not (GigaSpace Is Nothing) Then
Call GigaSpace.UnRegisterNotifications
End If
End Sub
The UnRegisterNotifications method removes all registrations for the invoking Excel from the space.
One more thing to note is the termination of the Excel process. As notifications require some daemon threads to run at the background, the Excel needs to invoke GigaSpace.Terminate function which cleans up these threads and frees some resources. Failing to invoke the method could result a hanging Excel process. As a best practice, you should catch the Excel workbook close event and call the terminate method.
Example (at the workbook level):
Private Sub Workbook_BeforeClose(ByVal Cancel As Boolean)
Call TerminateGigaSpace
End Sub
Which invokes the Terminate method:
Public Function TerminateGigaSpace()
If Not (GigaSpace Is Nothing) Then
Call GigaSpace.Terminate
End If
End Function
The example with inline comments and documentation is packed in the setup. After downloading and completing the installation process, the example Excel can be run from –> Programs –> VB Space API –> Examples –> FXSpot Notify. The example is located under \Examples\FXPrices. Follow the readMe.txt to run and change the example.
Enjoy!
-Guy
VB Space API Alfa Release
April 12th, 2008An Alfa release of the Space VB API is now available at OpenSpaces.org.
There are a number of changes in this build. Main ones are a new installer, a fix for default value in read and take APIs, and a more comprehensive example and documentation pages.
You can read more about it in the project pages, but one thing I want to specifically mention in my experience with the installer program.
It has been very long since the last time I had to compile an installer program. Actually, it was around 1998, where I created a setup for a university project. It was based on InstallShield, which made the entire process long and painful! This time it was very different. I used a utility called Inno Setup, a very lightweight and simple installer. It took me no more than 10 minutes to create the first run of the setup, and another half an hour or so to customise it with my own specific stuff (making sure GigaSpaces is installed, invoking the example at the end of the setup, etc’), which was also straight forward to do thanks to the detailed help files provided.
Now, surely InstallShield could do everything I needed and much more, but it would have been much harder and a longer process… Kudos to Jordan Russell for the well designed and implemented installer utility!
Enjoy!
-Guy
Excel That Scales at Microsoft Developer Conference
March 17th, 2008As a Microsoft partner we've been invited to attend the 6th annual Microsoft financial services developer conference which took place in New York City last week.
It has been a great experience (about 900 people attended), and for us at GigaSpaces it was a huge success.
We presented two live demos:
GigaSpaces Excel that Scales: [...]
