Category Archives: Excel

Excel Modeling World Championships 2014

Message from John, one of “the organizers” to all my fellow Excel mates –

Invitation to the Excel Modeling World Championships 2014 ModelOff 2014

We’d like to invite members of the ​Excelytics community to the annual Excel and Financial Modeling World Championship 2014 Event (known as ModelOff, www.modeloff.com). The Advanced Excel educational competition helps celebrate Excel in Financial Services. The fun, challenging and innovative competition has a mission to inspire skill development with Microsoft Excel, Financial Modeling and Financial Analysis which is central to global businesses and communities. The competition showcases some of the fastest, hard-working and talented Excel minds from 100+ countries. Round 1 starts on 25th October 2014 (8 weeks away)

Background

Over 3,000 participants competed in the ModelOff 2013 event. Major Global Partners and Sponsors are: Microsoft, Intralinks, S&P Capital IQ, Kaplan Education, Bloomberg, AMT Training, Corality and Ernst & Young.  Participants come from diverse companies and jobs – such as Analysts, Associates and Managers at Investment Firms and Accounting Firms, CFOs, Analytics Professionals, In-House Excel Gurus and Consultants with a shared passion for Microsoft Excel and Finance.  Students comprise ~35% of all entrants worldwide – most studying Commerce, Accounting, Finance and Masters university qualifications. The countries most represented have typically been: United States, UK, Poland, Russia, Canada, Australia, India and Hong Kong.  Female participation is ~20% of all competitors – hopefully higher this year (the reigning champion is Hilary Smart 26yo from London).

 

How It Works

The ModelOff competition involves two online qualification rounds (2 hours each) conducted simultaneously around the world. The Top 16 performers are flown to New York for the Live Finals at the offices of Microsoft and Bloomberg in early December 2014.  Questions are mostly case study and multiple choice format – with some ranging from a basic understanding of discounted cash flow (DCF) analysis, 3-way integrated cash flow models to more complex project finance and simulation techniques.  Some basic Accounting, Finance and Excel knowledge is likely needed to progress to Round 2.

 

Free Training and Preparation

All past questions and tests from ModelOff 2012 and 2013 are free and available on the ModelOff website. The organizers believe in accessible excellence and this can be a great starting point for anyone looking to become involved for the first time and improve their Advanced Excel skills in Financial Services and Financial Modeling.  We actively encourage all participants to visit all our community partners, bloggers, our major global sponsors during and following the event for their own learning, mentoring and professional development. Competitors in the Top 10% of ModelOff 2014 will be eligible for exciting local and international opportunities, offers from community partners and fun learning experiences (e.g. Trips to Microsoft Excel in Redmond). We’re also hosting free networking events in major Financial Centres including Hong Kong, London, Sydney, New York and Regional Meetups in the coming months for anyone interested in networking and mentoring with Excel-users in their local cities. Entry to the competition is $20 for students and $30 for professionals.

John R Persico is one of the lead organizers of the ModelOff event. John can be contacted at john@modeloff.com.  Get involved at: www.modeloff.com

Advertisements

Where EXCEL fails?!

All the while it’s been Excel can do this, and Excel can do that. But what are its pitfalls? Where does it fail? What should we beware?
Surprisingly, there’s a whole community that’s at this topic! And I only recently stumbled upon them. That while trying to learn the new software “R”.

Here’s a link to Excel’s shortfalls, something that only authentic Statistical softwares are capable of.
http://biostat.mc.vanderbilt.edu/twiki/bin/view/Main/ExcelProblems

I’d like to take the time to highlight some of these below –

1. Excel’s greatest boon – the spreadsheet page – is it’s greatest bane! Formulae and Values in the same cell, with no line of demarcation between the functions and the end results. Some operations such as sorting result in erroneous output.

2. Data Validation is a big problem

3. Computational complexity and inconsistencies, not to forget the time.

4. Bad graphing processes (I always thought Excel graphs were good!)

5. Excel Statistics is anything but Statistics! The probabilities, accuracy, plots, regression etc.. nothing matches with Statistical Software packages. (RAND function is potentially a great bug)

6. Solver is not something that can be relied upon, convergence issues.

7. No “HISTORY” to track the list of data actions/manipulations for ease of debugging or other reasons.

And the list goes on.. I’ve only scratched the surface I believe, but these are pretty much the high level accusations.

And now, what I’d like to take away from all this –

I know and understand that every software has its set of pluses and limitations, and Excel is no exception. It’s Microsoft, it’s unpredictable, it’s got bugs, it’s not perfect.. you can go on..
But it’s still the most widely used and understood piece of software, easy to use since we already know it so well, and works wonders on quick visual inferences. And Excel is powerful enough usually to do stuff that is beyond the average tasks, and I’ve myself used it for purposes that are beyond what I thought possible.

IF anything, it’s a great software, and currently, the easiest at handling data, charting and computation. And as long as we don’t rely on using hardcore Statistics using Excel’s built-in tools, I don’t see any harm why we can’t boast of Excel’s functionality in other domains! Also, most stuff above about Excel’s limitations on data complexity or computational complexity is something we already know, and here’s the best part – once used to Excel, we end up working around it always. ALWAYS! We just know where it hits the limit. It’s like having this old piece of machine and knowing how to use it. Agreed there are better tools out there, but currently, this one rules the majority of the population and until a time when the others take over, we can still boast of Excel’s capabilities and keep learning newer stuff to boost our skills and make our everyday life easier…

Coloring the scatter plot by groups in Excel

Scatter plots are generally a nice tool to represent data, especially ones where we’d like to show a pattern of some kind. But what happens when we have more than 2D data for a scatter plot?

Let’s take an example here. This is how the data looks like –

TableChart1

While the above scatter plot gives a general idea on the overall trend of the data, we’re unable to plot the 3rd dimension in the chart directly – Region grouping. But this can be achieved in Excel with a little exercise in VBA.

This is how we want our chart to look like –

Chart2

VBA Code that makes this possible –

With ActiveChart.SeriesCollection(1)

    For i = 1 To rows
        With .Points(i)
            .MarkerStyle = pointSymbols.Cells(i, 1).Value2
            .MarkerBackgroundColorIndex = pointColors.Cells(i, 1).Value2
            .MarkerForegroundColorIndex = pointColors.Cells(i, 1).Value2
            .markerSize = sizeOfMarker
        End With
    Next
End With

Another way to achieve the same is –

For Each pt In ActiveSheet.ChartObjects(1).SeriesCollection(1).Points
    With pt.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
    End With
Next

Here’s a neat link that shows how this can be done – http://www.clear-lines.com/blog/post/Excel-ScatterPlot-with-labels-colors-and-markers.aspx

Getting filenames of all files in a DIRectory ~

A very elementary request, but this had us searching all over the internet for solutions! We wished to get all the files in a folder, as we had saved multiple data files of Excel in one folder, and went about retrieving data from each file as and when required.

Trying to accomplish this is VBA is one thing, but what if we didn’t need to update it every time, and just needed it this once.. I wouldn’t want to waste my head over this by creating some complex VBA stuff.. then, where do we turn to? DOS!

It’s been a while since I heard that being used actually! But felt quite good trying to work it out.. Anyway, here’s the code to getting the filenames into one text file from a folder.

  1. Go to the file location in DOS using CD <PATH NAME>
  2. DIR /B /O:N > filename.txt
  3. DIR /B /O:N /S > filename.txt to get all files from subfolders as well
  4. DIR /B > filename.txt also works
  5. DIR > filename.txt gets all details other than file name too

Some basic stuff huh?!! But good to know it when you need it!

 

Did you know! Conditional Formatting in Excel 97-2003/2007 limits to 3

The Conditional Formatting of Excel 97-2003/2007 has the major limitation that only 3 formats can be specified (excluding the one that’s already present in the cell).

Lets take an example – Can I format a cell in 4 different ways as below?
1. A dollar format if it’s tabulating currency
2. A number format for frequency counting table values
3. A percentage when applicable
4. When ‘#N/A’ appears, I’d like it to dissolve into the background (Font = white)

And Excel throws up an error.

The workaroundVBA! Always!

Additional tipThere’s a ‘Stop if True’ feature in the Conditional Formatting rules. Conditional formatting executes the format rules in the order in which they are added! So, if you select this feature, the Conditional Formatting stops at that particular rule, and doesn’t go further! It’s similar to this piece of code below –

If <condition = true> then
    <body of statements>
    <exit sub>
End if

If we ended up using an ‘If’ with an ‘Exit Sub’, that’s exactly what Stop if True’ would do – exit the function if any of the ‘If’s’ is true!

Excel 2007: Specifications and Limits

Something that I came across while searching for something else… And this is such vital information, that you hardly ever stumble upon this when required!

Worksheet and workbook specifications and limits

Feature

Maximum limit

Open workbooks Limited by available memory and system resources
Worksheet size 1,048,576 rows by 16,384 columns
Column width 255 characters
Row height 409 points
Page breaks 1,026 horizontal and vertical
Total number of characters that a cell can contain 32,767 characters
Characters in a header or footer 255
Sheets in a workbook Limited by available memory (default is 3 sheets)
Colors in a workbook 16 million colors (32 bit with full access to 24 bit color spectrum)
Named views in a workbook Limited by available memory
Unique cell formats/cell styles 64,000
Fill styles 256
Line weight and styles 256
Unique font types 1,024 global fonts available for use; 512 per workbook
Number formats in a workbook Between 200 and 250, depending on the language version of Excel that you have installed
Names in a workbook Limited by available memory
Windows in a workbook Limited by available memory
Panes in a window 4
Linked sheets Limited by available memory
Scenarios Limited by available memory; a summary report shows only the first 251 scenarios
Changing cells in a scenario 32
Adjustable cells in Solver 200
Custom functions Limited by available memory
Zoom range 10 percent to 400 percent
Reports Limited by available memory
Sort references 64 in a single sort; unlimited when using sequential sorts
Undo levels 100
Fields in a data form 32
Workbook parameters 255 parameters per workbook
Filter drop-down lists 10,000

 

Calculation specifications and limits

Feature

Maximum limit

Number precision 15 digits
Smallest allowed negative number -2.2251E-308
Smallest allowed positive number 2.2251E-308
Largest allowed positive number 9.99999999999999E+307
Largest allowed negative number -9.99999999999999E+307
Largest allowed positive number via formula 1.7976931348623158e+308
Largest allowed negative number via formula -1.7976931348623158e+308
Length of formula contents 8,192 characters
Internal length of formula 16,384 bytes
Iterations 32,767
Worksheet arrays Limited by available memory
Selected ranges 2,048
Arguments in a function 255
Nested levels of functions 64
User defined function categories 255
Number of available worksheet functions 341
Size of the operand stack 1,024
Cross-worksheet dependency 64,000 worksheets that can refer to other sheets
Cross-worksheet array formula dependency Limited by available memory
Area dependency Limited by available memory
Area dependency per worksheet Limited by available memory
Dependency on a single cell 4 billion formulas that can depend on a single cell
Linked cell content length from closed workbooks 32,767
Earliest date allowed for calculation January 1, 1900 (January 1, 1904, if 1904 date system is used)
Latest date allowed for calculation December 31, 9999
Largest amount of time that can be entered 9999:59:59

 

Charting specifications and limits

Feature

Maximum limit

Charts linked to a worksheet Limited by available memory
Worksheets referred to by a chart 255
Data series in one chart 255
Data points in a data series for 2-D charts 32,000
Data points in a data series for 3-D charts 4,000
Data points for all data series in one chart 256,000

 

PivotTable and PivotChart report specifications and limits

Feature

Maximum limit

PivotTable reports on a sheet Limited by available memory
Unique items per field 1,048,576
Row or column fields in a PivotTable report Limited by available memory
Report filters in a PivotTable report 256 (may be limited by available memory)
Value fields in a PivotTable report 256
Calculated item formulas in a PivotTable report Limited by available memory
Report filters in a PivotChart report 256 (may be limited by available memory)
Value fields in a PivotChart report 256
Calculated item formulas in a PivotChart report Limited by available memory
Length of the MDX name for a PivotTable item 32,767
Length for a relational PivotTable string 32,767

 

Shared workbook specifications and limits

Feature

Maximum limit

Users who can open and share a shared workbook at the same time 256
Personal views in a shared workbook Limited by available memory
Days that change history is maintained 32,767 (default is 30 days)
Workbooks that can be merged at one time Limited by available memory
Cells that can be highlighted in a shared workbook 32,767
Colors used to identify changes made by different users when change highlighting is turned on 32 (each user is identified by a separate color; changes made by the current user are highlighted with navy blue)
Excel tables in a shared workbook 0 (zero)

 Note   A workbook that contains one or more Excel tables cannot be shared.

 

References –

Array Formulas in Excel – Using VBA

Array Formula is generally used while working with Excel Worksheets – very handy when quick “Matrix Math” is required. For eg. cross products of columns/rows or multiple products across rows & columns with custom filters etc.

But if I wanted to automate this using VBA, how do I do it?

Range(“”).FormulaArray = <formula>

And we’re done!!!

So, what’s the limitation? <formula> cannot exceed 256 characters in length!

‘.FormulaArray’ only supports when the character length is under 256, an error throws up otherwise. Surprisingly, this is not the case with ‘.Formula’ – no such limit on this.

How do you know when this happens? You get a run-time error 1004, stating inability to accept Range. Check out Microsoft’s link to know more.

So, what’s the workaround? Or is there one?! Ofcourse! There always is one!

1. A potentially notorious workaround I should say – using the ‘Sendkeys’ from VBA to send a ‘F2+Shift+Ctrl+Enter’ after entering the ‘.FormulaArray’ as a ‘.Formula’!

Range(“”).Select

SendKeys “{F2}^+{ENTER}”, Wait:=True

Application.Wait (Now() + TimeValue(“00:00:01”)) ‘(Usually required since VBA doesn’t necessarily wait for the execution of the ‘SendKeys’ statement!)

Although slightly twisted, I liked this approach since it solves the problem quite easily. BUT, beware, this could create bugs in the code execution or final results in ways you can’t even think of! And this is primarily due to the disconnect between VBA & Excel interface!

2. This is probably a very well thought out solution and easier in terms of operational complexity but requires more work in terms of code. It’s best read directly at Dick Kusleika’s blog – http://www.dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/.

What this does is simply break the <formula> into multiple strings and submit the first ‘.FormulaArray‘ with the first segment with fillers that will be substituted the remaining bits of the broken formula! Ingenious!

And now you’re all set to be automating Array Formulas from VBA…