Archive for the ‘Presentations’ Category

Excel Developer Conference Presenter – Bob Phillips

January 11, 2012

Intro

I am Bob Phillips, currently an independent solutions provider. In previous lives, I worked for large financial corporations building credit risk management systems.

My principal area of interest is presenting data as information, which to me covers the whole range from capturing (the right) data, extracting it from wherever it has been ferreted away, and presenting it in the most appropriate visual manner (that is, no pie charts!). To my mind, this naturally leads to what is now called Business Intelligence.

I first became involved in this area in 1996. As I said, I used to build credit risk systems, and I was building a system for one of our major investment banks and we needed to supply reporting capability. We could have opted to build a suite of custom reports, or used one of the market leaders at that time (Cognos, Business Objects, …), but both of these choices left us less than enthusiastic. Instead, we decided to build a data warehouse, plug Brio into this warehouse, output the reports as Excel files, and publish these to our intranet. That was my first exposure to warehouse development, and I chose Excel as it was a tool I had used for my own use, just like my users, and I knew how good it was. I have never been the same since.

My Session

Since I started in this field, things in the warehousing and BI world have just got better and better, even SQL Server is a decent product now (it wasn’t in the late 90s, early 2000s).

Of course, the big problem with these BI solutions is where they are supported, the dreaded IT department. So a solution that allows the user to produce their own reports; to do their own analysis; to incorporate data not in the enterprise systems; this has become the BI Nirvana. Many companies have produced products claiming to do this, and now Microsoft have come up with their own offering, PowerPivot. In this session, we will look at PowerPivot, data modelling and DAX formulae, consider whether this really is a user tool or still for the developer community. The great thing about PowerPivot is that it is integrated with Excel, it is an Excel addin, but has the goal of self-service BI been achieved?

My Interests

I think that Excel is the best computer application ever. It may not be perfect; it may not be the best architected; it may be lacking a ton of necessary, even critical, functionality; but it does so many basic things well, and it is so easy to build upon an Excel application that its spread and influence are enormous. Unfortunately, I think Microsoft are trying their hardest to destroy it in their misguided corporate vision, but I am not sure they are good enough to achieve it.

With my infatuation with Excel, I spend far too much time on various Excel and SQL based websites and forums, trying to convince others about my view of these products by helping them to resolve problems. When I do manage to drag myself away, I like to spend my time cycling or walking the cliffs in Dorset. I have been known to have a drink or two, and I do like eating, so I am looking forward to the apres-Excel session on the day.

Advertisements

Excel Dev Conf presenter – Simon Murphy

January 9, 2012

Name

Simon Murphy

Occupation

Work Dodger.

No really…

Excel interoperability nerd. I connect Excel to other corporate resources, either data (eg in Oracle or SQL Server or Access or Essbase or SSAS etc), or logic (eg C# or F# logic functions, or C++ user defined functions)

I use whatever technology fits the bill, so sometimes VBA, Sometimes C#, C++, Dos scripting, javascript, ADO, XML, full on buzzword bingo in fact.

I do consultancy and training, and also contracting, where I may work at the same client for 3 or 6 months.

I will work pretty much anywhere, but have recently set up the family base in Switzerland.

I work through Codematic Ltd an Excel development specialist company.

What topics?

The Excel window – This session is  about all the possible ways there is to get that useful corporate info and /or logic into Excel and the pros and cons of each.

Managing Excel developments – Here I talk about how Excel development projects go and what works and what does not work in trying to manage that effort. This is experience based, not book based. It’s also customer based not project management based.

Life summary:

Happy childhood, went to college, hated it, dropped out a few times, then went skiing for a few years. Came back, went into accounting, big mistake, realised technology was my best exit strategy. Did 36 period end closes, by which time I had automated all the boring stuff away, never sat my finals – studied for them loads of times. Went and did a MSc in Software Development part time whilst gradually shifting focus from finance to IT. Always struck by how powerful Excel is as a client and how much traditional IT hate it and waste years reproducing a fraction of the features in a browser based app that everyone hates. Currently treading the fine line between what users need to do their roles and what IT is prepared to deliver, or allow others to deliver.

Other work type stuff

I blog at smurfonspreadsheets where I try and cover spreadsheet risks and quality issues as well as hard core development stuff. Interspersed with occasional rants about a range of topics, including crap McManagement and poor electronic devices. I have spoken at a few development conferences, as well as Eusprig (where my focus is more on wasted time due to poor spreadsheet use rather than ‘errors’).

Hobbies

Kids mainly. I still ski and snowboard although not at the level I used too, I get regularly reminded of this by large wipeouts.

I used to do a bit of woodwork, but I am generally too far from my workshop at the moment. I do a bit of photography, although now the kids are sick of me saying ‘do that jump again I missed it’ my opportunities for action shots are decreasing. I try to capture any nice sunsets, I heard a rumour there are often nice sunrises, but I rarely stay up that late these days.

Favourites:

Beer: Jenlain. Biscuits: Fresh crunch creams (i used to work on the cc line at Foxes, these are delicious as they come out of the oven, hot.). Biking: Downhill. Super hero: Monkey 😉

Other positions

International self declared life president of the Worldwide Extreme Spreadsheeting club:

Unless you have a more extreme spreadsheeting shot?

I was going to call it Extreme Excel which rolls off the tongue a little easier, but it’s Open Office Calc, innit? (on Linux)

(this was on a chairlift 5-10 meters up, in the snow at about-5C)

cheers

simon

Excel Dev Conf Presenter – Charles Williams

January 5, 2012

What’s your job?
I am Decision Models Ltd – Excel consultancy & micro ISV

What’s your Topic?
VBA to C – Pratfalls and Perils of a C++ Newbie

My FastExcel product contains about 8K lines of VBA UDFs designed to speed up Excel calculations. For the next version I am rewriting and extending them using C++ XLLs (see Excel UDF Technology choices for some of the reasons behind this).

So the idea of this session is very much to approach C++ XLL’s from the viewpoint of a VBA developer, and talk about my journey from VBA developer to C++ newbie.

I plan to demonstrate developing and debugging some XLL UDFs using Planatech XLL+ and Visual Studio 10, along with some of the embarrassing mistakes that I keep on making (but you aren’t allowed to laugh), and a few of the things that I still don’t properly understand (string handling/functions !*#!).

The session will hopefully demolish some of the myths surrounding XLL development.
If there is time I will show how using the STL and BOOST libraries and XLL+ UDF wizards make it easy to add powerful features such as multi-threaded global memory caches to your UDFs.

How did you get to be an Excel Speed Geek?

I spent many years with IBM in a wide variety of different roles, until the time came to part. Then in 1996 I started my Excel consultancy and development business. And somehow along the way I became an Excel Geek obsessed with ways of making Excel calculate faster.

What else do you do?

Excel Dev conf presenter – Stephen Allen

December 23, 2011

Name:        Stephen Allen

Business:    ACBA (UK) LTD – consultancy and Excel software development.

Topic:    Project management within Excel (2003). Managing the process of reviewing a large (multi sheet) workbook logically and drawing findings / conclusions. The process is engineered such that a reviewer can inspect the data sources and associate logic instantly.

This presentation concentrates, specifically, on the programming and management of closely related but separate findings/conclusions, such that a report (in MS Word) can be generated directly from the review process.

Is the process sufficiently robust?
Is there a realistic case for developing the same software in ribbon style Excel?

Why;    The software has been on the market for 10 years (nearly). I’m still not certain of the answers to the above questions even though it has been employed extensively within the company. The programming itself is fairly sophisticated and deserves an airing even if the functionality proves unpopular.

The Excel 2003 Add-In is available free (and open-sourced) from http://tech.groups.yahoo.com/group/ACBA_Functions/

You will have to apply for membership. Sorry, but I must keep the riff-raff out.

Hobbies:    Grandfather-dom, walking/cycling, amateur classical chamber music

Conference Feedback

December 16, 2007

Finally got all those feedback forms entered and analysed.

In most areas the results were either 4’s or 5’s (out of 5) so that is very encouraging. Basically the presenters and the content were very well received. As this is the core reason for attending its great that the attendees were so pleased.

The main areas for improvement were around pre-conference admin and information. Part of the issue here was the last minute nature of the event – we only had about 6 weeks from confirmed date/venue till event. As we are already planning for next year, this stuff should be much better organised.

Thanks a lot to all those who took the time to fill out the forms, they will help us make the event even better next time.

As usual leave any other feedback or suggestions for 2008 somewhere on this site and we will find them and consider them.

If you didn’t come this time, but are considering 2008, then you’ll be pleased to hear this years delegates rated the event so highly. We hope (and plan) to do even better next year so watch this space for dates and venues.

cheers

Simon

Conference Report Day 3

December 10, 2007

The Saturday session was targeted at developers.
Again the day went well and the feedback was very positive.

The first session was on the future of VBA
The session started old-Skool when I demoed recording XLM macros in Excel 5 on Windows 3.11. The XLM life story is probably our best guide to what may happen to VBA. The future of VBA is a concern for many developers, not just from a career point of view but also from a maintainability of current projects POV. We discussed what people thought might be key influencing factors and timescales.

The second session was a led discussion around programmability
We looked at VSTA in Infopath, VSTO in Visual Studio 2008 and discussed how these might impact our projects. We also had a candid discussion about security. This was an extremely useful session for getting a feel for the consensus view on some of these topics. Rather than being a one way ‘I think this’ presentation everyone chipped in with what they thought, what they had seen etc.

The afternoon started off with a look at xlls.
These are the recommended technology for writing Excel add-ins. They have some major benefits over VBA based xla’s (speed mainly), and some significant drawbacks (they are harder to write (well)). I walked through a demo VBA version I had built to explain the concepts and then went though the code of some xlls I have written. I also demoed some of the commercial tools in the area that take away all the C API pain. My performance demo was 266ms for a set of VBA functions down to 13 ms for the xll equivalent. (as a matter of interest the XLM version was 26ms, and whilst a bit more ugly than VBA, its not that much harder to write.)

The second session was a led discussion around data and Excel alternatives
We had a good chat about the ways people are using external data in Excel. And some great insight into some of the issues with certain providers. Essbase got a mention as a couple of us work with it regularly. We also had a look at OpenOffice Calc, and a discussion of where that was at in relation to Excel in enterprises. We also had a chat about Office 2007, with some of the attendees part way through roll-outs or pilots. Almost inevitably we touched on corporate culture, which so often seems to get ignored, but can have a dramatic effect on the work we do and the way we do it.

We also found time to discuss next year and what people would like to see and do.

Finally a few of us retired to the pub to watch the footie, have a drink and eventually go for a (delicious) curry.

Overall an excellent event I reckon, I’m already looking forward to next year. Hope to see you there.
Simon


Conference Report Day 2

December 10, 2007

Friday: Conference – Advanced, Add-ons – Intermediate.
Again the feedback was very positive all round.
The first session was Andys session on Excel Charting.
Andy took us through a range of different ways to present information on charts. In each case explaining the logic behind it as well as the actual steps to create the chart. Although the demo was on 2003, he explained where Excel 2007 was different.
I learnt a load of stuff in Andys talk last year, and I learnt a load of new stuff this time around.
Next up was my Excel VBA design tips
This covered a range of areas from IDE setup to design, class, named ranges etc. I demoed some of the hard to maintain code I have worked with in the past. The session was a bit of a challenge as I had pretty much lost my voice, but I think I scraped through. (apologies for the squeaking!)
After dinner Charles went through UDF performance
Charles had an example of performance improvement step by step from someone he had helped from a newsgroup posting. Sadly I can’t remember the numbers, but it was big from 20 second to 0.04 or something. He also took us through some of the underlying issues like the VBA call overhead, and ways to minimise that. He had also created several new UDFs to demonstrate how a bit of domain knowledge can enable you to create functions that perform better than Excel native ones. He also discussed some of the issues in creating and deploying professional UDFs.
Last slot of the day was Nick on Data Interchange
Nick took us through some of the many ways of connecting Excel up to external data sources. One of the big features in Office 2007 is Sharepoint integration and Nick demoed some of the ways they use that at his firm. By choosing the right structure in Excel 2007 (which seemed to be tables, tables, tables – now what was the question?) exposing and synchronising data with external sources (eg sharepoint lists) is almost trivial.

The addon classes were Charles on Optimising Calculation speed and Bob on VBA Tips and Techniques.

The day finished with some general questions and answers, mainly about which pub to go to before our planned Chinese meal. (which was excellent btw)

One point worth mentioning was during a couple of sessions I spotted presenters and delegates huddled around laptops in the foyer. There aren’t many opportunities to get such one to one focussed advice from the top folks in the country, this is surely one of the hidden benefits of conferences such as this. We are wondering about formalising this somehow for the 2008 event – any suggestions as to how are welcome.

Conference Report Day 1

December 7, 2007

Thursday – Conference Intermediate, Add-ons – Advanced.

Overall the delegate feedback was very positive about the venue, the content, and presenters.

The first session was my Pivot table slot.

I went through from basic intro to pivots, through some of the features I have found useful on client assignments. And features I tend to avoid, and why, and what I do instead. That included a 10 minute intro to the most useful parts of Access. I did a very brief demo of using some simple macro recorder stuff to synchronise multiple pivot tables, and ran out of time before punishing everyone with some of my gruesome real world examples.

Next up was Patrick with auditing spreadsheets

He gave us some insight into the work of a spreadsheet auditor, in particular the things to look out for, or sniff out. Poor practice being likened to a bad smell. He gave a useful overview of some of the available tools, both free and those that cost. We had a good discussion about names and external links, and plenty of other ‘quality’ practices. Patrick was also generous enough to offer a copy of his excellent book Spreadsheet Check and Control as a draw prize. So thats one delegate going back to the office armed with the knowledge to improve theri corporate spreadsheet quality.

(fortunately Patrick had left to catch his flight before he saw my random name picker fail rather embarrassingly with a #REF! error – whoops)

We then went off for dinner/lunch, for some more spreadsheet chat, this time over a buffet. I defy anyone to suggest there wasn’t enough food!

Bob got the graveyard shift after the food to talk about Visual Impact.

Considering the tube map as an presentation example was inspired, especially when alternative inferior examples were compared. Bob had plenty of excellent advice for clarifying the message, I particularly liked the talk (and demos) of removing ‘ink’ from a diagram to leave only what is genuinely important. In another section Bob went through and highlighted some of the weak informational content in many of Excel’s inbuilt chart types.

Nick closed the formal session for the day with a look at what new in 2007.

There are some great new features in Excel 2007, and Nick did an excellent job of demoing a selection of the most useful. It was especially useful that Nick has moved his whole firm over to 2007 – he has the insight that only real world use can provide.

We then had a short informal questions from the floor session, and a prize draw for Patrick’s book and some wonderful wooden puzzles Keith had brought.

It was then I realised what it was about conferences that bothered me – the dead spot between the last session and food. I had sorted the food no bother but had nothing arranged for the hour or so beforehand. Obviously we were going to a pub, but which?

We found a pub (eventually), then had a great meal at Pizza Express and those that were up for it went off for more drinks. They suffered on Friday!

Although the social aspect is a hard sell to the budget holder, in many ways it is one of the most important activities. Many a useful snippet is picked up in the pub, and by getting to know people better you can be more at ease asking questions. Its often easier to approach someone in the pub with a question than infront of 20 other people who may be more keen to get off and get their dinner.

Conference resources

December 7, 2007

If you attended the 2007 UK Excel User Conference you should have received an email from me detailing where you can access the supporting slides, documents, workbooks and code for all the sessions we ran.

If you didn’t get the email give me a shout and I’ll send it on. You can also leave a comment here if that’s easier. Break up your email address though if you don’t want to get spammed to oblivion.

If you didn’t attend then you missed out, but don’t worry there will be another event in September 2008 where you can make amends. In the meantime if there is something you would really like included next time around leave a comment on the 2008 suggestions page.

Returning a RANGE from a UDF

November 22, 2007

[From Charles: ]

I always wanted to write a UDF that returned a Range rather than the usual things like numbers. It sounded like a cool idea, and so when I was writing the FSUMIFS UDF for my talk at the forthcoming Cambridge UK Excel Users Conference I realised that this was my chance!

(FSUMIFS is a VBA UDF that handles multiple condition SUMs faster than SUMPRODUCT)

I could write a UDF that returned a range (well OK actually for error handling reasons it returns a variant containing a Range Object), and then the user could embed it in any other Excel function that handled a range (SUM, MEDIAN, INDEX, STDEV, MIN, MAX, SMALL …) without needing a special variation of FSUMIFS for each case.

I wrote the FSUMIFS version first, and then did the FRANGEIFS version.
Of course in a lot of cases the returned Range is a multi-area range created by a loop using UNION, which has some disadvantages:
– UNION is quite slow
– Some Excel functions like OFFSET do not work with multi-area ranges.

It all works well and the performance of the current beta version in almost every case so far except for small ranges is substantially (typically 5 to 30 times, up to 100 times in extreme cases) faster than the equivalent ubiquitous SUMPRODUCT formula.

BUT, having done all this work, I am now wondering why return a multi-area range rather when you could just return a (single-area) array (ARRAYIFS)?

ARRAYIFS would probably work just as well if not better (would avoid all those slow UNIONs and problems with function that don’t like multi-area ranges).
 
What do you think the pros and cons of returning Arrays rather than Ranges from UDFs are?

Charles Williams