software

I was having a coffee this afternoon with Darren Miller, the CEO of Sumwise and the author of their blog with the topic ‘our fascination with financial modeling’. We discussed the lack of collaboration functionality in Excel 2003 and 2007 and how there is an open whole for someone to fill when it comes to improving the current spreadsheet space.

Google have made a big effort in this sector but they are clearly aiming at the mass market of consumers and are not really catering for us professional users of spreadsheet software. Darren’s Sumwise has some interesting projects underway in this space and I think there could be some potential for future cooperation for us.

Has Excel got any functionality for collaboration?

The discussion with Darren reminded me of the Share Workbook functionality in Excel which I tested a couple of year’s ago. The Share Workbook functionality allows multiple users to work simultaneously in the same workbook without being ‘locked out’. When one users saves the file on the network gets updated and the other user gets informed of recent updated by other users upon saving the file.

This gives companies new options in terms of dividing projects between team members in ways not possible without this functionality. For example, one could ask a junior team member to code up the operational calculations of a financial model while the more senior person would focus on the cashflow waterfall and lock-up mechanics.

How to use the ‘Share Workbook’ functionality in Excel

It is pretty straight forward to test this functionality so I would recommend that you give it a go if you haven’t already tested it. Simply click Tools -> Share Workbook and you are half way there already. Check the ‘Allow changes by more than one user at the same time. This also allows workbook merging’ and you are done!

Excel Share Workbook functionality

Now, ask a colleague to open the same file from the network and have a play with performing updates and then saving the file. Excel will then identify areas modified by the other user - it is pretty good so far!

Excel Share Workbook - Advanced Options

So, what is the problem with Shared Workbooks?

Limited functionality in Excel Shared Workbooks

Well, there are two groups of problems with Shared Workbooks. The first one is well-known and properly documented in the Excel help and is the limited functionality available for Shared Workbooks.

From Microsoft Excel help

Workbook corruptions and freezing Excel

The second issue with Shared Workbooks is that Excel gets really unstable and often crashes or corrupts the file. This may not be too much of an issue if you are working on simple spreadsheets at home, but it you are working under time pressure on large financial models in the project finance or PPP sectors then this alone is enough to drive you crazy.

This massive limitation to Excel is also why we are not using the Share Workbook functionality at Corality even though it would in some cases have a massive positive impact. If Microsoft ever sorts this out in Excel 2003 I would be the first to congratulate them!

I was born in Stockholm, Sweden and lived there until I moved to Australia in 2003. Coming to Australia was like travelling back in time in regards to the “internet” used in Australia. Ads on TV still compared “broadband” to dial-up, a concept I couldn’t believe anyone was still using.

Going from Napster (it feels like a long time ago now, doesn’t it..?) on 10 Mb/s in my bedroom to Australia where the most used site on the web was the Commonwealth Bank of Australia’s NetBank which doesn’t allow you to use the ‘back’ button for ‘security reasons’ and looks like it has been designed by the guy who invented Lego was nothing short of a shock .

Kevin Rudd’s national internet infrastructure project

Finally, as a positive spin to the global financial crisis, the Australian government has decided it is time for Australia to see the light and to upgrade the national internet infrastructure to a decent standard. For a thorough analysis on this see BuddeComms analysis of the situation.

What outcomes can we expect of the infrastructure investment?

The Swedish investments in solid internet infrastructure have resulted in many fantastic internet solutions including Skype, Spotify and Pirate Bay. A successful delivery of this massive project (AUD 43 Billion) will put Australia on the map as forward thinking and potentially open up new sources of export now that resources seems to be of everyone’s mind for a bit….

I really hope that Rudd’s project will increase the Internet awareness in Australia which may result in

• more cross-border collaboration (via applications like WebEx and video conferencing via Skype),
• more efficient corporate infrastructure (using offsite storage and cloud based applications like Salesforce , the Google suite of corporate tools and DropBox)
• Better use of outsourcing of non-core tasks (RentACoder , Amazon Mechanical Turk )

When I was working with Navigator Project Finance we needed a new website. As a solution we hired a Mexican Developer in Sinaloa, Mexico through GetAFreelancer.com at the cost of US 300 using OpenSource Joomla compared to our best Sydney quote of AUD 12,000 (~USD 10,000) using a very second rate in-house CMS.

Most people found that very strange but what is really so different with a consultant in Mexico compared to someone at the other side of your own home town. Most tasks can be done with no face-to-face interaction.

How does Internet Infrastructure impact Corality?

At Corality we are constantly evaluation ways of working more efficient with clients all over the world. Only this week I have been liasing with clients in Kuwait, Saudi, Holland, United Kingdom and Australia (Sydney, Perth and Adelaide).

Out of these clients I have only met two face to face. Sure, for certain tasks one has to be in on site but 95% of all project related work can be performed from our Sydney offices. We are currently experimenting WebEx-based long-distance training catering for the US and Africa in particular.

Working with a global client base

With such a wide-spread client base I welcome all new solutions that can improve long-distance collaboration. We have over the years tested many tools including

• Blackberries (of course)
• Skype (voice, chat and video)
• WebEx (with great success)
• Remote Desktop (can’t live without it)
• VPN (useful but dull)

and more recently

• Google spreadsheets (very exciting!)
• LinkedIn (fantastic networking tool but unfortunately underrated by many professionals)
• Twitter (I still don’t get it..).

So, let’s just hope this project gets delivered sooner rather than later. No one would be happier than I too see some serious speed in our Internet infrastructure.

Operis, the developer of the spreadsheet review software OAK (Operis Analysit Toolkit) recently announced a new version OAK 4.00.

Graphically Operis has taken on the huge task of bring OAK into the ribbon-style interface of Excel 2007 and Windows Vista. Graphical updates are of course necessary to keep a software up to date but with the lack of uptake of Excel 2007 and Vista by the wider financial modelling community it doesn’t excite me nearly as much as the more technical improvements. We are yet to analyse the full impact of the new OAK functionality, but the promises are definitely very positive:

• Complete conversion from VBA to C#, which speeds some functions up one thousand fold
• Full compatibility with Excel 2007 and Vista, including ribbon interface and large spreadsheets (which, of course, make the performance all the more valuable)
• More capable setup utilities, capable of diagnosing and overcoming many obstructions to installation in the more locked down environments typical of banks
• Innovative and technically ambitious commands that seek to clarify the intent of complicated calculations, going much beyond simple precedent tracing
• Various enhancements to the already market-leading comparison functionality
• Exposure of all the functionality to COM, so that it can be driven by VBA and similar programming environments, allowing favourite review processes to be automated

OAK (Operis Analysis Toolkit) v 4.00

Updates from David Colver (Joint Chief Executive) indicate that Operis are in great shape to handle the continuous demand for PPP/PFI services in the UK and I would like to believe that the new version of OAK will make them even stronger. Delivering a big software update in the middle of a financial crisis is a very positive signal from Operis, so good on them for pulling this off when many other companies are scaling back investments to manage cash flow.

The data security of Excel spreadsheets is a joke. Anyone with half a talent can crack the passwords at most levels using either simple VBA code or one of the Russian password crackers on Google. This is a well-known security issue for most people having a technical perspective on Excel, but in the banking / transaction space this is certainly not the case. It is not uncommon that people responsible for multi-billion dollar investments protect parts of their Excel models by hiding and password protecting individual sheets without being aware that this ’security’ can be circumvented in seconds using simple VBA code available on most Excel forums.

How secure is the password on ‘Protect Sheet’?

Below is a typical VBA example of the code demonstrating the weaknesses of the terrible security in the ‘Protect sheet’ functionality. Password-protect a sheet and run the code below to generate another(!) password for that sheet.

Sub PasswordBreaker()

Dim i As Integer, j As Integer, k As Integer

Dim l As Integer, m As Integer, n As Integer

Dim i1 As Integer, i2 As Integer, i3 As Integer

Dim i4 As Integer, i5 As Integer, i6 As Integer

On Error Resume Next

For i = 65 To 66: For j = 65 To 66: For k = 65 To 66

For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66

For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66

For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126

ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _

Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _

Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

If ActiveSheet.ProtectContents = False Then

MsgBox “One usable password is ” & Chr(i) & Chr(j) & _

Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _

Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

ActiveWorkbook.Sheets(1).Select

Range(”a1″).FormulaR1C1 = Chr(i) & Chr(j) & _

Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _

Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

Exit Sub

End If

Next: Next: Next: Next: Next: Next

Next: Next: Next: Next: Next: Next

End Sub

How to send secure Excel information to external parties?

The day-to-day impact of the security flaws in Excel is that many bankers have to extract key information from Excel into PowerPoint which is a waste of everyone’s time but at least it can get distributing all of the data in the Excel workbook. Funnily enough you often see the Excel workbook pasted as an Excel object into PowerPoint which means that you can simply copy-past the whole workbook back into Excel…

Can you send safe Excel Workbooks?

There are a number of different providers working on security solutions for these problems. Many individuals have built ‘time-bombs’ etc in VBA which disables or destroys the workbook after a set date, however these often rely upon the user enabling VBA code when opening the workbook… A simple ‘No’ to the question ‘Enable Macros?’ therefore disables the time-bomb and you get full access to the workbook. One classic example of this is Chip Pearson’s  ’Timebombing a Workbook‘  which is an excellent article outlining a pragmatic view of Excel Security and should be read by all Excel users.

How to improve the security of Excel workbooks and VBA?

It would be great if there was one standard trick that solved all these issues. Unfortunately that is not yet the case and the industry of Excel users are still searching for a simple-to-use and still secure solution. One new up-and-coming software development company that appears to have overcome the biggest hurdles of secure Excel workbooks is DataSafeExcel. DataSafeXl was started by Andreas Ashiotis who has a background in KPMG financial modelling in London. I have only tested an early prototype of DataSafeXL but it looks very interesting and Andreas swears that the security really is at the level needed for institutional transactions. In a rather clever way the software can be used to set things like expiry dates and it then disables all the usual ways of disabling functionality in the normal ways. If someone’s used this in real applications it would be great to hear more about it.