Archive for May 17th, 2011

Lehman Excel snafu could cost Barclays dear

May 17th, 2011 admin No comments

October 22nd, 2008

A formatting fubar involving an Excel spreadsheet has left Barclays Capital with contracts involving collapsed investment bank Lehman Brothers than it never meant to acquire.

Working to a tight deadline, a junior law associate at Cleary Gottlieb Steen & Hamilton LLP converted an Excel file into a PDF format document. The doc was to be posted on a bankruptcy court’s website before a midnight purchase offer deadline on 18 September, just four hours after Barclays sent the spreadsheet to the lawyers. The Excel file contained 1,000 rows of data and 24,000 cells.

Some of these details on various trading contracts were marked as hidden because they were not intended to form part of Barclays’ proposed deal. However, this “hidden” distinction was ignored during the reformatting process so that Barclays ended up offering to take on an additional 179 contracts as part of its bankruptcy buyout deal, Finextra reports.

The error was discovered on 1 October, after US Bankruptcy Judge James Peck approved the deal, prompting a legal motion (pdf) from Barclays to amend the deal, excluding the scores of contracts it states were mistakingly included in the agreement. The story was broken by legal tabloid Above The Law.

Lehman Brothers sought bankruptcy protection on 15 September, a move that set off a chain of events that have shaken confidence in global financial institutions and the collapse of stock markets across the globe that has only been partially reversed by unprecedented government bail-outs to the banking sector. Barclays first offered to buy a “stripped clean” portion of Lehman for around $1.75bn on 16 September.

A revised version of the deal was eventually agreed on 18 September, largely focusing on the New York arm of Lehman’s business (including its $910m-valued headquarters) and responsibility for 9,000 former employees. Barclays paid $1.35bn for these assets as well as taking on responsibility for some of Lehman’s trading positions which, because of the formatting error, became more numerous than it intended.

It’s unclear what the financial ramifications of the formatting error might be. Excel spreadsheets might seem a fairly unsophisticated method of logging multi-billion pound trading positions, but they are quick to produce and easy to understand – vital consideration in a financial market – which makes them widely used.

A hearing on Barclays Capital’s attempts to extricate itself from the mess created by the dodgy Excel conversion is pencilled in for 5 November.
By John Leyden 15th October 2008

Categories: Excel Tags:

Pivot Table based on CSV file.

May 17th, 2011 admin No comments

July 31st, 2008

It’s been a while since my last post.
Due to my new job and rebuilding my house free time was scarce.
In my line of business we work a lot with data from numerous suppliers.
Some of the data is provided in csv files which can be imported in excel to base a pivot table on.
But what if the number of records / lines exceeds 65536 in excel 2003 or 1048576 in excel 2007.
You can cut the csv file in parts and base the pivot table on multiple consolidation ranges.
This was too much of a hassle for me and I started thinking about a more structured way to
create a pivot table on a csv file, with this result…
What is does is in fact simple but beautiful.
Select CSV File, create a recordset from it and attach it to the pivot cache to base a new
pivot table on this cache.

Option Explicit

'Set Reference to Microsoft ActiveX Data Objects 2.7 Library

Const sConnStrP1 = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq="
Const sConnStrP2 = ";Extensions=asc,csv,tab,txt;Persist Security Info=False"
Const sFilter = "CSV File, *.csv"

Sub CreatePivotTableFromCSV()

  Dim sFileName As String
  Dim sFilePath As String

  sFileName = Application.GetOpenFilename(sFilter, 1, "Select File", , False)
  sFilePath = Left(sFileName, InStrRev(sFileName, "\"))
  sFileName = Replace(sFileName, sFilePath, "")

  TestCSV sFilePath, sFileName

End Sub
Sub TestCSV(ByVal sFilePath As String, ByVal sFileName As String)

  Dim cConnection As ADODB.Connection
  Dim rsRecordset As ADODB.Recordset
  Dim pcPivotCache As PivotCache
  Dim ptPivotTable As PivotTable
  Dim SQL As String

  Set cConnection = New ADODB.Connection
  cConnection.Open sConnStrP1 & sFilePath & sConnStrP2

  SQL = "SELECT * FROM " & sFileName

  Set rsRecordset = New ADODB.Recordset
  Set rsRecordset = cConnection.Execute(SQL)

  'For Excel 2003 Use
  'Set pcPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
  'For Excel 2007 Use

  Set pcPivotCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal)
  Set pcPivotCache.Recordset = rsRecordset
  Set ptPivotTable = pcPivotCache.CreatePivotTable(TableDestination:=Range("B5"))


  Set rsRecordset = Nothing
  Set cConnection = Nothing

End Sub


Comments are welkom

Categories: CSV, Excel, Pivot table Tags: , ,

Who’s da mastah?…….Sho’nuff!

May 17th, 2011 admin No comments

June 1st, 2008

Last Friday I was making dinner for a company of 12 at my grandmother’s house and happened to be sitting next to the CTO of the major social networking site. He told me a story about a question he asked every programmer he interviewed for a job. The question was how to program the Fibonacci sequence. Just because he left me a scrap in which he makes fun of me I’m going to post a little array UDF for this problem.

Function FIBONACCI(n As Long) As Variant

   Dim sFibonacci() As Variant
   Dim CallerSize As Long
   Dim i As Long: Dim j As Long: Dim k As Long

   Const sqrt5 = 2.23606797749979

   With Application.Caller
      ReDim sFibonacci(1 To .Rows.Count, 1 To .Columns.Count)
      CallerSize = .Cells.Count
      For i = 1 To .Rows.Count
         For j = 1 To .Columns.Count
            k = (i - 1) * .Columns.Count + j + n
            sFibonacci(i, j) = Round(((1+sqrt5)^k - (1-sqrt5)^k) / (2^k*sqrt5),0)
         Next j
      Next i
   End With

   FIBONACCI = sFibonacci

End Function

p.s. I’m going to win a case of champagne and a public apology soon…..

Categories: Excel, UDF Tags: ,

Standard Deviation of Counts

May 17th, 2011 admin No comments

May 20th, 2008

After reading and commenting on the Standard Deviation of Counts at Methods in Excel I decided to award the topic a post on my own blog ( with the blessing of Ross )
The problem is to get the Standard Deviation for a number of counts of scores. You can achieve this by coding an UDF or by a combination of worksheet functions. Each with the obvious drawbacks.

The test example consists out of the following data:

The formula for the Standard Deviation I used was:

I named the two main ranges “values” and “scores”, defined:
N as SUM(counts),
the first summation as SUM(MMULT(values^2,TRANSPOSE(counts))),
and the second summation as SUM(MMULT(values,TRANSPOSE(counts))))^2
The combination of these parts resulted in:
If the scores represent the entire population instead of a sample you should change it to

My choice for coding a UDF would be:

Function STDEVCOUNTS(Counts As Range, Values As Range, bPartial As Boolean)

   Dim N As Long: Dim i As Long
   Dim dSum As Double: Dim SValues() As Double

   ReDim SValues(1 To Values.Cells.Count)

   For i = 1 To Values.Cells.Count

      SValues(i) = Values(1, i) * Values(1, i)

   Next i

   With WorksheetFunction

      N = .Sum(Counts)
      dSum = N * .Sum(.MMult(SValues, .Transpose(Counts)))
      dSum = dSum - .Sum(.MMult(Values, .Transpose(Counts))) ^ 2
      dSum = dSum / (N * (N + bPartial))

   End With

   STDEVCOUNTS = dSum ^ 0.5

End Function

Slightly slower ( about 5% ) but less code is my second attempt with a little help from Bob Phillips from the Excel User Group.

Function STDEVCOUNTS(Counts As Range, Values As Range, bPartial As Boolean)

   Dim N As Long: Dim i As Long: Dim dSum As Double

   With WorksheetFunction

      N = .Sum(Counts)
      dSum = N * Evaluate(”SUM(MMULT(” & Values.Address(, , , True) & “^2,TRANSPOSE(” & Counts.Address(, , , True) & “)))”)
      dSum = dSum - .Sum(.MMult(Values, .Transpose(Counts))) ^ 2
      dSum = dSum / (N * (N + bPartial))

   End With

   STDEVCOUNTS = dSum ^ 0.5

End Function

Thoughts on this code or on the functions….?



Categories: Excel, UDF Tags: ,

How Excel solves all my problems

May 17th, 2011 admin No comments

May 8th, 2008

Last week I was on holliday and we had to divide 3 bottles of wine among ten friends.
We could have written our names on little papers and draw 3 names from the lot,
but why do something like that when you have a computer with excel.
So I wrote a array function that returns a permutation from a set of data.

I put the 10 names in A1:A10 and then selected C1:C3 and inserted =PERMUTATION(A1:A10)
finally I pressed shift ctrl enter

and voila ( sorry for the french ) a random permutation of 3 names…….

the source for this small miracle is below

Function PERMUTATION(ByRef rSource As Range) As Variant

   Dim PermCol As Collection: Dim Cell As Range: Dim Result() As Variant
   Dim iIndex As Long: Dim i As Long: Dim j As Long

   ReDim Result(1 To Application.Caller.Rows.Count, 1 To Application.Caller.Columns.Count)

   Set PermCol = New Collection

   i = 1

   For Each Cell In rSource

      PermCol.Add CStr(Trim(Cell.Value)), CStr(i): i = i + 1

   Next Cell

   For i = 1 To Application.Caller.Rows.Count

      For j = 1 To Application.Caller.Columns.Count

         iIndex = WorksheetFunction.RandBetween(1, PermCol.Count)
         Result(i, j) = PermCol(iIndex)
         PermCol.Remove (iIndex)

      Next j

   Next i


End Function

I don’t know any real life use for this function besides the dividing of winebotles I told you about.
But if somebody has any use for this beautiful function please let me know…



link to this post on the excel user group with comments and other solution


Categories: Excel, UDF Tags: ,

Real Time Updating Online Spreadsheet

May 17th, 2011 admin No comments

April 20th, 2008

A friend asked me to look into online spreadsheets.

The best option seems to be Editgrid because he wants to collaborate with others and see
the changes realtime. I will be blogging on this topic in the near future.
Any comments are welkom.



Categories: Excel Tags:

Modified Dietz Method UDF

May 17th, 2011 admin No comments

April 15th, 2008

A while back when there was no I wrote and posted code for a Modified Dietz Method
UDF on wikipedia. For those of you in the financial industry it might be usefull.

Here is the link to that page on wikipedia.


Categories: Excel, Financial, UDF Tags: , ,

Extended VLOOKUP UDF Version 2.0 Beta

May 17th, 2011 admin No comments

April 9th, 2008

Thinking about Rick Williams remarks I rewrote the VLOOKPLUS function to perform better.
For an even better performance I shall have to rewrite the function in C/C++ or VB.Net.
Here is the result for now.

( It’s only good for exact matches when working with a negative column_index ).

Public Function VLOOKUPPLUS(l_v, t_a As Range, c_i As Long, Optional r_l) As Variant

   Dim i As Long: Dim l_r As Range

   With Application.WorksheetFunction

      If c_i < 0 Then

         If t_a.Columns.Count + c_i < 0 Then VLOOKUPPLUS = CVErr(xlErrRef): Exit Function

         Set l_r = t_a.Offset(0, t_a.Columns.Count - 1).Resize(t_a.Rows.Count, 1)

         VLOOKUPPLUS = .Index(l_r.Offset(0, c_i + 1), .Match(l_v, l_r, 0))

      ElseIf c_i > 0 Then

         VLOOKUPPLUS = .VLookup(l_v, t_a, c_i, r_l)


         VLOOKUPPLUS = CVErr(xlErrNA)

      End If

   End With

End Function
Categories: Excel, UDF Tags: ,

Writing Custom Excel Worksheet Functions in C#

May 17th, 2011 admin No comments

April 9th, 2008

Gahban berry wrote a great blog on writing custom Excel Worksheet Functions in C#.

Here is the link


Categories: Excel, UDF Tags: , ,

Alternative for my Extended VLOOKUP UDF

May 17th, 2011 admin No comments

April 8th, 2008

Rick Williams had a interesting comment on my Extended VLOOKUP UDF post.
He uses a index(match()) combination for these purposes.
I will look in to the performance comparison.

Here is the link to his reply on the Excel User Group.


Categories: Excel, UDF Tags: ,