Hello world!

Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!

Business analytic can turn your data into success

In today’s tight economy, every business tries to cut their operational cost, improving customer experience, maximize performance and minimize risk.  The challenge is how!  How can we work smarter and more efficient? How can we improve decision making, recognize new opportunity, maximize cost saving and determine inefficient processes.  The answer lies in our data!

Business analytic empowers us to transform data into information which is the foundation of our success.  For example, we can profile our customers based on their purchasing patterns, past activities and profitability to our business.  We can invest more resources to our best customers and determine what they are interested.  Predictive model can help us to analyze customers’ behavior, e.g. if they prefer buy one get one free or 30% discount.  We can use business analytic to improve our business processes.

From Data to Information

In order to make a smarter and more efficient decision, we need different types of information.  There is no single Swiss army knife that can answer all our questions.  We need a suite of tools:

• Standard reports
• Ad hoc reports
• Dashboard and key performance indicator (KPI)
• Online analytical processing (OLAP)

They have various usages and help us to answer different questions.

Standard reports and ad hoc reports are traditional tools that typically answer simple questions about the past, e.g. what is the income for Q2, 2010?  Dashboard and KPI provide high level overview to our senior management about how our business performs, e.g. are we meeting our sales target?

Today’s competitive environment won’t allow us to make a decision in months or weeks.  We only have hours or even minutes.  This is why OLAP is getting very popular in the past 10 years because OLAP allows us to make decisions based on accurate, comprehensive, consistent and timely information.

During the last few years, advanced analytical tools are getting more user friendly.  We don’t need a PhD in mathematics or statistics to master the advanced analytical tools such as the following:

• Statistical analysis
• Predictive modeling
• Optimization
• Planning
• Simulation

With these advanced analytical tools, we can easily:

• Explore and find pattern in the data.
• Making business decision that is based on the projection from the predictive model.
• Better equip our business with simulation
• Minimize the risk with optimization.

What tools are available?

There are four major players, – SAP, Oracle, IBM and Microsoft plus a few other independent business analytic vendors, e.g. SAS, MicroStrategy and Information Builders in the market.

Open source and Software As A Service (SaaS) are small but they are growing strong. Most of the recent implementations have been among “cost conscious” small and medium business. JasperSoft, Actuate and Pentaho are a few examples.

Personal business analytic tools are also available.  The most successful one is definitely Microsoft Excel.  If you have been working with Excel, you may already be familiar with What-if analysis.  But Excel has also bundled a few free and powerful analytic tools.  Microsoft Solver add-in and Solver Foundation can be used for planning, optimization and simulation ; Analysis ToolPak, a statistical toolkit that delivers 19 statistical methods to analyze data.

I have to mention the latest Excel 2010 add-in, PowerPivot, an in memory OLAP tool. I like it so much that I wrote a series of articles on my blog to discuss its power and features.  If you already know Pivot Table in Excel, then you know how to use PowerPivot.  It can analyze millions of records in seconds.

Conclusion

In order to survive, we cannot afford to make wrong decision. This is why we need business analytic to transform data into accurate, consistent and timely information. What tools are you using now?

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

VBA vs. VS – Final: To Migrate or NOT To Migrate

I hope I didn’t get all of you bored to death with all the technical stuffs in the last 2 weeks.  Don’t worry; this article would be the last one of the series.  I started this series because I know a lot of part time Excel VBA developers; their full time job may be accountants, financial analyst, traders….  They have been using VBA for a long time; and I admit some of them are even better than me.  However, they don’t seem to understand that VBA has its pros and cons and there are other development products which can do better job than VBA.  They just don’t have the time to find out about it.  So I started writing about VBA and its potential replacement, i.e. Visual Studio (VS).

I want to wrap up this series of blog by summarizing if you should use VBA or VS.

To Migrate or NOT To Migrate

Stay with VBA if:

• You want to use a lot of Excel functions and Add-In.
• You have deployment constraint within your organization.
• Your code is tightly integrated with Excel.
• Your code is simple or for one time use.
• Your code does not involve a lot of business logic.
• You code has to run on older version of Excel (2003 and older).

Migrate to Visual Studio if:

• Performance is an issue.  VS compiler allows better optimization and .Net Framework provides mechanisms to handle parallel and distributed processing
• Your code involves a lot of business logic.  VB.Net is an object oriented programming language which provides a more effective way to model business logic and inheritance supports robust code reuse.
• Your business processes requires frequent changes and deployment.  VS has bundled with Team Foundation Server (TFS).  TFS is a comprehensive application lifecycle management (ALM) suite which covers requirement management, change management, project management, configuration management, build management, testing, release management. deployment and issue management.
• You need easier code maintenance.  VS stores data, code and assembly in separated locations; so it facilities easier code maintenance
• You want to run your applications on web, cloud or even smart phone.  .Net Framework provides the foundation of multi platform development.
• You need to integrate with line of business (LOB) systems such as SAP and PeopleSoft.  .Net Framework has built in Web Services support that we can easily integrate your LOB system with Microsoft Office.
• You need a wide variety of user interfaces.  VS offers Windows Form, Windows Presentation Foundation (WPF) and Silverlight which would cover the needs of all your user experiences.
• You need to deploy the application to a wide spectrum of users (both onsite and offsite).  Microsoft System Center Configuration Manager can automatically deploy your .Net applications, setup the right permission and even apply future patches.
• You need to fine tune the application security for different users.  .Net Frameworks allows you to implement a more flexibility security model.

Conclusion

To migrate or not to migrate, it is always a tough decision. The examples I showed you, seemed very simple but in reality the migration is always a huge undertaking in term of both resources and risk.  You should consider their difference with regard to code maintenance, deployment, programming paradigm, performance, and security.  Will your application benefit from Microsoft .NET Framework?  Do your developers know how to do object oriented design (OOD) and understand the difference between VBA and VB.Net programming syntax?  WPF supports more GUI features than VBA UserForms; will your users have better experience with WPF?  It may sounds a stupid question but you may want to confirm with your IT department that you can install .Net Framework to all the computers.

However, the benefits are also significant!  Your application can run 10, 100 or even 200 times faster.  It can be deployed to cloud so you don’t need to buy new hardware or software plus you can add more horse power anytime you want.  You can find out what / who / when / why / how about a particular change.  Object Oriented (OO) is a proven methodology that can reduce development efforts; this is why all the popular programming languages like Java, C#, C++ and Objective C are OO.  There are more benefits about migrating to VS but I have to stop here.

I hope my blogs can help you to make an educated decision on the choice of development tools.  And if you have any further question or comment, please don’t hesitate to contact me at chan_a@algconsultings.com.

• Happy 17 Birthday Visual Basic for Applications!

• VBA vs. VS – Part 1: Performance Review

• VBA vs. VS – Part 2: Programming Paradigm Review

• VBA vs. VS – Part 3: What is new in VB.Net?

• VBA vs. VS – Part 4: .Net Framework

• VBA vs. VS – Part 5: First Excel Project
• VBA vs. VS – Final: To Migrate or NOT To Migrate

Andrew Chan is the owner and founder of ALG Inc.

VBA vs. VS – Part 5: First Excel Project

So much about background and theory, let’s start our first Excel project using Visual Studio 2010.

After we started Visual Studio 2010, we can build our first Excel project:

• Select File, then New and choose Project…
• New Project dialogue is displayed, select Excel 2010 Workbook and enter First Excel Project into Name and then click OK button.

• We choose to Create a new document, click OK button

• Visual Studio creates the First Excel Project project, and adds the following files to the project.
• First Excel Project .xlsx – Represents the Excel workbook in the project. Contains all the worksheets and charts.
• Sheet1.vb file
• Sheet2.vb file
• Sheet3.vb file
• ThisWorkbook .vb file – Contains the design surface and the code for workbook-level customizations.

• In Solution Explorer, Right Click on Sheet1.vb and select View Code.

• Replace the Sheet1_Startup event handler with the following code. When Sheet1 is opened, this code adds Now to first 100,000 cells in Col A.

Private Sub Sheet1_Startup() Handles Me.Startup
    Cells(1, 1) = Now

    For i = 2 To 100000
        Cells(i, 1) = Now
    Next
End Sub

• When the code is ready, press F5 or click the green arrow button to build and run your project.

• Excel opened First Excel Project Workbook and it would take about 10 seconds to process and added Now to 100,000 cells.

Instead of cut and paste my code into the program, if you actually type my code then you would see VS editor is much smarter.  When I tried to type Cells in the editor, it would actually show me the variable name.

And when I finished the For statement, VS editor would automatically add the Next statement.

It is a very simple program, so I decided to repeat the simple code in an ordinary Excel 2010 VBA environment.

Run the program on VBA.

I am actually surprised to see that the same logic run faster in VS environment; I would expect the other way.  So I have to do more study to find out the reasons behind.  This is a very simple demo that you may never find in practical business environment.  So your real experience would definitely be not the same.  However, this demo should give you some idea how Excel (and other Office product) can be automated in VS as well.

There are 3 types of project templates for Excel, Add-in, Template and Workbook.  We were using Excel 2010 Workbook for this demo which is a document-level customizations.  This means the code is associated with a single workbook; we have to open this particular workbook to run the workbook.   If you want the code to be avialable for all Excel workbooks, then we should choose the Add-in template.  Add-ins is associated with  Microsoft Excel, regardless of which documents are open.

Conclusion

I am going to write 1 more article to wrap up my stories, I would discuss the situations that I would recommend my clients to migrate their application from VBA to VS.  VBA is a great development tool and I still use it to check results from the financial models  that I developed using C++.  However, I hope you can now understand that VBA has limits and there are tools on the market that can do a better development job under certain circumstances.  For example, do you want to integrate your line of business (LOB) systems like SAP or PeopleSoft with Microsoft Office suite?  It would be much easier to do it with VS than VBA, you can find some information from Microsoft MSDN: Microsoft Office and LOB integration.

• Happy 17 Birthday Visual Basic for Applications!

• VBA vs. VS – Part 1: Performance Review

• VBA vs. VS – Part 2: Programming Paradigm Review

• VBA vs. VS – Part 3: What is new in VB.Net?

• VBA vs. VS – Part 4: .Net Framework

• VBA vs. VS – Part 5: First Excel Project

Andrew Chan is the owner and founder of ALG Inc.

VBA vs. VS – Part 4: .Net Framework

.Net Framework may be new to a lot of VB6 / VBA developers; however, the idea is not new.  When we used VBA / VB6 to create a simple graphic user interface (GUI), e.g. MsgBox or use Data Access Objects (DAO) to retrieve data from Microsoft Access, we are actually calling a runtime class library that contains millions line of code.  We don’t have to worry about all the Windows / database API and we don’t need to manage the messages flying around domain specific objects.  The VB6 runtime and DAO would take care everything for us and we only need to focus on business logic.  This is the beauty of VB6 / VBA.  Let Microsoft takes care about the technical complexity ! 

This is the same concept behind .Net Framework; the Common Language Runtime (CLR), the runtime environment for .Net Framework is more powerful than VB6 runtime and the Framework Class Library provides a larger range of features.

If you read my blog VBA vs. VS – Part 1: Performance Review, you can see how easy and powerful .Net Framework can be.  Instead of using a regular For statement, I used Parallel.For; Parallel is a new feature of .Net Framework which would make the best use of our multi core CPU.  The performance was improved by 2.5 times.  We would not be able to maximize our hardware investment if we are using VB6 / VBA.

.Net Framework

The .Net Framework consists of 2 main components, Common Language Runtime (CLR) and Framework Class Libraries (FCL).

Common Language Runtime

CLR is an abstraction layer over the operating system that all .Net programs are running on.  It takes care all technical details for developers; so we don’t need to worry about:

• CPU management
• Memory management
• Thread management
• Objects management
• Exception handling
• Garbage collection
• Security

We can use VB.Net, C#, C++ and other .Net compliance languages to develop applications.  CLR makes it easy to design components and applications whose objects interact across different programming languages. Objects written in .Net languages can communicate with each other, and their behaviors can be tightly integrated.

Framework Class Libraries

FCL is a a collection of thousands of reusable classes that encapsulate a large number of common system functionality.  To name a few, it provides simple file I/O, GUI, and database services or we can use something much more powerful and sophisticated such as parallel programming, distributed computing, workflow and cryptographic services. 

As a business developer, even if I have the technical knowledge, it may take me months to build a financial application that can fully utilize my multi cores CPU.  How much effort do we have to spend if we want to migrate the same application to a server farm with hundred or even thousand CPU?

Advantages

.Net Framework has many advantages over VB6 / VBA.

Multi-Language Development Platform

Instead of just using VB, CLR allows us to use the language we prefer.  We can use:

• Static Language – Visual Basic.Net, C# and C++ .
• Dynamic Language – Managed JScript, IronRuby, and IronPython.

User Experiences

Developers can use Windows Presentation Foundation (WPF) or Silverlight to develop richer GUI.

Most Windows developers are not graphic designer and graphic designers do not always know how to program.  WPF provides a common file format (XAML) which allows designers to work alongside developers in a workflow that promotes creativity while maintaining full fidelity.  WPF is a powerful framework that we can integrates GUI, documents, 2D and 3D graphics, hardware accelerated effects, and media content. 

Silverlight provides a cross-browser, cross-platform, and cross-device plug-in for advertising and rich interactive applications

Web Applications

Everyone is using the web now!  How can we run our applications on web?

ASP.NET enables developers to create anything from small, personal Web sites through to large, enterprise-class dynamic Web applications. Together with Asynchronous JavaScript and XML (AJAX ), developers can quickly create efficient, interactive, and highly personalized web sites

Distributed Systems

Do our systems need to communicate with our business partners or services providers?  Do we want to build a server farm to run our financial model?

Windows Communication Foundation (WCF) provides a model that developers can rapidly build service-oriented applications that communicate across the web and the enterprise.

Business Processes

We work as a team.  How can we work more effectively together?

Windows Workflow Foundation (WWF) delivers a  model that developers can use to build a business process which enable closer collaboration among business users.

Data Access

We already have DAO to access data from relational database but we now have data from XML and other applications.

ADO.NET provides a rich set of components for creating distributed, data-sharing applications,providing access to relational, XML, and application data.

Conclusion

If you visit Microsoft .NET Framework Class Library, you would see how feature rich it is.  I have used .Net Framework for over 5 years but I seriously doubt if I ever use more than 10% of the functionality.  I can  now develop web sites, distributed systems, and cloud applications.  Accessing data from RDB, XML and other applications is much easier.  I can  do thing that was impossible with VB6 / VBA or at least 10 times harder!

 

 

.

.

VBA vs. VS – Part 3: What is new in VB.Net?

I mentioned in my last blog, VBA vs. VS – Part 2: Programming Paradigm Review that VB.Net is now an object oriented (OO) programming language.  So what OO features have been introduced to VB.Net programming language?

Inheritance

VB.Net supports inheritance; it allows us to derive classes from an existing base class by extending the properties and methods of the base class. They can also override inherited methods with new implementations.

Class Base
    Sub Method1()
        MsgBox("This is a method in the base class.")
    End Sub
    Overridable Sub Method2()
        MsgBox("This is an override method in the base class.")
    End Sub
End Class

Class Override
    Inherits Base
    Public Field2 As Integer
    Overrides Sub Method2()
        MsgBox("This method is overrided in a derived class.")
    End Sub
End Class

Protected Sub Test()
    Dim O1 As New Base
    Dim O2 As New Override
    O1.Method1() 
    O1.Method2() 
    O2.Method1() 
    O2.Method2()
End Sub

Overloading

Overloading is the ability to create several methods with the same name which differ from each other in terms of the type of the input and the type of the output of the function.

Overloads Sub Print(ByVal theChar As Char)
    ‘ Add code that displays Char data.
End Sub
Overloads Sub Print(ByVal theInteger As Integer)
    ‘ Add code that displays Integer data.
End Sub
Overloads Sub Print(ByVal theDouble As Double)
    ‘ Add code that displays Double data.
End Sub

Overriding

Overrides allows derived classes to override member variable or functions that are inherited from parent class. 

Const BonusRate As Decimal = 1.45D
Const CommissionRate As Decimal = 14.75D

Class Commission
    Overridable Function Payment( _
        ByVal FaceAmount As Decimal, _
        ByVal CommissionRate As Decimal) _
        As Decimal

        Payment = FaceAmount * CommissionRate
    End Function
End Class

Class BonusCommission
    Inherits Commission
    Overrides Function Payment( _
        ByVal FaceAmount As Decimal, _
        ByVal CommissionRate As Decimal) _
        As Decimal

        ‘ The following code calls the original method in the base
        ‘ class, and then modifies the returned value.
        Payment = MyBase.Payment(FaceAmount, CommissionRate) * BonusRate
    End Function
End Class

Sub RunCommission()
    Dim CommissionItem As Commission = New Commission
    Dim BonusCommissionItem As New BonusCommission
    Dim FaceAmount As Decimal = 40000D

    MsgBox("Normal pay is: " & _
        CommissionItem.Payment(FaceAmount, CommissionRate))
    MsgBox("Pay with bonus is: " & _
        BonusCommissionItem.Payment(FaceAmount, CommissionRate))
End Sub

Constructors and Destructors

Constructors are functions that control initialization of new instances of a class. Conversely, destructors are methods that free system resources when a class leaves scope or is set to Nothing.  VB.Net supports constructors and destructors using the Sub New and Sub Finalize procedures.

Interfaces

Interfaces determine what properties and methods of the classes can be consumed within the system.  It is a logical view of the classes, which provide no implementations.

Interface IAsset
    Event ComittedChange(ByVal Success As Boolean)
    Property AccountBalance() As String
    Function GetBalance() As Double
End Interface

Class InvestmentAccount
    Implements IAsset

    Public Event ComittedChange(ByVal Success As Boolean) _
       Implements IAsset.ComittedChange

    Private AccountBalanceValue As String

    Public Property AccountBalance() As String _
        Implements IAsset.AccountBalance

        Get
            Return AccountBalanceValue
        End Get
        Set(ByVal value As String)
            AccountBalanceValue = value
            RaiseEvent ComittedChange(True)
        End Set
    End Property

    Private BalanceValue As Double

    Public Function GetBalance() As Double _
        Implements IAsset.GetBalance

        Return BalanceValue
    End Function

    Public Sub New(ByVal AccountBalance As String, ByVal Balance As Integer)
        Me.AccountBalanceValue = AccountBalance
        Me.BalanceValue = Balance
    End Sub
End Class

Delegate

Delegate is a reference to function and it is particular useful if we want to raise events that can call different event handlers under different circumstances.  We can dynamically associate event handlers with events by creating a delegate when we use the AddHandler statement. At run time, the delegate forwards calls to the appropriate event handler.  Delegate is not limited for event handlers and we can use it to call different versions of functions at run time.

Delegate Sub MyFirstDelegate(ByVal x As Integer)

TestClass
Sub TestSub(ByVal x As Integer)
MsgBox("The value of x is: " & CStr(x))
End Sub
End Class

Protected Sub DelegateTest()
Dim T1 As New TestClass
Dim msd As MyFirstDelegate= AddressOf T1.TestSub
msd.Invoke(10)
End Sub

Shared members

Shared members are properties, procedures, and fields that are shared by all instances of a class or structure.

Public Class SharedClass
    Public Shared Count As Integer = 1
    Public Shared Sub ShareMethod()
        MsgBox("Current value of Count: " & Count)
    End Sub

    Public Sub New(ByVal Name As String)
        Me.SerialNumber = Count
        Me.Name = Name
        Count += 1
    End Sub
    Public SerialNumber As Integer
    Public Name As String
    Public Sub InstanceMethod()
        MsgBox("Information in the first object: " & _
            Me.SerialNumber & vbTab & Me.Name)
    End Sub
End Class

Sub TestShared()
    Dim Shared1 As New SharedClass("keyboard")
    Dim Shared2 As New SharedClass("monitor")

    Shared1.InstanceMethod()
    Shared2.InstanceMethod()
    SharedClass.ShareMethod()
End Sub

Conclusion

There are many non OO related enhancements that were added to VB.Net, e.g. structured error handling, multithreading.  However, I am not going to cover them in this article.  So far, I covered most of the OO features in VB.Net and I the examples are not so difficult to follow.  But in case if you have any question, please feel free to ask.

I often tell people that the OO programming is easy to pick up; the challenge is OO design.  How can we design a class library that is simple to maintain and easy to reuse?  It would take us years before we can master the OO design.  But at the end, it is worth the efforts!

Andrew Chan is the owner and founder of ALG Inc.

VBA vs. VS – Part 2: Programming Paradigm Review

Part of this article is also being published in the July issue of Comp Act

When Microsoft upgraded VB6 to VB.Net in 2002, they decided to make VB.Net an object oriented programming language.  It was a hard choice because it created a huge gap for VB6  developers.  Most VB developers were not familiar with object oriented, even though some of them had worked extensively with objects; e.g. Microsoft Excel Objects.  VB6 / VBA only allowed developers to create their own business class.  Why was it so hard for some VB6 / VBA developers to migrate to VB.Net?  And why did Microsoft take such bold move to migrate VB to OO programming language?

VBA is an object based programming language.  They allow us to create classes and instantiate objects.  However, unlike object oriented programming language, they don’t have inheritance and hence without polymorphism.  So what are inheritance and polymorphism?  And why are they so important?  Let’s review some of the basic object oriented concepts first.

Object Oriented Concepts

There are a few object oriented concepts:

• Abstraction
• Encapsulation
• Inheritance
• Polymorphism

Abstraction

Abstraction is "the mechanism and practice of abstraction reduce and factor out details so that one can focus on a few concepts at a time."  Since abstraction extracts key characteristics of an object and hides other immaterial complexity, our readers should easily understand and visualize what we want to discuss.

"I just bought a new Samsung 46-inch 1080p 120Hz LCD TV," my friend told once told me. Size, number of lines, refresh rate and type are the key characteristics of an HDTV. He can go on to tell me about its physical dimension, weight, power consumption, etc., but most people don’t care, and would prefer being told "I just bought a new TV," abstracting the technical specifications to hide unnecessary details.

Encapsulation

Encapsulation is "the process of compartmentalizing the elements of an abstraction that constitute its structure and behavior; encapsulation serves to separate the contractual interface of an abstraction and its implementation."

Encapsulation allows programmers to use any method without understanding the details of implementation. This can drastically reduce the learning curve or maintenance effort of an financial system.

For example, how many developers understand the implementation of ADO.Net? With ADO.Net, most of us can learn in a couple of hours how to write a simple function to retrieve data.

If our financial system consists of 1,000+ classes, do we want all our developers to understand every single class and every method within? It would take forever to train a new developer. Encapsulation will shorten the learning curve of developers and will provide better system manageability and stability.

Inheritance

Inheritance is "a way to form new classes (instances of which are called objects) using classes that have already been defined. Inheritance is employed to help reuse existing code with little or no modification."

When we derive a new annuity calculator from its base class, we only have to implement the new features. It can save us enormous development, checking and testing time.  We can add new data type and member functions or even override existing functions.

Inheritance also increases overall system stability and reduces quality assurance effort. It allows developers to reuse code, enhance and modify existing class.

Polymorphism

"Polymorphism in the context of object-oriented programming, is the ability of one type, A, to appear as and be used like another type, B."   Inheritance is required in order to achieve polymorphism.

Polymorphism can greatly simplify coding and allow extensibility for future enhancements. With polymorphism, we can call 10 different annuity calculators from a single line of code. When we want to add five more new annuity calculators, we don’t need to modify the calling function.

Benefits of Object Oriented

Since OO promotes code reuse which would result in better reliability, robustness, extensibility and maintainability.  No wonder more and more computer systems have been developed using OO languages.  According to a study by TIOBE, OO programming languages are the most popular for more than 4 years; 55.4% code is based on OO languages.

However, migrating to OO programming language would not automatically grant us all these benefits!  Let’s say we had an old financial system that we developed using VB 6 and it had 20 program files. We decided to migrate to the latest Visual Basic.Net and create 20 classes to store each program files respectively. Moreover, since we couldn’t think of a good name for each class, we simply named them fin01, fin02 … fin20, the same name as each of the program files. We then copied the content of each program file into the corresponding class. By doing this, we created a system that uses OO programming language. But what benefits does our system have from this migration? Not a lot unfortunately!

In order to realize the true benefits of an OO programming language, we must understand the SOLID OO principles first.

SOLID Object Oriented Principles

• Single Responsibility Principle
• Open Closed Principle
• Liskov Substitution Principle
• Interface Segregation Principle
• Dependency Inversion Principle

Single Responsibility Principle (SRP)

"There should never be more than one reason for a class to change."–Robert Martin, SRP paper linked from The Principles of OOD.

Simple is beautiful. Each class should have only one responsibility and focus to do one single thing.

Our annuity calculator classes are already very sophisticated. If we also implement policy projection, decrement calculation and cashflow projection within it, then it would be huge and all our developers may always work on this big class together.

SRP would promote the reuse of code, clarity and readability. Our system would also be easier to test, enhance and maintained. Developers would also find less contention for source code files.

Open Closed Principle (OCP)

"Software entities (classes, modules, functions, etc.) should be open for extension, but closed for modification."–Robert Martin paraphrasing Bertrand Meyer, OCP paper linked from The Principles of OOD.

Most of us work on existing systems rather than build new systems from scratch. When we add new features to a system, we often feel more comfortable adding new functions than modifying an existing codebase. Why? We worry that our modifications would accidentally add new bugs to the systems, especially fragile systems. OCP recommends extending existing codebase, not modifying it.

If we already have 10 different annuity calculator classes, adding a new one should not modify any existing code.

Once we have followed SRP to build our system, it would be easier to implement OCP. Systems following OCP are often more stable because existing code does not change, and new changes are isolated. Deployment is also faster because existing features would not be accidentally modified.

Liskov Substitution Principle (LSP)

"Functions that use pointers or references to base classes must be able to use objects of derived classes without knowing it."–Robert Martin, LSP paper linked from The Principles of OOD

This principle is just an extension of the Open Close Principle, and it means that we must make sure that new derived classes are extending the base classes without changing their behavior so that the derived classes must be completely substitutable for their base class. Otherwise the new classes can produce undesirable effects when they are used in existing program modules.

Below is a classic example of LSP:

public class Rectangle
{
protected int _width;
protected int _height;
public int Width
{
get { return _width; }
}
public int Height
{
get { return _height; }
}
public virtual void SetWidth(int width)
{
_width = width;
}
public virtual void SetHeight(int height)
{
_height = height;
}
} public class Square: Rectangle
{
public override void SetWidth(int width)
{
_width = width;
_height = width;
}
public override void SetHeight(int height)
{
_height = height;
_width = height;
}
}
[TestFixture]
public class RectangleTests
{
[Test]
public void AreaOfRectangle()
{
Rectangle r = new Square();
r.SetWidth(5);
r.SetHeight(2);
// Will Fail – r is a square and sets
// width and height equal to each other.
Assert.IsEqual(r.Width * r.Height,10);
}
}

Square class is derived from Rectangle class; so C++ allows a Square object to be cast into a Rectangle object. However, Square class has its own setter functions; so r.SetWidth and r.SetHeight would set width and height equal to each other. What do we expect r.Width * r.Height to be equal to? Is r a Rectangle or Square object?

LSP would make the system easier to test and provide a more stable design.

Interface Segregation Principle (ISP)

"Clients should not be forced to depend upon interfaces that they do not use."–Robert Martin, ISP paper linked from The Principles of OOD

Again, it is another "simple is beautiful" principle. We should have multiple slim interfaces rather than a giant interface. Each interface should serve one purpose only.

If we have both policy month and calendar month projections, put them in two separate interfaces. For example, use IPMCashflowProj and ICMCashflowProj rather than just one interface named ICashflowProj.

With ISP, design would be more stable and flexible; changes are isolated and do not cascade throughout the code.

Dependency Inversion Principle (DIP)

"A. High level modules should not depend upon low level modules. Both should depend upon abstractions.

B. Abstractions should not depend upon details. Details should depend upon abstractions."–Robert Martin, DIP paper linked from The Principles of OOD.

Low-level classes implement basic and primary operations, and high-level classes often encapsulate complex logic and rely on the low-level classes. It would be natural to implement low-level classes first and then to develop the complex high-level classes. This seems logical as the high-level classes consume low-level classes. However, this is not a flexible design. What happens if we need to add or to replace a low-level class?

If our annuity classes (high level) contain cashflow classes (low level) directly, and we want to introduce a new cashflow class, we will have to change the design to make use of the new cashflow class.

In order to avoid such problems, we can introduce an abstraction layer between the high-level classes and the low-level classes. Since the high-level modules contain complex logic, they should not depend on the low-level modules. The new abstraction layer should not be created based on the low-level modules. The low-level classes are created based on the abstraction layer.

Once we implement DIP, our financial system will be significantly easier to extend, and deploying new features will take less time.

Conclusion

Please keep in mind that all of these principles are just guidelines that would make our system more stable, easier to maintain and enhance; but they are not ironclad rules. We must apply our own judgement and experience.

The learning curve from VBA to VB.Net is challenging; however, the benefits can also be enormous and definitely worth the efforts.

Andrew Chan is the owner and founder of ALG Inc.

Visual Basic for Application vs. Visual Studio – Part 1: Performance Review

I had used VB6 / VBA for many years and I like them a lot!  Like most Windows programmers, I developed my first Windows application using Visual Basic ; I didn’t have to learn or understand Windows API which drastically reduce the learning curve.  However, technology has changed a lot in the last 10 years.  Microsoft stopped support VB6 in March 2008 and as of July 1, 2007, Microsoft no longer offered VBA distribution licenses to new customers. 

Yes, we can still find VBA in Office 2010 but what improvement has been introduced in the latest VBA?  We can now migrate Excel 4 macros to VBA – this is the only enhancement that was mentioned in Microsoft Technet , Changes in Excel 2010.  Does anyone still have any Excel 4 worksheet?

I don’t think Microsoft would get rid of VBA in near future but at the same time, is Microsoft going to invest any significant effort to improve VBA?  I seriously doubt it.  So do you want to use a development tools that didn’t have any major upgrade for the last 10 years?  Or do you prefer to use the latest development tool that allow you to simplify coding, debugging and deployment?  A tool that you can target multiple platforms, e.g. Windows, web, cloud and of course Office environment.

Let’s look at the difference between Visual Studio and VBA by first reviewing their performance.  I developed a very simple financial calculator using Excel VBA.  I ran it.

Option Explicit

Public Sub Main()

Dim startTime As Date

Dim endTime As Date

Dim run As Long
Dim runs As Long

Dim Age As Integer
Dim AgeStart As Integer
Dim AgeLimit As Integer

Dim InterestRate As Double
Dim MeanInterestRate As Double
Dim Variance As Double
Dim Scenarios As Integer
Dim Scenario As Integer

Dim i As Integer
Dim PV As Double

runs = 10000

AgeStart = 20
AgeLimit = 100

Scenarios = 500
MeanInterestRate = 0.05
Variance = 0.05

startTime = Now

For run = 1 To runs

    For Age = AgeStart To AgeLimit
  
        For Scenario = 1 To Scenarios
      
            InterestRate = MeanInterestRate + (0.5 – Rnd()) * Variance
            PV = run * 1000 * (1 – (1 + InterestRate) ^ (Age – 100)) / InterestRate

      
        Next

    Next

Next

endTime = Now

MsgBox (startTime & endTime)

End Sub

It took 114 seconds to complete.

I copy and paste the whole program to Visual Studio 2010.  I compiled it and ran it again.

It took 58 seconds so it is nearly 2 times faster.

I modified couple lines in Visual Studio and ran it again.

Now it only took 23 seconds; nearly 5 times faster than VBA.  What did I change in the program?  Let’s look at it.

Imports System.Threading.Tasks

Module Module1

    Sub Main()
        Dim startTime As Date

        Dim endTime As Date

        Dim runs As Long

        Dim Age As Integer
        Dim AgeStart As Integer
        Dim AgeLimit As Integer

        Dim InterestRate As Double
        Dim MeanInterestRate As Double
        Dim Variance As Double
        Dim Scenarios As Integer
        Dim Scenario As Integer

        Dim i As Integer
        Dim PV As Double

        runs = 10000

        AgeStart = 20
        AgeLimit = 100

        Scenarios = 500
        MeanInterestRate = 0.05
        Variance = 0.05

        startTime = Now

        Parallel.For(0, runs, Sub(run)

                                  For Age = AgeStart To AgeLimit

                                      For Scenario = 1 To Scenarios

                                          InterestRate = MeanInterestRate + (0.5 – Rnd()) * Variance
                                          PV = run * 1000 * (1 – (1 + InterestRate) ^ (Age – 100)) / InterestRate

                                      Next

                                  Next

      End Sub)

        endTime = Now

        MsgBox(startTime & endTime)

    End Sub

End Module

Do you know what the changes are?

The CPU inside my computer is an Intel Q9400 which is a quad core processor.  However, the first 2 runs (VBA and VS 2010) only utilized 1 core.  Visual Studio 2010 supports parallel programming which would automatically make use of all cores on my system.  The code to utilize parallel programming is quite simple;  I just replaced the For statement with Parallel.For, then Visual Studio would execute the program on all cores rather than single core.

Without spending any effort on optimization, the performance gain to migrate from VBA to VS 2010 is already enormous, i.e. nearly 500%.

	Execution Time (Seconds)
Excel VBA	114
Visual Studio 2010 (w/o Parallel Programming)	58
Visual Studio 2010 (with Parallel Programming)	23

Someone may argue my program does not reflect a real financial application because it does not include any database input / output (I/O).  They are right!  My program does not have any I/O and I/O may not have such significant performance gain.  However, today’s financial model may involve hundred to thousand scenarios and each scenario can contain a few million calculations, therefore, the effort to I/O a small piece of data would be very minimal.

The purpose of this demonstration is to illustrate a performance gain can be easily achieved by migrating to Visual Studio 2010.  My next blog would be addressing the different programming methodology between VBA and VS, i.e. Object Based and Object Oriented.

Nonlinear interpolation with Excel to construct US Treasury bond yield curve

Excel offers many powerful mathematical and statistical functions that allow us to solve numerous business problems.  One of the typical challenges is that we don’t have all information we need!  Using US Treasury bonds as an example; it only has yield rate for certain maturities, e.g. 1 year, 2 years, 3 years, 5 years.  If we want yield rate for a 4 years maturity bond, then we may have to find a mathematician /  statistician to interpolate the value for us.  Fortunately, we can be the mathematician /  statistician if we have Microsoft Excel.  Excel has a few tools that we can use to interpolate values; and we are going to try a few of them in this blog.

Trendline

I went to US Treasury website and copied the following yield rates to my Excel Worksheet.

Once we have the data, then we can create a trendline.   And the first step is to create a chart based on the yield rate.

It is very simple to create a Trendline, just right-click the data series in the chart. From the pop-up menu, select Add Trendline….

Format Trendline dialogue would be displayed, we choose Polynomial with Order 3 as the trend type and we also select to Display Equation on chart and Display R-squared value on chart.

After we click the Close button, we would see a trendline is added to the chart with the equation and R^2.

Worksheet Functions

Excel provides many functions to project values:

• FORCAST
• TREND
• GROWTH
• LINEST
• LOGEST

We are going to use LINEST in this demonstration.  Since the yield curve is a 3rd order polynomial function, we would have 4 variables.

Yield = a₁ * Years³ + a₂ * Years² + a₃ * Years + a₄

Enter the following formula in our Excel worksheet.

=INDEX(LINEST(Yield, Years^{1,2,3}),1,1)

=INDEX(LINEST(Yield, Years^{1,2,3}),1,2)

=INDEX(LINEST(Yield, Years^{1,2,3}),1,3)

=INDEX(LINEST(Yield, Years^{1,2,3}),1,4)

Yield and Years are the defined range name.

We can see the coefficients a₁, a₂, a₃ a₄ and even R₂ are very close to the results from Trendline.

The formula for R₂ is:

=INDEX(LINEST(Yield, Years^{1,2,3},TRUE,TRUE),3,1)

Now we have all coefficients, we can interpolate the yield rates that we need.

Solver

If we want to use some specific functions, e.g. Nelson-Siegel function to interpolate the yield value, Solver is a good tool for the job.

Nelson-Siegel function is a modified Exponential function:

Yield = A₁ + (A₂+A₃) * (Beta / Years) * (1-e^-Years/Beta) – A₃ * e^-Years/Beta

Column D is the projected values that are based on Nelson-Siegel function and column E is the Squared Residual value between the projected and actual values.  Solver is going to minimize cell E10 which is the sum of all Squared Residual values by adjusting variables at cells E12..E15.  The setup is quite straight forward; we specify the Objective and Variable Cells and then Select a Solving Method.  When everything is ready, we click the Solve button.

The variable cells have been changed to their optimal values.

Again, we can now use the coefficients to interpolate the yield rate.  The R₂ is 0.9993 vs. 0.9966 from LINEST.

Others

There are other Excel tools that we can use to project / interpolate values, such as Analysis ToolPak or Microsoft Solver Foundation.  I would discuss them in near future. 

Andrew Chan is the owner and founder of ALG Inc.

DATA = Gold Mine

Organizations make business decisions every day. These decisions may range from how much discount should be given to clients, how much raw materials we need, marketing promotion…etc.  They can have a direct impact on costs and revenue to the organization. For example, giving a customer a discount may increase revenue but may not help the bottom line.

How do we make business decision?  Based on experience? guessing?  Unfortunately, not all of us have the right business instinct or it would take a long time to build up such experience.  How can we make a better decision?  Information!  We need strategic information to make a better decision!  Accurate, consistent, timely information can lead to lower cost, higher revenue and better customer satisfaction.

Reducing Costs

Once the information backlogs are eliminated, we can easily identify the root causes of any inefficiency in our operations and take prompt action to fix the problems e.g. building model to optimize business processes; identifying wasted resources and reducing inventory costs.

Increasing Revenue

“Who are our best clients that delivered 80% of our profits?”, “What are our most effective marketing channels?”  Such information allows us to micro segment our markets, gain an edge over the competition and empower the sales force to focus on high profitability customers and products.

Improving Customer Satisfaction

We can dramatically reduce the time to answer our clients’ questions, better understand our customer behaviors and hence provide customized services and even provide information to our clients so that they can make smarter decisions.

Challenges

In God we trust; all others must bring data. – W. Edwards Deming

By now, we should understand the importance of information.  Unfortunately information is not always readily available to every decision maker.  They have to wait or even guess!  What are the challenges? We have many business applications; e.g. finance, human resources, customer relationship management, supply chain, manufacturing.  They contain vast amount of data; however, data is not information.  It is a long way to transform data into information!

Garbage in, garbage out

Data quality is always the biggest challenge.  Poor data quality can jeopardize decision makers’ ability to make a better business decision or even make the wrong decision.  This problem could take a lot of effort to fix; so it is far better to implement a proper data strategy to avoid such problem.

Too slow

Each system generates many reports that deliver a lot of information to the decision makers.  However, if we need extra or the latest information, then we have to rely our IT department to extract more information for us.  We all know how long it would take!  And it may take a few cycles to understand where the real problem is.  It is simply too slow and we don’t always have the time to wait!

Inconsistent

Some of our power users know how to use Excel or other reporting tools to extract data directly from the enterprise applications.  Great!  They can retrieve the latest information in real time.  However, it leads to another issue, i.e. consistency. 

“How many people are working for our organization?”  It is a very simple question, but you may be surprised to find out how many different answers you can get; e.g. should I include employee with disability pay / maternity leave? how about contractor? If an employee who works 3 days a week; should we count 1 or 0.6?  Even such simple question can lead to so many different, inconsistent results.  Imagine what you would get if we want a more sophisticated report!

Lack of 360 views

Our organization has implemented more and more systems over time and each system has captured different data.  However, only a few employees may have access to all data; our database administrators.  For example, account manager may only have access to sales data but he may like to gather other information, e.g. customer satisfaction, inventory level, and other market information before he heads to his client’s office.  He may send an email to his department head; and his department head would forward his request to other department heads who would subsequently pass the request to their analysts.  Good luck if you can gather all your information in 2 weeks time!

Business Value of Data

What is the business value of data?  It is enormous!  Data in our system is the gold mine in our office.  It would help us to reduce cost, increase revenue / productivity and improve customer satisfaction!  What is the business value of a loyal and satisfied customer; especially in today’s downhill economy?

Ask yourself the following questions:

• Do you find it takes too long to get the information that you need to make a business decision?
• Do you receive consistent information, no matter who prepare the information?
• How many places do you need to go to get the answer?
• Do you know where to look for the information?
• How often is data rekeyed?
• How much information do you need to manually aggregate?
• How much data needs to be manipulated to get to an answer?

Do you need a better reporting system that can transform data into a good mine?  Most companies said “No, we already have a management information system.  If it ain’t broken, don’t fix it”  However, a well implemented business intelligence solution could give your competitive advantages and empower you to grow stronger!  So act now and benefit tomorrow!