Archive for the ‘Financial’ Category

Lehman Excel snafu could cost Barclays dear

Wednesday, 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

Posted in Financial | No Comments »

Modified Dietz Method UDF

Tuesday, April 15th, 2008

A while back when there was no xlns.lamkamp.nl 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.

Posted in Financial, UDF, Wikipedia | No Comments »

ISIN Code UDF

Wednesday, April 2nd, 2008

In my daily job validating security codes is a must.
So I wrote a UDF for validating ISINCodes.
There are no comments in the code, sorry for that…. maybe later…. enjoy..


Public Function ISINCODE(ByVal sISINCode As String) As Boolean

   Dim i As Integer: Dim iTotalScore As Integer
   Dim s As String: Dim sDigits As String

   sISINCode = UCase(Trim(sISINCode))

   If Len(sISINCode) <> 12 Then Exit Function

   If MID(sISINCode,1,1) < "A" Or MID(sISINCode,1,1) > "Z" Then Exit Function
   If MID(sISINCode,2,1) < "A" Or MID(sISINCode,2,1) > "Z" Then Exit Function

   sDigits = ""

   For i = 1 To 11
       s = Mid(sISINCode, i, 1)
       If s >= "0" And s <= "9" Then
          sDigits = sDigits & s
       ElseIf s >= "A" And s <= "Z" Then
          sDigits = sDigits & CStr(Asc(s) - 55)
       Else
          Exit Function
       End If
   Next i

   sDigits = StrReverse(sDigits)

   iTotalScore = 0

   For i = 1 To Len(sDigits)
       iTotalScore = iTotalScore + CInt(Mid(sDigits, i, 1))
       If i Mod 2 = 1 Then
          iTotalScore = iTotalScore + CInt(Mid(sDigits, i, 1))
          If CInt(Mid(sDigits, i, 1)) > 4 Then
             iTotalScore = iTotalScore - 9
          End If
       End If
   Next i

   If (10 - (iTotalScore Mod 10)) Mod 10 = CInt(Mid(sISINCode, 12, 1)) Then ISINCODE = True

End Function

Here is the link to the text file.

Posted in Financial, UDF | No Comments »