XLNS

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

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

End Sub


Cheers

Comments are welkom

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

End Function

cheers

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

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:

{=SQRT((SUM(counts)*SUM(MMULT(values^2,TRANSPOSE(counts)))-((SUM(MMULT(values,TRANSPOSE(counts))))^2))/(SUM(counts)*(SUM(counts)-1)))}

If the scores represent the entire population instead of a sample you should change it to

{=SQRT((SUM(counts)*SUM(MMULT(values^2,TRANSPOSE(counts)))-((SUM(MMULT(values,TRANSPOSE(counts))))^2)))/SUM(counts)}

My choice for coding a UDF would be:

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

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)

End Function


Thoughts on this code or on the functions….?

Cheers

Jelle-Jeroen

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

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…

cheers

Jelle-Jeroen

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

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.

Jelle-Jeroen

I just read the Smurf on Spreadsheets blog and found a post on a Excel Dashboard Competition.
witch I’m gonna check out and maybe even enter.
Check out the site…..

Cheers

Jelle-Jeroen

A couple days ago Dick Kusleika from Daily Dose of Excel was the first person to ccomment on my blog.
I want to thank him for that and hoping he will be reading my blog as promised.
All sugestions / comments will be appreciated.

Jelle-Jeroen

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.

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

End Function


Here is the link more readable code file.