Newsletter and blog updates

Today marked a new milestone for me, as I actually sent out a newsletter to everyone who has ever signed up at Excelguru.ca.  I was little hesitant, but figured I’d give it a go and see how people reacted.

I actually got some really nice feedback from people, which was great, even asking how to get others to sign up.

To that end, I’ve added a newsletter signup button on my Facebook page at www.facebook.com/xlguru.  And, since my blog theme was getting a bit stale, I’ve updated that as well, adding a link on the right hand side here.

I’m not totally sure I’m sold on the new theme, but let me know what you think.

PowerPivot training live in Victoria, BC!

Anyone who follows my website or Facebook Fan Page knows that I’m a huge fan of PowerPivot. Well great news now, that you can come and learn not only why this is the best thing to happen to Excel in 20 years, but also how to take advantage of it yourself!

I’ll be teaching a course on PowerPivot and DAX in Victoria, BC on November 22nd, 2013. While the course is hosted by the Chartered Professional Accountants, it’s open to anyone who wishes to subscribe.

If you’ve been trying to figure out how to get started with Power Pivot, you’re confused as to how and why things work, or you want to master date/time intelligence in PowerPivot, this course is for you.

100% hands on, we’ll start with basic pivot tables (just as a refresher.) Next we’ll look at how to build the PowerPivot versions and why they are so much more powerful. From linking multiple tables together without a single VLOOKUP to gaining a solid understanding of relating data, you’ll learn the key aspects to building solid PowerPivot models. We’ll work through understanding filter context, measures and relationships, and finish the day by building measures that allow you to pull great stats such as Month to Date for the same month last year, among others.

Without question, you’ll get the most out of this course if you’re experienced with PivotTables. If you don’t feel like you’re a pro in that area though, don’t worry! As an added bonus, to anyone who signs up via this notice, please let me know. I’ll provide you with a free copy of my Magic of PivotTables course so that you can make sure you’re 100% Pivot Table compatible before you arrive.

This is a hands on course, so you need to bring a laptop that is pre-loaded with either:

  • Excel 2010 and the free PowerPivot download
  • Excel 2013 with Office 2013 Professional Plus installed (yes the PLUS is key!)
  • Excel 2013 with Excel 2013 standalone installed.

If you have any questions as to which you have installed, simply drop me a note via the contact form on my website, and I’ll help you figure it out.

Full details of the course contents as well as a registration link, can be found at http://www.icabc-pd.com/pd-seminars-seminar.php?id=2849. Don’t wait too long though, as registration deadline is November 14th!

Hope to see you there!

Excel Power Map Sample

Yesterday, Microsoft released a preview of Power Map; a geo-spatial mapping Excel add-in, formerly know as GeoFlow.  It’s a pretty cool add-in that allows you to plot data based on geographic identifiers (longitude, latitude, country name, town names, postal codes, etc), and show it on a 3D or 2D map.  It works with tabular data sets, whether they be from and Excel table, database or Power Pivot.

The other cool thing is that, once you get it right, you can actually produce a video that can be shared with non-Excel users!  I’ve uploaded one of those to YouTube so you can check it out.

This video shows the wind speeds of hurricane Sandy as it travelled through the Caribbean to it’s eventual landfall on the USA’s east coast.  I used a heat map to show the speed (the redder it gets the faster), and played it over time so you can trace the path.

Pretty cool stuff!

If you have Excel 2013 Pro Plus, you can download the preview here:  http://www.microsoft.com/en-us/download/details.aspx?id=38395

OFFSET or Named Range – Which would you use?

I’m working on a spreadsheet where users will be able (required) to insert new rows at a later date.  When they do so, it’s critical that the section subtotals always… well… subtotal correctly.

The challenge, of course, is that you can’t rely on newly inserted rows being picked up by the subtotal formulas, so someone needs to check them.  At least, you can, but it takes more than just a SUM or SUBTOTAL formula to get it done.

I reached back to the method using a named range that I describe in the “Always Refer to the Cell Above” Excelguru KB Entry, resulting in a formula that looks like this:

SNAGHTML958c98

Of course, I don’t actually need to use the named range to do this.  I could make it work by using the OFFSET function in L66 as follows:

=SUBTOTAL(9,L62:OFFSET(L66,-1,0))

Either will work just fine, and will not be tripped up by a user inserting a new row within my boundary, so I should never (okay, never say never) run into an issue with this particular problem.

I’m curious which method you would use?  Named Range or OFFSET, and why…

A review of Add-in Express

A while ago I started transitioning from VBA to VB.NET again, attempting to build a tool to manipulate the new Power Pivot components in Excel 2013. While I was able to get part way to a working solution using Visual Studio 2012 and VSTO, I ran into two key issues:

1. VSTO seems to be “bit” specific, meaning that I would need to keep one version of the solution for 32 bit versions of Office, and another for 64 bit versions, and

2. While I could run my code, the Power Pivot engine would crash on me unless I opened Power Pivot BEFORE I ran any of my code.

Both of these issues were rather severe to me, as I didn’t want to maintain multiple versions of the same code, nor could I release something and expect users to open Power Pivot before running my project. A friend of mine suggested I try Add-in Express to deal with these issues.

In the spirit of full disclosure, I contacted them and asked if they had a trial version. They don’t, but offered to let me trial it if I’d blog on my experiences. I agreed to do that, and what follows is my honest observations of the software.

I do want to preface that, like my friend Rob Collie, I am not a “Read the manual” kind of guy. Ironic, since I write a lot of material, but I take the approach of diving in, and hitting Bing for a quick pointer when I get stuck. It usually causes me a lot of pain, but I tend to learn better that way.

So, here’s how it all came together for me…

When you install Add-in Express, you get a new set of templates. I started my project by creating a new ADX COM Add-in targeting the .NET Framework 2.0. (For those not in the know, in order to target Excel 2010 with VSTO, you need to use .NET 4.0 and Excel 2013 is .NET 4.5. So .NET 2.0 is way too early a framework to have ever even heard of Power Pivot!)

adx

From there you pretty much follow the prompts through the setup. I called it “Connect”, I set the minimum supported version to Office 2010 (since PowerPivot didn’t exist in 2007), I chose a Visual Basic Project, I selected Excel, and I left the rest of the settings at their defaults. Pretty easy, that part.

Once I had a project to work with, I created a new VB Module, just like normal, and built the code I would need to refresh my PowerPivot model and all the PivotTables. (Be aware that, for simplicity of the post, this is 2013 specific code, and will not work with Excel 2010.)

The key piece in this is making sure the xlApp declaration is correct, as you need to refer to the AddinModule portion in order to bind it to Add-in Express’s handlers, instead of just binding to the Interop.Excel objects. That change is what makes Add-in Express work:

  1. Imports Excel = Microsoft.Office.Interop.Excel
  2.  
  3. Module Module1
  4.  
  5. Public xlApp = Connect.AddinModule.CurrentInstance.ExcelApp
  6.  
  7. Public Sub RefreshPowerPivotTables()
  8.  
  9. Dim xlWorkbook As Excel.Workbook = xlApp.ActiveWorkbook
  10.  
  11. Dim ws As Excel.Worksheet
  12.  
  13. Dim pvt As Excel.PivotTable
  14.  
  15. 'Attempt to connect
  16.  
  17. Try
  18.  
  19. xlWorkbook.Connections("ThisWorkbookDataModel").Refresh()
  20.  
  21. Catch ex As Exception
  22.  
  23. 'Data connection could not be refreshed.
  24.  
  25. MsgBox("Sorry, but I could not refresh the model! Are you sure this workbook has one?")
  26.  
  27. Exit Sub
  28.  
  29. End Try
  30.  
  31. 'Refresh all PivotTables
  32.  
  33. For Each ws In xlWorkbook.Worksheets
  34.  
  35. For Each pvt In ws.PivotTables
  36.  
  37. With pvt
  38.  
  39. .PivotCache.Refresh()
  40.  
  41. .RefreshTable()
  42.  
  43. End With
  44.  
  45. Next
  46.  
  47. Next
  48.  
  49. End Sub
  50.  
  51. End Module

So far so good. Now I needed a user interface.

To be fair, it took me a bit to figure this one out. Once I finally realized that you need to open the “AddinModule.vb” portion in the solution explorer, then add a Ribbon tab to the canvas, then things got easier.

Despite reading very little documentation, with a little help from the blogs and articles on the Add-in Express site when I did get stuck, I was very quickly able to build a simple UI. There’s a good article on doing this here, which I wish I’d read earlier in the process.

As I say, it’s a really simple UI: a tab called “Model”, a single group, and a button with an image on it.

One criticism I do have is that it would be nice to be able to link the buttons and other controls to their callbacks inside the visual designer. As is, it’s a bit clunky, as you have to select the designer, then choose the other controls in the properties window. It’s not totally intuitive, but once you know where to look (read the article linked to above), it is workable.

My callback code for my button (which I didn’t bother renaming) is as follows:

  1. Private Sub AdxRibbonButton1_OnClick(sender As Object, control As IRibbonControl, pressed As Boolean) Handles AdxRibbonButton1.OnClick
  2.  
  3. Call RefreshPowerPivotTables()
  4.  
  5. End Sub

And with that done, it came time to debug. Again, fairly straight forward:

  • Build -> Build the project
  • Build -> Register ADX project
  • Make sure Excel is closed
  • Start the debugging engine

My “Model” tab showed up, with the command I’d built, as shown below:

onLoad

And with a single click (and a bit of a wait since Power Pivot is so slow), my Power Pivot data was refreshed, and the PivotTable updated to reflect the changes I made in my database. Notice the new customer and the new sales transactions for 8/1/2013:

onRefresh

But the best part is this:

  • The solution is deployable to both 32 bit and 64 bit Office platforms, and
  • I can open my Power Pivot project even if I run my code first, and it doesn’t crash.

I can honestly say that I fought that Power Pivot crash issue for about 2 months with VSTO, and I was really worried that it was going to kill my project completely. No amount of searching would turn up a fix, and other help calls didn’t yield any gold either; where they were answered, it was with a “don’t know” answer. Add-in Express has actually made this goal achievable.

I’ll also tell you that, while refreshing Power Pivot isn’t the focus on my full project, I have been able to use Add-in Express to successfully target and manipulate Power Pivot in both 32 and 64 bit versions of both Excel 2010 and 2013. I.e. multi version deployment with one code base. Pretty damn awesome.

I should also mention that their support has been phenomenal as well. Not only have they answered my emails, but I even ended up on a call with one of their lead people to examine why I didn’t seem able to use the debugging tools at first (a blog post for another day). 30 minutes, problem solved, and I’m good to go. Again, pretty damn awesome.

Readers of this blog will know that I don’t endorse very many products at all. Sure, I use Google Adwords and stuff, but I don’t write too many blog posts talking about how awesome a product is. Here’s my word on Add-in Express:

I’m sold. This product has been a life saver, and I won’t develop using VSTO.

Conditional Formatting Formula Inspector

David Hagar made a comment in my last blog post that I should add a userform to the code that I built there.  Since I had to use it on 40 workbooks, and figured that it could come in useful again, I did exactly that.  If you’d like to try it, you can download a copy.

It needs to unzipped and installed as an add-in, at which point you’ll end up with a CF Inspector button on the Formulas tab:

image

That takes you into the userform where you can type your text and click Find.  It will search all conditional formatting formulas in the workbook for your text, and return the list.

image

Once you select and item, it’s Applies To address and the formula will show in the boxes below so you can see them all.

It’s pretty simple, but could be useful since this kind of facility doesn’t exist in Excel by default.  Yes, it would be nice if it would do the replace as well, but given the stability issues I mentioned in the last post, I wasn’t going to try it on my workbook at this point.  Maybe in future if I added a backup facility first.

Let me know what you think!

Find and Replace In Conditional Formats

Yesterday I made a comment on twitter that I was trying to figure out if it would be faster to check the conditional formatting formulas in 40 workbooks manually to ensure they were correct, vs writing a tool to do it.  My buddy Jon Peltier tweeted back saying “Don’t know about faster, but it’s got to be more fun to code it.”  I thought about it, but then when I found that one workbook returned a count of 250 rules… the answer was to code it.

Well, yes, Jon was right… if your definition of fun is akin to performing home dentistry for your root canal.

The code I used to modify my conditional formats has been incredibly unreliable, and is returning different effects depending on the users’ machine that it is run on, the version of Excel, and maybe a few other things.  (It did different things after I had lunch too!)

I finally gave up on re-writing the conditional formatting rules, and ended up just running a macro to tell me which formulas I needed to edit:

  1. Sub FindCFIssues()
  2.  
  3.     Dim ws As Worksheet
  4.     Dim ftc As FormatCondition
  5.     Dim sFormula As String
  6.     Dim sFind As String
  7.     Dim sReplace As String
  8.     Dim lCount As Long
  9.  
  10.     On Error Resume Next
  11.     sFind = "ops-Internal"
  12.     sReplace = "ops_Internal"
  13.  
  14.     For Each ws In ActiveWorkbook.Worksheets
  15.         For Each ftc In ws.UsedRange.FormatConditions
  16.             With ftc
  17.                 sFormula = Replace(.Formula1, sFind, sReplace, 1, compare:=vbTextCompare)
  18.                 If .Formula1 <> sFormula Then
  19.                     Debug.Print ws.Name & ": " & .AppliesTo.Address
  20.                     lCount = lCount + 1
  21.                 End If
  22.             End With
  23.         Next ftc
  24.     Next ws
  25.  
  26.     If lCount > 0 Then
  27.         MsgBox "All done, a few issues were found", vbCritical
  28.     Else
  29.         MsgBox "No issues!", vbInformation
  30.     End If
  31. End Sub

Ultimately, this code searches for “ops-internal”, and flags my attention, because it should be “ops_Internal”.

Some weirdness here too… the “Next ftc” portion of the code runs until it’s gone through all the rules, then errors.  I had to add the On Error Resume Next above in order to let it continue, as it choked with just “Next” inside the other loop.

So at least I’ve got something now that quickly tells me if I have an issue, and prints out the offending rules in the VBA Immediate window.  I’d rather have the replacement done, but really don’t have the time to work through all the idiosyncrasies… err.. I mean fun… that will be involved in getting it right.

Review – Creating Data Models with PowerPivot How-to

A couple of weeks back I was approached by PackT Publishing, asking me if I’d be willing to read and post a review of Creating Data Models with PowerPivot How-to, by Leo Taehyung Lee.  As I’m always interested to see what others are saying in this space, I agreed.

The e-Book is 40 pages of content (after you discount the copyright, author profiles and other stuff that goes with every book), and is intended to cover off PowerPivot 2010 from install to basic usage.  Being an author, and having written many how-to webpages, I can say that it’s a lot to cover in that few pages.

Overall, you get a pretty basic glimpse into PowerPivot.  Topics covered include:

  • Installing PowerPivot
  • Installing SQL Express
  • Importing a bunch of tables
  • Making a basic Pivot
  • Making a Pivot Chart
  • Adding another table
  • Creating a relationship
  • Creating calculated columns
  • Optimization

The PowerPivot install is pretty simplistic; it basically points you to Microsoft’s site and tells you to follow the instructions.  I guess you can’t complain about that too much, although I think I would have highlighted the pre-requisites a bit more myself.

Honestly, I was more than a little surprised to see the installation of SQL Express in here.  Again, it was a very short coverage, and all in an effort to get access to the AdventureWorks database.  (I’ve never understood the fascination with AdventureWorks myself.)  Of all the pieces covered in this e-Book, I seriously have to question this one.

PowerPivot connects to Access databases, whether Access is installed or not, so that would have been a far easier route to demonstrate connecting to databases in my opinion.  Yes, I understand that corporate users don’t like Access, but that’s not the point.  Using an Access file requires no extra software be installed, unlike SQL Express, and therefore leaves no unneeded software on your machine once you are finished with the e-Book subject.  To my mind, it would have made much more sense to provide a downloadable Access sample database, or even push the user to download data from an online feed that didn’t require SQL Express installation.

Back to the actual PowerPivot specific stuff though… The Author’s approach to pulling in tables was quite interesting to me; he connects to the database and pulls in a whole bunch of them… way more than he needs.  I could criticize that, but I won’t, as that is EXACTLY how an Excel pro builds a PowerPivot solution.  Suck in as much data as possible, and then figure out what you need to link to get what you want.  The reason why this is interesting to me is that I had a conversation at the last MVP summit with some SQL MVP’s and they were horrified at this development approach.  SQL pros explore data first, publish as little as possible, so that the solutions are optimized from the beginning.  Excel people generally only optimize when their file is too big to email, or when it gets so slow they can’t stand waiting for it to refresh any more.

Cleaning up these unused columns was covered, although to my mind there were some key points that probably should have been made.  I would HIGHLY recommend that before any users starts pruning columns from their workbook that they save it first.  Then I’d refresh all of my pivots every 2-3 columns I remove.  Yes, it’s painful.  Yes, PowerPivot is slow to refresh.  But the first day you accidentally delete a column that has a dependency and catch it, you’ll be thankful.  Rebuilding PowerPivot model logic sucks.  (Trust me, I’ve been there.)

The only true technical issue I found in the book is related to the final statements regarding version compatibility issues.  The Author states that “… Excel files created with the older version of PowerPivot will be accessible in the new version…”  While that can be argued to as technically true, it is quite misleading.  PowerPivot files created in 2010 must be upgraded to be used in Excel 2013, and that writes permanent changes to the model structure, preventing it from being used in 2010 again.  I think that the author should have been much clearer in this regard as mixed version Offices will find no joy when trying to share/co-author PowerPivot files.

Overall, the e-Book gives a fairly decent intro level coverage to the topic.  It starts assuming no PowerPivot knowledge at all, and I can certainly see many things in here that remind me of the way I navigated my way through the basics of the PowerPivot learning curve.

If you’ve never used PowerPivot before, this e-book will walk you through some of the basics to get you up and running.  But be aware that PowerPivot is not easy, and you’re very quickly going to require a more in depth book to create good models, let alone master DAX (the real power in PowerPivot.)

If you have used PowerPivot, created a relationship between two tables and based a PivotTable off the related fields, then this book wont’ do anything for you.

To buy directly from PackT, you can do so from the following link:  http://link.packtpub.com/MQIfoM

Giving PowerPivot a Keyboard Shortcut

When I was down at Microsoft for the MVP Summit, I suggested that, as the “other most powerful part of Excel” that PowerPivot, like the Visual Basic Editor, should be given it’s own keyboard shortcut.  Since Alt + F12 is currently empty, and since it’s right next to the VBE’s Alt + F11 shortcut, it only seems logical that Alt + F12 would make a logical choice.

Well, I got tired of waiting… not that I ever expected they’d release a patch for that or anything.  So I up a macro to do it.  It’s pretty short, works with Excel 2010 and 2013, and can be stored in the Personal Macro Workbook.*

If you know your macros, or you already have code in your personal workbook, then add the Workbook_Open line and the Sub OpenPowerPivot to your project.  But if you’ve never written a macro, or you’ve never used the personal macro workbook, here’s how you do it:

  • Expose the Developer tab
  • Click on “Record New Macro”
  • Choose to store it in the Personal Macro Workbook.  (That will create your personal macro workbook.)
  • Stop recording
  • Press Alt + F11
  • Find VBAProject(Personal.xlsb) in the Project Explorer.  (The project explorer is the treeview on the left.  If it’s not showing, press CTRL+R to display it.)
  • Double click the ThisWorkbook module inside it
  • Paste the following code in the code pane:

Private Sub Workbook_Open()
Application.OnKey “%{F12}”, “OpenPowerPivot”
End Sub

  • Now open the Modules folder and double click Module 1
  • Replace the code in there with this:

Sub OpenPowerPivot()
On Error Resume Next

Select Case Val(Application.Version)
Case Is = 14
Application.SendKeys “%GY2″
Case Is > 14
Application.SendKeys “%BM”
End Select

On Error GoTo 0
End Sub

  • Close the Visual Basic Editor
  • Close Excel and say Yes when prompted to save changes to the Personal Macro Workbook
  • Re-open Excel and Press Alt+F12

Isn’t that just the height of lazy?  (I mean efficient!)  Smile

*Just a quick note here… if you float back and forth between Excel 2010 and Excel 2013 on the same machine, Excel locks the personal macro workbook for editing when you open the first Excel instance.  So if you open a new copy of Excel, you’ll get a prompt.  Just say okay though, and it will still work fine.

One hell of a weekend!

This is a personal post, but it was a HUGE weekend for me where some very cool things happened.  If you’re a fan of Excelguru on Facebook, follow me on Twitter, or are connected to me on LinkedIn, you’ve already seen a bit of this news, but this is a more detailed version of things.  Smile

It started with a soccer game…

We started bright and early, hopping into the car at 7:30 on Saturday morning to head down to Victoria.  Excelguru sponsors the Angry Jellybeans girls soccer team, of which I’m the coach, and we were off to play in the Gorge Soccer Association’s mini jamboree.  Three games over two days, and a good test for our Nanaimo based team to see how they’d fare against the Victoria clubs.  We pulled in shortly after 9:00 and played our first game at 10:00.  The girls came out flying, earning a 6-1 victory in their first match.  Confidence was high, the sun was shining, and it was turning into a great day.

We had some time to kill before our second match at 2:00, so we all hung out in the sun for a while, had a hotdog, and just enjoyed the day.  I hung out with my daughter for a while, just chatting.  I don’t remember most of what we talked about, but I do remember this… “Daddy, why do you have to go to an awards presentation tonight?  I want you to stay with us for the team dinner.”  It’s the kind of thing that kills you, you know?

As a bit of background here, my boss had nominated me for Vancouver Island’s Top 20 Under 40 awards.  The award recognizes the top 20 business and community leaders under 40 years of age on Vancouver Island.  I’d been shortlisted to the top 100, but the awards banquet was Saturday night in Courtenay… about a 3 hour drive from Victoria where we were playing.  Fortunately I’ve got a great co-coach in my friend Scott.  Scott agreed to look after the team dinner, my in-laws got a hotel room and kept my daughter overnight, and Deanna and I were set to head up to Courtenay immediately following the game to make it in time for dinner.

At any rate, what can you do to a question like that?  I looked at her and said “well, sweetie… if someone wants to honour you with an award, you should do everything you can to show up.  It’s only polite.”

She nodded, not happily mind you, but she accepted it.  Then came a question from behind her:  “Coach Ken, what award are you going to win?”

The question to them wasn’t IF I’d win, it was WHAT I’d win.  Smile

The funny part is that I hadn’t really told a lot of people about this at all.  I’d been shortlisted to the top 100, but I was actively trying to convince myself that I would be okay with that, even if I didn’t get recognized in the top 20.  That’s a hard battle to wage with yourself when you are as competitive as I am.  Really hard.  I don’t settle for short of the mark.  The award said Top 20… to me, that was the mark.  If I’d never been nominated, I wouldn’t have lost a minute of sleep over it… but I was… and that meant the prize was top 20.  And yet, I saw the bios of the other finalists, and they were incredible.

I laughed it off, and pretty soon it came time to square off in the next match so we were back to the pitch.

The second game… well… it didn’t start so well.  The girls didn’t play nearly as well as they should at all, and we were losing 0-1 at half time.  Scott and I pulled them in, had a little chat about how they were playing and how we knew they could play.  It was pretty cool, actually… we coached and they listened… and the second half was a different story.  The girls turned it on, scoring three, conceding one, and ending up with a 3-2 come from behind victory.  We were 2-0-0 on the day.

Equally cool though, was what happened on the sidelines.  We play 6v6 (including a keeper).  Since I keep my keeper in net the entire half, that meant I had 5 girls on the field to sub on and off, and 6 on the sidelines.  We’d taken to subbing 3 at a time.  If it was a corporate environment you would have called it a succession plan… and the effect was interesting.  6 of the girls decided that they should do some cheering… Who’s going to win?  Not the King, not the Queen, it’s the Angry JellyBeans!  3 girls went on the field, 3 came off.  And the three who were still on the sidelines pulled in the new arrivals… a new round of cheering started.  Then 3 went on and 3 came off… and the cheering started again… and again… and again…

It was very cute.  The cool part was this though… our opponents started a cheer of their own… and their coach was wide eyed.  “I’ve never heard these girls cheer before… wow!”  My Angry Jellybeans inspired the other team to do something they’d never done before.  Pretty awesome.  Smile

Game done, and with a 2-0-0 record, Deanna and “Coach Ken” hopped in the car, hoping to go 3-0-0 on the day.

The Top 20 awards

We pulled in to the Old House at about 6:10, and I hopped out of the car still dressed in soccer pants and my Angry Jellybeans hoodie.  Working my way through the tuxedos and evening gowns I reached the front desk.  The clerk asks “Are you checking in”.  I nodded, and he pulls the last folio off the counter behind him.  “You must be Ken… you need to change!”

I laughed, but he was right.  We bolted to the room, changed superman style, and were out the door about 10 minutes later.

After a quick drive to the Filberg Centre, we parked, hoofed it up the stairs and… holy crap!  There was a red carpet rolled out for us… wasn’t expecting that!  I also wasn’t expecting to be given a glass of champagne as I walked in the door either… made me kind of feel like a movie star!

Because of the late arrival from the soccer tournament, we missed most of the mix and mingle time, but we still had time to chat with Russell (Fairwinds Asset Manager), his wife Mary, Jim (my boss) and Julie (Jim’s wife) who had all come out to support me.  A couple of drinks, some chatting about the day, and a little conversation with a couple of nice guys that rounded out our table of 8 made for a good wind down time from the crazy drive we’d just experienced to get there.

They also gave us a nice full colour book of all the nominees and their bios. We started flipping through them… which only helped to make me more nervous!

Next up were a few speeches including one from Premier Christy Clark, who had skipped her son’s hockey game to come and talk to the finalists before the awards.  She had a great speech that had the crowd laughing. Not political, which was nice, but rather focused on us.  You can see some of that in the Chek News coverage.  Next came dinner, then they got to the awards.

Each of the top 100 finalists was given a nice framed certificate to hang in their office.  Here’s a picture of Deanna and I (smartphone shot, so quality not great), with mine:

Top100

And then came the announcement that they were going to start announcing the Top 20… Russell slams his hand on the table “A hundred bucks on Ken!”

So here’s all this support around me, all this encouragement, all this faith… and me still trying to convince myself that it’s okay if I don’t make the Top 20.

And then it started to roll out, Oscars style.  They called Troy Wilson.  As he’s working his way to the stage they’re reading a more complete bio.  Troy makes his acceptance speech, and they call another name.  The process repeats and they call another, and another… and another…

And I’m telling myself “It’s okay.  Top 100 of all under 40’s on the Island is awesome.”

… and another, and another…

“You don’t have to WIN everything you know…”

… and another …

“It’s a hell of an achievement, be proud!”

… and “KEN PULS!”…

The words that came out of my mouth at that moment were deep and profoundly inspired…. “Holy Shit.”

I won.

I can actually say that I was a bit in shock.  But I managed to get up there and put together a speech on the fly that I’m pretty happy with.  You can see it here:

And then something remarkably cool happened.  As I was heading back to my table to show off the trophy a young guy stops me… I can’t remember the exact words, but it went something like this:

“Ken, I just wanted to tell you that I took one of your Excel courses a couple of years ago.  You really inspired me, and I decided to pursue my accounting designation.  I’m now enrolled and pursuing my CMA.  I’m a huge fan of your website and spend a lot of time there too.  I just wanted to say thank you.”

To me, that is the ultimate compliment.  I was so stunned I never even got his name, but wow.  That was inspiring.  Smile (If you read this, drop me a line!)

Then back to soccer…

After the awards were all presented we returned to the room to get some sleep, turning in around 12:30 AM.  The alarm was supposed to go off at 6:00 AM (which felt like 5:00 AM thanks to the daylight savings time change that night.)  Thankfully Dee woke up right at 6:00 as neither my phone nor hers actually went off.  45 minutes later we were back on the road heading back to Victoria for the final Angry Jellybeans game vs the Gorge FC host club.

When the girls found out that I had won they all surrounded me and gave me a great big Angry Jellybeans cheer, which was really touching.  We then had a chat about our goals for the final game, which ended up with the girls deciding that they were going to “win it for Coach Ken”.

My girls are a talented team, and they play hard.  But what I saw on the field on Sunday was remarkable.  They did everything that Scott and I had coached them to do all season long.  They spread out, they were aggressive, they run into open space, they passed to each other, they connected and they scored… and scored… and scored…  At 6-0 Scott and I were just about to implement our “3 passes before shooting rule” when they scored again.  So in the rule went… and they scored again… and again…  By half time it was 9-0.

We came back on after the half with the girls very clear that they had to connect 5 passes before they were allowed to shoot on net.  It was actually great.  There is nothing more amusing that seeing the look on a parents face when one of your players is standing in front of an open net, and they turn and pass the ball because they’ve only completed 3 passes of the required 5. The Gorge girls also spread out and got more aggressive.  Because of that they did manage to score a goal as well, which was great.  The final score ended up with an 11-1 win for us.

Again though, my girls led a 1 hour cheering session through the game.  And just after half time Gorge responded.  Out of nowhere I hear one of my players say “they just cheered us back… come on, let’s have a cheer off!”  And sure as anything they did.  They’d cheer, wait for the other side to respond, and launch into a follow up… over and over again.  It was such a weird counter-point… we were creaming them on the field, and they were still having FUN.  And that is just plain awesome.

My only regret the whole weekend is that I let my girls run the score up as high as they did before putting in the passing rules.  I never want to beat a team by 10 goals, as that is disheartening for anyone.  I want these girls, no matter the team, to play soccer year after year.  But at the end of the day I can’t help by feel that this time the girls on the other side had fun anyway.  They were all smiles when they shook our hands, and the coaches from the other side thanked me for my actions in stopping the slaughter while still letting them play.  It really felt like a win-win.

I can’t begin to say how proud I am of my Angry Jellybeans.  They played amazing, and they inspired a bunch of kids to have a great time and have fun in ways that their coaches haven’t seen before.  It was an inspiring end to a great tournament, and a great weekend.

photo

And in the end…

On Saturday night I became part of a very exclusive club… one I’m proud, honoured, humbled, and flattered to be a part of. (As this was the first year of the event, there are only 20 members to date.) What’s amazing is that I never pursued this, I just did what I do. With my Excel stuff it’s what I love to do, with my other stuff what I feel that I need to do, but regardless, I was recognized for just being me, and that is pretty cool.

And on Sunday I saw the culmination of something very cool in my amazing Angry Jellybeans.  Not only the wins, but the fact that they inspired others in ways that seems like it could only come through some kind of magic.

To all of those of you have liked my Facebook statuses, LinkedIn updates, tweeted me and expressed congratulations on my award through any other manner, Thank You. It means a great deal to me.  I truly feel like a lucky man right now.  Smile