Articles by Ron

You are currently browsing Ron’s articles.

Monday, September 29, 2008 was an ugly day.  How ugly do you ask?  The Dow Jones Industrial Average (DJIA) plunged nearly 800 points with violent moves all day as the House rejected the bailout plan.  Ouch babe.  This one will unfortunately be one that will be remembered.

Yahoo Finance provides historical data for Equities and Indices such as the DJIA.  What do we do with a bunch of DJIA closing and their associated dates?  Hrmm… graph anyone?

Download the Sample File

This graph is fairly easy to create but it does not tell us much.  After all, I think it is pretty obvious that the US economy has grown since 1928…  I am thinking that it would be a really nice & spiffy if we were able to create an interactive version of this graph.

Download the Sample File

Remember the scrollbars from when I purchased a vehicle?  Yeah, let’s use them.  Let’s also use another cool feature, “Name Manager“.  You can find the Name Manager within the Formula Tab within Excel 2007- it also exists in Excel 2003.

Quick explanation for Excel Names.  For many financial related spreadsheets, the concept of interest rate is used extensively.  Lets say we designate cell B1 as our interest rate. To make our spreadsheet formulas easier to read, let’s Name cell B1, iR.  To do this we have two main options, either go into the Name Manager and create the variable iR (=B1).  Or simply, edit the cell name next to the formula bar.

Now referring to interest rate is easy, instead of =B1, we can utilize =iR.  The main advantage of this approach is that it is cleaner and easier to understandNames can also be dynamic.  As you can see, I have defined three complex names with complex formulas.

Names can also be referenced as series within Graphs.  Once we Select Data > Edit Series, we are able to refer to the Names previously defined.

So here is ultimately how the Excel spreadsheet works.

  1. Scrollbars are created which drive our starting date and zoom features.
  2. Names are created which create arrays based on the scrollbar values.
  3. Graph Series refer to Names.
  4. Graphs will update based on real time changes in the scrollbar!

So with that, let’s analyze a one year return.  I see a sea of red.  Ouch.

Download the Sample File

Please, let’s hope for an Economic recovery soon!

Not too long ago, I decided it was time to evolve my mode of transportation.  Sure, I had the tools of the trade- shoes, bicycle, subway pass.  But I desired something more, something greater than myself which would weigh over 2 tons.  Yes, my friends- I was looking for a new car.

Being a car buyer is quite stressful especially if you have limited experience.  Although some of you may believe the process of purchasing a vehicle is an artistic notion, I decided I wanted to consider just a few factors: price, engine, miles per gallon, passengers, automatic/manual transmission.

Research is very important for consumers.  The first item that I wanted to determine in my new quest for a new mode of transportation is, what are the current hot cars?  Thanks to my friends at Yahoo Auto, I was able to determine 8 cars that I wanted to analyze.  A few tweaks here and there and I was able to determine basic information which I would need for my analysis.

Great.  Since I want to use Excel for the basis of this analysis, I have decided to build a nifty graphical user interface (GUI).  OK- great.  So what does this mean?  For the model intensive folks, this means no more lame-O blue cells (or whatever color you firm uses) to represent inputs or random highlighted cells where you “input”.  No, sir.  We want to build something that looks prettier and functions more intuitive.  That’s why I have decided to add some fancier items such as scrollbar, checkbox, drop down and radio buttons.  Something like this…

Before I begin, a few little notes:

  • This model contains basic calculations which will not be discussed in this post.
  • Conditional formatting strikes again!  (Remember, use this technique to quickly troubleshoot + dynamically highlight cells instantly)
  • I have officially began my migration to Office 2007!  (the file is saved as 2003 XLS format).

As many of you know, Excel 2007 has a pretty fancy item called the Ribbon.  We are looking for the Developer tab within the ribbon.  Don’t see it there?  By default, many of you may not have this option available.

  • Office Button > Excel Options > Popular > check “Show Developer tab in the Ribbon”.  (Alt F > I > Alt D)

Now that we have our Developer tab sitting nicely on our Ribbon.  Let’s discuss why we added this tab.

  • Now let’s look for the ActiveX controls.  Developer > Insert (Alt L > I).  Here are some pretty nifty inputs we can utilize to make the GUI that we so desired.
  • Each of these ActiveX controls has their own individual properties including Appearance + Characteristics.  There are tons of options, so please take the time to explore each individual option.
    • Before you can modify the properites of each ActiveX control, you must first enter “Design Mode”.  Developer > Design Mode (Alt I > DM).  To view each ActiveX control’s property simply choose an existing item and choose Properties within the Developer tab.
    • Of all the options, there is one which is VITAL.  It is known as LinkedCell.  This is the link between the ActiveX control and Excel.  Make sure you set this property to refer to the Excel Spreadsheet. 
    • There are other useful properties as well, but I will let all of you dig into the spreadsheet to figure them out! I’ll update the post if anything is confusing.

At the very end, we are able to use the GUI + the settings.  Voila.  Here are our filtered results!

_
In case you are wondering- the blue cells mean that there is a match in criteria.  The number of smileys designate the number of total matches.  So while you are at it- download the Excel spreadsheet and like a fancy car… take it out for a spin.  Vroom Vrrrrroom indeed.
_
~ FIN~
_
_
PS.  You might be wondering what car I purchased.  Yes, indeed …1998 Red Toyota Camry.

The Beijing Olympics games is all about the shimmer, shine and glamor: the Olympic Medals. If I were interested in comparing the number of Olympic Medals by country using Excel, how would I do this?

Download the Sample File

To make tasks such as this a bit more dynamic, I often like to create user interfaces. When creating an interface, the general rule of thumb is that it should be simple, allow the user to select his/her preferences and produce accurate results (both detailed and executive summaries).

Download the Sample File

To create this user interface, I will use the Excel functions MATCH and OFFSET to fetch data. This data is the basis to populate the graph and perform the simple difference calculation.

MATCH

  • =MATCH(lookup_value, lookup_array,[match_type])
  • MATCH returns the index / element position for the specified lookup value based on an associated array. The optional match type argument is FALSE if a lookup value must match an element within the array. TRUE or blank (default) in combination with a sorted array will return an index based on the range of values.
  • Ex, lookup_array = {1, 3, 5, 7, 9}.
    • MATCH(5, lookup_array, FALSE) = 3 because 5 is the 3rd element within the lookup_array.
    • MATCH(4, lookup_array, FALSE) = #N/A.  Since match_type = FALSE an exact match must occur which means that the lookup value must exist within the lookup array!
    • MATCH(4, lookup_array, TRUE) = 2. lookup_array must be sorted if match type is TRUE.
      • lookup values from 3 to <5 will return an index of 2,
      • lookup values from 5 to <7 will return an index of 3, etc, etc.

OFFSET

  • =OFFSET(reference, rows, columns, [height], [width])
  • Think of OFFSET in terms of Excel key navigation. Excel defaults at cell A1.
    • Want to get to cell B1? It is easy- simply press the right arrow or in other words, shift forward one column.
    • Cell A2? Shift forward one row / press the down arrow.
    • In Excel, OFFSET(A1, 0, 1) = B1 and OFFSET(A1, 1, 0) = A2. Therefore, OFFSET(A1, 2, 1) = B3.
  • OFFSET can also retrieve array / matrices of information when using the optional height and width arguments.

How does it work?  MATCH allows us to determine the position of each country and the medal type.  The MATCH results filter to OFFSET where Excel can retrieve the appropriate data from our table.

We have created this exhibit with just a few tools: Excel’s built in graph formats, conditional formatting, and the MATCH and OFFSET Excel functions. This type of model can have its various uses whether it is to create an exhibit to quickly compare financial ratios of equities, varying benefit plan design costs, and even Olympic medal counts.

Note: OFFSET and MATCH are not the only functions we can utilize to create the output, some of you may prefer MATCH with either VLOOKUP or HLOOKUP.

Download the Sample File

Let’s recap.

  1. You want to learn about Excel.
  2. You want to buy computer parts to build a PC.
  3. You research the prices at Best Buy and Circuit City.
  4. You research the prices at more stores.

You have optimized the computer part prices and determined that the lowest total cost is $640.63. Before you pat yourself on the back for your hard work, you must realize that although cost is important, it means little unless you determine where to shop. The following Exhibit cleanly indicates the best price option and its respective store.

Download the Sample File

One option to create the exhibit is to use a manual “select cell, highlight, change font” (rinse, repeat, recycle many many times). This manual process may be fine for small projects, but the task can quickly become tedious if information changes or expands quickly.  Let’s create the exhibit using Excel’s highly powerful and usable Conditional Formatting (Excel 2003: Format > Conditional Formatting).

  • If an additional “cheaper” column is available, this task is easy. Choose the appropriate cells and set the “Cell Value Is” to refer to the Cheaper column.  (In the example below, column I refers to the before mentioned “cheaper” column.)  I have chosen the “equal to” option, but there are many other options that are easy to understand.

Download the Sample File

  • Now, lets make the assumption that you have no interest in creating an additional “cheaper” column.  No problem.  Instead of the default “Cell Value Is“, change the condition type to “Formula Is“.  This allows you to write an Excel formula which must solve to True or False (or equivalent).  In the example below, columns C through G refer to prices specified in Best Buy through Walmart.

Download the Sample File

Conditional Formatting is a very useful tool. When working for a financial firm, I used Conditional Formatting to error check, show real time changes in Equity Prices (Bberg BDP function), and highlight cells for concern.  What is really nice about Conditional Formatting is that you can utilize all the popular Excel formulas.  In many ways, you can create instant feedback for your spreadsheets because you can set up cells to format automatically based on your input/formulas.  Unfortunately, there are restrictions.   Excel 2003 only allows 3 conditions, the size of the file will increase, and large spreadsheets can become noticeably slower.  Microsoft expanded this tool in Excel 2007 and offers >3 Conditional Formatting formulas and more intuitive default conditions.  I have yet to explore all the Excel 2007 additions, but perhaps this could serve as a post in the future!?  Thanks to Peter for his original post.

Note*  If you need to build a computer, do your own research.  These prices most likely do not reflect actual store prices!

Download the Sample File

Lets revisit Summing Element-Wise Minimum once again. To recap:

“Suppose you plan to buy several computer parts from either BestBuy or Circuit City. The two stores have different prices for each product. If you buy from one store exclusively, you won’t get the best deals possible. To optimize the value of your hard-earned dollar, you want to find the least amount of money necessary to buy one of each part.”

Lets suppose you are not convinced you have truly optimized your hard-earned dollar. Therefore, you decide to add prices for Office Depot, Duane Reade and Walmart.

Download the Sample File

An easy way to solve this problem would be to create a new column which utilizes the MIN() function. But, since you believe in optimization you want it ALL in one single cell. What are your options? I guess you can start creating a crazy array that will be difficult to understand such as the following example which determines the cheapest total price between 3 stores (arrays):

{=SUM(IF([array 1]<[array 2],IF([array 1]<[array 3],[array 1],[array 3]),IF([array 2]<[array 3],[array 2],[array 3])))}

Looks… um… um… right. So outside of adding a column and creating a crazy array formula, what other option is available? Enter Microsoft Visual Basic! It seems that many Excel based users may go pretty far in their careers without ever understanding the super hyper intimidating but useful tool. (Excel 2003 keyboard shortcuts provided)

  • Tools > Macro > Macro… (Alt + F8) and
  • Tools > Macro > Visual Basic Editor (Alt + F11).

So now, wouldn’t it be nice if we could just write a function and refer to a bunch of cells and get exactly what we want? Guess what? With Visual Basic, you can… With the Visual Basic code below, you can add as many stores as you like while solving for the optimal price.  It is important to note that everyone has their own programming style, you can feel a glimpse of my style with the code below.

Download the Sample File

I certainly encourage you to download the sample file because I have provided comments within the Visual Basic Module. That said, I do want to make a few quick notes.

  1. A Visual Basic function (if non-Private) can be called from the Excel Spreadsheet directly. The spreadsheet contains the formula, =minSumItNow([Array 1 … Array N]).
  2. Visual Basic Function differs from Sub because Function must return a value. The value is set by assigning the Function name.
  3. [Array Name].Rows.Count and [Array Name].Columns.Count are very useful. This allows the user to understand the size (vertically and horizontally) of arrays. This is useful because users do like to include a range of cells as an input. (For Each is useful as well and will be discussed in a later post)
  4. Visual Basic might seem like more work to learn, but the rewards are plentiful. Learn Visual Basic to:
    • Impress your colleagues.
    • Work more efficiently.
    • Automate tedious tasks.
    • Unlock the true power of Excel.

Download the Sample File

Peter explained an useful array based formula in his post. I won’t repeat what he was trying to accomplish, but a piece of his post really got me thinking. So for my very first, real post, I will blog about a real issue that many of us will face at one point.

Here’s the scenario. You are the valedictorian from the USA Today’s #1 ranked Liberal Arts University in Northern Kentucky. Your summa cum laude in Art History has taken you to the most prestigious consulting firm, ABC Consultants LLC (“ABC”). Your manager, Peter, is very excited to have you on board and tells you about a really exciting project that he worked on last year. It deals with the super-innovative and enterprising firm that is breaking new grounds everywhere it turns. This firm is known as XYZ Corporation (“XYZ”). Peter is really proud of the work he did last year (after all, XYZ renewed its contract with ABC, so why not be proud Peter?) and directs you to a spreadsheet named “XYZ Model.xls“. He wants you to take a look at the model and understand it by tomorrow morning so you can have a long conversation with XYZ’s CEO about its methodologies. Before you leave, Peter mentions:

“XYZ Model.xls may have a ‘convoluted approach and a super long formula‘”.

Here’s that formula:

{=SUM(C4:C12*((C4:C12-D4:D12)/IF(C4:C12=D4:D12,1,
ABS(C4:C12-D4:D12))-1)/(-2)+D4:D12*((C4:C12-D4:D12)/
IF(C4:C12=D4:D12,1,ABS(C4:C12-D4:D12))+1)/2)}

Array formulas are great because you can cram a ton of information into one single cell. The problem is, they are often difficult to understand because they can be very involved! So where would you begin?

1) Organize the array formula. When programmers code, they love to use indenting to help organize their thoughts. Let’s use a similar approach:

{
_____=SUM
_____ (
__________C4:C12 *
__________(
_______________(C4:C12-D4:D12) /
_______________IF(C4:C12=D4:D12,1,ABS(C4:C12-D4:D12))
_______________-1
__________) /
__________(-2)

__________+

__________D4:D12 *
__________(
_______________(C4:C12-D4:D12) /
_______________IF(C4:C12=D4:D12,1,ABS(C4:C12-D4:D12))
_______________+1
__________ ) /
__________2
_____)
}

2) Look for patterns. Many Excel array formulas process similar cells of information. Let’s define variable lists A through E as the following:

A = C4:C12
B = D4:D12
C = C4:C12-D4:D12
D = IF(C4:C12=D4:D12,1,ABS(C4:C12-D4:D12))
Let’s also have … E = C ÷ D
_____Note (if we want to go wild)*
_____C = A – B, D = IF( A=B,1,ABS(C) )

3) Now that we have organized our formula and defined variables, we can combine the results from Steps 1 & 2 together to create a cleaner formula.

= ?(A x (E – 1) ÷ -2 + B x (E + 1) ÷ 2)
= ½ x ?(-A x (E – 1) + B x (E + 1)) | E = C ÷ D

4) The formula seen in Step #3 looks complex, but to me it looks a lot simpler (and less confusing) then the original formula!  Let’s take advantage of Excel and expand out the array formula.

Download the Sample File

5) Great.  Now that we have confirmed our result with the original array formula’s result, we are done, right?  No way!  Remember Peter’s initial request.  He would like you to be able to  hold a conversation about the model.  Let’s understand the formula!

= ½ x ?(-A x (E – 1) + B x (E + 1)) | E = C ÷ D

  • C is simply the difference between A and B.
  • D is the absolute value of C or 1 if C is zero.  Why is D not just zero when C is zero?  I will tell you soon.
  • E as mentioned is C ÷ D.  This results in E becoming either -1, 0, 1.  Back to our non-zero D riddle above.  Since E is C ÷ D, dividing by D = 0 would result in a #Div/0! (Divide by Zero) error.  Mathematically speaking, anything divided by zero gets everybody confused!
  • Now we know that E is -1, 0 or 1.  Since we have calculated E, lets forget about arrays C and D.
  • Let’s take a look at each scenario.
    • If A < B Then E = -1
      • = ½ x (-A x -2 + B x 0)
      • = ½ x (-A x -2)
      • = A
    • If A = B Then E =0

      • = ½ x (-A x -1 + B x 1)
      • = ½ x (A + B) |  A = B
      • = A or B (it does not matter!)
    • If B < A Then E = 1

      • = ½ x (A x 0 + B x 2)
      • = ½ x (B x 2)
      • = B
  • Based on the results above.  Peter has duplicated a Min like function for A and B.  This formula does achieve what Peter was originally set to accomplish “Summing Element-Wise Minimums“.

6) If you become completely confused by the formula, removing yourself from the spreadsheet will often clear your mind.  Do your best to understand what you can, but do not spin your wheels!  Don’t be shy.  Have a conversation with your manager (or a knowledgable colleague) to either confirm or gain further understanding.  As you gain practice and experience with array formulas, you will begin to understand these formulas quickly.  Just be sure when you create a model with complicated array formulas, document the process to avoid further confusion!

Download the Sample File

I finally felt like I was able to figure out Excel 2003.  I knew all the keyboard short-cut commands without even needing to look at the keyboard, was proud of my ability to convey data in fancy graphical representations, and was getting very comfortable with building Visual Basic macros.  Yes, with regards to Excel 2003- I felt absolutely dangerous.

Now its all different.  Here on my desktop is the brand-spanking new Microsoft Excel 2007 (right, it is not that new anymore considering it is already 2008).  Gone now are the familiar menus I once knew and replaced with a seemingly more complex simpler ribbon system.  It feels new to me, and I seem to work really really slow.  That makes me scared.  Very scared.  My goal is to become proficient and speedy with the newer Excel 2007.  In the process, I hope my learning becomes yours as well.

It is important to note that Excel 2007 has a NEW format unrecognizable by Excel 2003.  Does this mean that Excel 2003 users should completely avoid my posts?  Nope!  Since not all of my concepts will focus on the newer excel, the 2003 “.xls” will be available.  I will be sure to make this distinction!