Author Archives: Arun Ravi

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

Converting Monthly data to Weekly data in Excel

A lot of the time, there are differences between different pieces of data received from a client/ data team – one being when different pieces roll up to different levels in a nested structure, for eg. weekly vs monthly vs yearly. Is that problem? Let’s take a look..

Taking the example here – let’s assume we have Monthly data for spends for campaigns while we’re working at a weekly level with Sales. Thus, we’re forced to split Monthly figures into the Weekly format to be used for any purposes – from reporting to modeling. It is often required to accurately split the monthly into weekly, so as not to double count or misrepresent the spends in the weekly format. One of the biggest problems – overlapping days between the week and months.

For eg. from the image below – the week of 27th Jan 2014 contains days from Jan ’14 as well as Feb ’14. In order to accurately get the spends for this week, we need to factor Jan & Feb spends weighted by number of days.Excel Monthly-Weekly

Here’s a neat trick that solves the problem with one formula – that’s right, just ONE formula!

=IF(C$2<$B3,MAX(0,C$2-EOMONTH($B3,-1)+6),MAX(0,MIN(7,EOMONTH($B3,0)-C$2+1)))/(EOMONTH($B3,0)-$B3+1)

And you get this –Blog - Excel v1
Blog - Excel

 

 

 

Credits to this guy – “AlphaFrog”. His post (http://www.mrexcel.com/forum/excel-questions/627101-converting-data-monthly-weekly.html) provided the basis for this post. Below is a quick snapshot from his page showing the flow of logic.Example - Blog

Round “time” in Excel

How does the Time format work in Excel?
– The DATE & TIME is integrated to be represented by one number – a floating point number!
– The decimal part characterizes the time, while the integer part the date.
– [0,1] = [00:00 HRS,00:00 HRS + 1 day] This is the set that determines the time of the day. For eg, 0.5 = 12 NOON.
– Hence, “1/1/2012 12:00:00 PM” is nothing but “40909.5”

What kind of functions would we need to run on Time formatted data?

Here’s 2 examples –
– Rounding time to the nearest hour, half hour or quarter hour (for eg. converting running time into buckets of 15 min intervals)
– Finding the difference in time between one day and the next (How many hours has elapsed between onset time and end time?)

Below is a set of syntax to round time to the nearest 15 min window.

= (ROUND((A1 * 1440) / 15, 0) * 15) / 1440
= TIME(HOUR(A2), ROUND((MINUTE(A2)/60)*4, 0) * 15, 0)
= ROUND(A1*(24*60/15),0)/(24*60/15)
= INT(A1*(24*60/15),0)/(24*60/15)
= B2-MOD(B2,15/24/60)
= MROUND(MOD(B2,1)*60,15) (Note: Excel 2007 Addin – Not tested)

All of these work, and they resort to using the decimal numbers to get to the TIME manipulations.

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

Extracting Text from Right of String in Excel

When working with Strings in Excel, it doesn’t get any easier than this. You can practically do anything you want with the strings and also visualize as you do them.

Some Functions that are frequently used – LEFT(), RGHT(), MID(), FIND(), SUBSTITUTE(), TRIM(). These are even more powerful when used in combination. LEFT() & RIGHT() are used to extract a part of the text from LEFT/RIGHT of the string input. MID() is used to extract text from anywhere in a string given the start and end positions. FIND() is used to locate a character or string in a string. SUBSTITUTE() is used to substitute a character or string within a string. TRIM() is used to trim any trailing spaces.

So, what’s the BIG deal with these functions anyways?

Consider extracting the First Name from every record in the file – assuming every name is stores <First Name> <Last Name>. There are a number of ways to do this – I’d use a combination of LEFT and FIND. Here’s a quick syntax – LEFT(A2,FIND(A2,” “,1)-1)

Perfect. That was easy. Now what?

What if I wanted to extract the Last Name of every Record?

While the RIGHT() function certainly extracts text from the right, when used with FIND() – things get a little complicated. So, RIGHT(A2,FIND(A2,” “,1)-1) does not work!

But this does – RIGHT(A2,LEN(A2) – FIND(A2,” “,1) + 1)

Great! We’re set here. Is there any more to this?

What if we had First, Middle & Last Name for every record? What then? How will you extract the last name in this case? Or, let’s take the case of Addresses – can you get just the PINCODE for every address in the database?

What this entails is – nested FIND() to locate the correct delimiter. For example, in the case of First, Middle & Last name, we’d have to locate the second ” ” (space) for the start number for Last name. Here’s the syntax – RIGHT(RIGHT( A2, LEN(A2) – FIND(A2,” “,1) + 1),LEN(RIGHT( A2, LEN(A2) – FIND(A2,” “,1) + 1))-FIND(RIGHT( A2, LEN(A2) – FIND(A2,” “,1) + 1),” “,1) +1)

Too complicated? What if we have more than 2 delimiters? What if we don’t know how many exist?

It surely doesn’t have to be so complicated. Why can’t FIND() just look from the right of a string?!

There indeed is a neat solution to this.
Here’s the ideaHow about if we counted the number of occurrences of the delimiter per string, and then, temporarily change that delimiter to another delimiter and use that in the FIND function I’ve mentioned above. Voila!!

Still confused?? Take a look below for a step by step explanation –

Step1 :

Find number of occurrences (this is the most important step in all this)

= LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””)) (What a great way to get the number of occurrences – very thoughtful!)

Step2:
Substitute the last occurrence only!
= SUBSTITUTE(A1,”,”,”|”, … ) (… follows from the function above which gives the instance to be substituted)

Step3:
Extract using the first formula above
RIGHT(A1, LEN(A1) – FIND(“|”,…,1))

Putting them all together
=RIGHT(A1,LEN(A1)-FIND(“|”,SUBSTITUTE(A1,”,”,”|”,LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””)))))

And there we have it… This can be further improved using the ‘INSTANCE’ option in SUBSTITUTE() function.

This should handle most of the situations that calls for text extraction directly from the right. Just copy that last function and put it in the cell next to where you want to extract (ofcourse, redirect the A1 to your cell) and you’re good to go.

Don’t have to thank for this one! 🙂

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!