Making Box and Whisker Plot in Excel

There's a simple method to construct a box and whisker diagram in Excel, by taking advantage the fact that box and whisker diagram is just a scatter plot with values fixed on one of the axis to give height to the box. Given a sample of data in column A of an Excel worksheet, here are the parameters necessary, along with the formula needed to obtain those values:

• Minimum: =MIN(A:A)
• 1st quartile:  =QUARTILE(A:A,1) or =PERCENTILE(A:A,0.25)
• Median: =MEDIAN(A:A)
• 3rd quartile:  =QUARTILE(A:A,3) or =PERCENTILE(A:A,0.75)
• Maximum: =MAX(A:A)

Once those values are obtained, here is the most crucial component of the diagram: pasting the values into this table for a horizontal box and whisker diagram. Simply reverse the columns for vertical diagram:

Min	1
Min	3
Min	2
Q1	2
Q1	3
Q1	1
Median	1
Median	3
Q1	3
Q3	3
Q3	1
Median	1
Q3	1
Q3	2
Max	2
Max	1
Max	3

Once the numerical values are filled, this is simply a set of coordinate points. Select the table, and choose Scatter Plot > Scatter with Straight Lines and Markers. As imagined, the values 1, 2, 3 are arbitrarily chosen; since we are dealing with one-dimensional data, and just want the other axis to demonstrate some height to the boxes, the values 1, 2, 3 are the easiest to work with. In fact, as soon as the scatter plot is made, it's best to delete the values of that axis. Here's a box-whisker-plot of 50 randomly chosen numbers between 0 and 1:

To see that this is indeed the result of the table from above, trace the points on this plot, by going down the rows of the table. The labels on the plot should help to trace. Start from (min, 1) at the bottom left of the plot. The next row of the table is (min, 3) at the top left of the plot. In order to move onto the Q1 value on the plot, we must go down to (min, 2) and then to (Q1, 2) in order to preserve the straight edges of the box and whisker plot. And so on, the table lists the points that must be traced over to ultimately make the plot.

Introduction to Array Formulas in Excel: Transpose

Standard formulas in Excel begin at something as simple as =SUM(A1:A3), typed into a single cell. Suppose that formula was written in cell B1; in this case the sum of the numerical values from cell A1 to A3 is returned in cell B1. Among their many capabilities, array formulas can return multiple results, into multiple cells. Let's illustrate that with a simple case of transposing, or converting a row of array into a column, or vice versa.

Suppose that we have a table with the integers 1 through 5 listed in the first column, and the square of those integers in the second column, calculated through formula: for example, cell B1 contains =A1^2, and so forth. In the end, the table looks like this:

1	1
2	4
3	9
4	16
5	25

Now say that we want to transpose this table, so that instead of a 5x2 table, it'll be 2x5 table with the integers 1 through 5 in the top row, and the square of those numbers in the bottom row. We can actually do this easily by copying and instead of simply pasting, right-click the house and select Paste Special. When the option box comes up, check the Transpose box and hit OK. The table should look like this as envisioned:

1	2	3	4	5
1	4	9	16	25

However, these two tables are not linked by any formulas. Suppose that in the original table, we want to replace the value 5 with 10. Next to it, 100 will automatically appear since the calculation is based on the formula. However, the transposed table will not automatically change. This may be problematic if the data need to be linked.

Fortunately, there is a way to do transposing but through copy / paste special, but rather through formulas, just like =SUM() or A1^2. However, do realize that the output is going to be a 2x5 table, not a single cell. Here is where array formulas can perform this task. To begin, select and activate a range of 2 rows by 5 columns. While those cells are highlighted, begin typing =TRANSPOSE(A1:B5). As before, the range may be selected by the mouse. Now, if we hit enter right at this stage, we would get an error. The key to using array formulas is to press Control + Shift + Enter to enter the formula. Upon hitting those three keys, the transposed table will appear. Not only will it appear, it will be linked to the original table. Any change in the original table will automatically be reflected on the transposed table.

If we click on any cells that comprise the transposed table, we see that their formula all say {=TRANSPOSE(A1:B5)}. The curly brackets are indicative of the cell being part of an array. Another characteristic of an array is that its cells can't be modified. Try editing the content of any of the cells and an error message appears. The only way to exit this message is to press Esc.

In future posts, we will see how array formulas can perform multiple calculations on one or more items.

Quick Summary on Options

Options are the most common forms of derivative, financial instruments that derive their values from those of other assets. Call options give the bearers the ability to buy an asset for a specified price, called the exercise or strike price, on or before the expiration date. Put options, on the other hand, give bearers the ability to sell an asset for a specified price on or before the expiration date. For a call option, here are the possible outcomes:

• If exercise price = current stock price, the option is "at the money" call.
• If exercise price > current stock price, the option is "out of the money" call.
• If exercise price < current stock price, the option is "in the money" call, as the bearer can purchase the stock at the lower exercise price and sell at the higher current stock price.

Buyers of call options are betting that the stock price will be higher than the exercise price by the expiration date. The seller, or writer, or the call option bets that the stock price will fall below the exercise price. For put options, it's the opposite. Those who buy put options bet that the stock price will fall, so profit can be made by buying the security at the lower price and selling at the higher exercise price. On the other hand, writers of put options bet that the stock price will increase.

Numerous factors go into how options are priced. If the price of the underlying security increases, so will the price of call options given a fixed exercise price. This is because there will be a greater range such that the option will be "in the money." By similar arguments, the lower the exercise price, the higher the value and price of the option. The results are opposite for put options in these cases. However, if volatility of the underlying security increases, so will the value of both call and put options. This is because the downside loss is fixed; bearers of options do not need to buy or purchase the asset if the price is unfavorable. Potential upside, on the other hand, increases the value of the option. Similarly, time to expiration increases the value of both options. Finally, rising interest rates depress the present value of exercise price and makes the call option more valuable. Dividends depress the expected capital gain of stocks and the value of the call option.

1% Up and 1% Down ≠ 0% Cumulative Effect

Suppose the indices are up 1% one day, and down 1% the next. Is the cumulative effect 0%? What if they were down 1% and then up 1%? It turns out that in both cases, the cumulative effect is not 0%, but rather negative. To understand this in the most simple manner, realize that 1% of 1.01 (after 1% gain) is more than 1% of 0.99 (after 1% loss). Therefore, the 1% loss is greater than 1% gain. And due to the communicative property of multiplication, it doesn't matter whether up or down occurred first.

The cumulative effect is 1.01*0.99 = 0.9999, not 1. To truly offset an 1% loss, the gain required would only be 1/0.99-1 = 1.010101%, which is above 1%. This residual decreases as the magnitude of the gain and loss becomes smaller. Let x be the same magnitude for the gain and loss, so x=0.01 in the case above: (1+x)*(1-x) = 1-x^2. Compared to the unchanged initiate value of 1, the difference is simply x^2. So when the gain and loss is 10%, the end result is 1% lower. When the gain and loss are 1%, or 0.01, as was the case above, the end result is 0.0001 deviated, and the cumulative effect of 1.01*0.99 = 0.9999 confirms that.

While 0.9999 may not seem much different from 1, the effects can easily add up. Supposed that the portfolio repeatedly bounced up and down 1% each day for the entire calendar year. Given 250 trading days, so 125 of such cycles, an $100 portfolio will only be worth $100*0.9999^125 = $98.76 after the year, corresponding to 1.24% loss. And because the magnitude-residual relationship is quadratic as observed above, the effects multiply quickly: given 2% back-and-forth for a year, the same $100 portfolio would only be worth $100*0.9996^125 = $95.12, or 4.88% loss.

MATLAB Code for 1-D Random Walk

See related post: 1-D Random Walk with Equal Probability

The previous post ran a simulation of one-dimensional random walk. Here is the MATLAB code that was run to achieve the the results, along with explanatory documentation:

numRuns = 100000;       %# of resets to position 0
numTrials = 1000000;    %# of trials per run
probLeft = 0.5;         %Here, equal probability going to left and right
zero = 0;               %# of times on position 0
negative = zeros(1);    %Array to keep track of # of times on negative numbers
positive = zeros(1);    %Similar array for positive numbers
for run = 1:numRuns     %Loop over the # of runs
    position = 0;       %Reset position for each run
    for count = 1:numTrials     %Loop iver the # of trials
        roll = rand;            %Simulated roll
        if roll < probLeft      %Going to the left
            position = position - 1;
        else                    %Going to the right
            position = position + 1;
        end
        if position < 0
            if abs(position) > length(negative) %If the position has never been visited before
                negative(abs(position)) = 0;    %Initialize the value to avoid error
            end
                negative(abs(position)) = negative(abs(position)) + 1;  %Increase counter
        elseif position > 0
            if position > length(positive)      %Similar tasks as above, but for positive positions
                positive(position) = 0;
            end
                positive(position) = positive(position) + 1;
        else
            zero = zero + 1;        %If landed on zero
        end
    end
end
y = [negative(end:-1:1) zero positive]; %# of times landed on each position
x = -length(negative):length(positive); %Position vector
plot(x,y)
xlabel('Position')
ylabel('Number of Times on the Position')
title('1 Dimensional Random Walk')

In this code, the variable 'probLeft' had a constant value of 0.5, reflecting on equal probability of going to the left and right. Further adjustments can tweak to define this variable to allow for the probability to reflect on the current position.

1-D Random Walk with Equal Probability

This is the absolutely fundamental case of random walk. Start from position 0 on an one-dimensional number line. One unit step is taken on each step, with equal probability of going to the left and the right. The number of times stopped on each position is kept track. What's the distribution like after many steps?

It turns out that because the probability of moving in either direction is independent of the current position, increasing the number of steps in each trial doesn't help to reduce much variance. Instead, "resetting" the trials back to position 0, while keeping and continuing the tracking, helps to yield a smoother distribution curve. For this exercise, 100,000 runs were simulated, each with 1 million steps. In another word, 1 million steps were simulated, the position reset to 0, another 1 million steps simulated, the position reset to 0, and so forth until 100,000 of such cycles were completed. Overall, that's 100 billion steps. Here was the result:

Upon extraction of variable values, the following useful data were attained:

• Most extreme positive value landed on: 4,729
• Most extreme negative value landed on: 4,900
• Number of times landed on position 0: 79,820,608
• Percentage of time position was between [-100, 100]: 15.07%
• Percentage of time position was between [-500, 500]: 58.15%
• Percentage of time position was between [-1000, 1000]: 85.00%
• Percentage of time position was between [-2000, 2000]: 98.85%

Kings of 8th Seed Champion

See related post: 3-0 Playoff Series Comebacks in Professional Sports

On Monday, the Los Angeles Kings of National Hockey League defeated the New Jersey Devil to win the Stanley Cup, the first time that an 8th seeded team has won the championship in either NHL or NBA, the two leagues that employ the 16-team playoff format. The Kings have been the story throughout this playoff. Despite barely making the playoffs, the 8th seed Kings went onto defeat the Vancouver Canucks, St. Louis Blues, and Phoenix Coyotes, the 1st, 2nd, and 3rd seeded Western Conference teams, respectively, on its way to the Stanley Cup, where it defeated the Devils in 6 games for its first Stanley Cup trophy ever.

In NBA, only give 8th seeded teams have won the first round of the playoffs, most recently the Philadelphia Sixers this season against the Bulls. In the most successful case, the 1999 New York Knicks managed to make it to the NBA Finals, where it ultimately fell to the Spurs. Over at NHL, there have been more cases of upsets. In the most successful case, the 2006 Edmonton Oilers managed to not only make it to the Stanley Cup Finals, but also managed to come within 1 game of winning it, before losing to the Hurricanes in Game 7.

Not only did Kings it all, they won their series in decisive fashions. Until Game 5 loss at New Jersey, the Kings had won every single road game (10-0) of the playoffs. The ten road roads tie a single postseason NHL record. Given that the 8th seeded Kings began all series on the road, they were able to take commanding leads in all series. In fact, they were also able to win all Game 3's at home, thereby beginning each series up 3-0. Only a handful of teams have come back from 0-3 hole. The Kings put all four of their opponents in that unbearable hole, before finishing the series in Game 4 (vs Blues), Game 5 (vs Canucks and Coyotes), and Game 6 (vs Devils). The 16-4 run to championship was quite an impressive one, and the Kings doing so with an 8th feed makes it quite unique.

Sources:

• http://bleacherreport.com/articles/1218100-la-kings-complete-cinderella-run-to-claim-stanley-cup-glory
• http://www.sportsnet.ca/hockey/2012/04/23/nhl_playoffs_top10_first_round_upsets/
• http://allball.blogs.nba.com/2011/04/30/ranking-the-8-1-upsets-in-nba-playoff-history/

Micronetics Inc (NASDAQ: NOIZ) Merger with Mercury Computer Systems Inc (NASDAQ: MRCY)

On an overall dismal day for the markets Monday, as all three major US indexes fell over 1%, jumping a whopping 94.79% was Micronetics Inc. (NASDAQ: NOIZ). Headquartered at Hudson, NH, Micronetics "designs and manufactures high quality, reliable, microwave/RF components, multi-function subassemblies and highly integrated subsystems," according to its website. Over the weekend, it was acquired by Mercury Computer Systems, Inc (NASDAQ: MRCY) via a merger for $14.80 per share. Shares of Micronetics, which closed 7.49 last Friday, jumped to close at 14.59 on Monday.

Financials reveal that in the 12-month period ending in Q1-2012, the revenue and profit of Micronetics were at $45.97 and $3.41 million, up from $35.30 million and $1.52 million, respectively, the previous 12-month period. Shares of Micronetics have not been as high as the acquisition price of 14.80 since July of 2006. According to its website, Mercury, headquartered at Chelmsford, MA with market cap of over $360 million, is the "leading supplier of very high-performance digital image, signal and sensor processing solutions for prime contractor customers in the defense industry, as well as the Intelligence Community."

Sources:

• http://www.bostonherald.com/jobfind/news/technology/view/20220611mercury_computer_to_acquire_micronetics/srvc=home&position=also
• http://www.micronetics.com/about.html
• http://www.mc.com/company-information/who-we-are/
• http://www.google.com/finance?q=NASDAQ:NOIZ
• http://www.google.com/finance?q=NASDAQ:MRCY

Maximum Possible "Jeopardy" Score and its Probability

What's the theoretical maximum score on the TV show Jeopardy? To get the maximum possible score, one has to ring first on all answers (or have a different first ringer answer incorrectly), answer everything correctly, bet the maximum amount on all occasions, and then a luck factor plays in. The Daily Double clues are located at the optimal spot. If all of the non-luck factors are obtained, what's the probability that yields all of the lucks in place to obtain the theoretical maximum score?

In the first round, there are 6 categories, with each with $200, $400, $600, $800, and $1000 clues. Add them up and multiply it by six: 6*(600*5) = 18,000. However, there is one Daily Double clue in this first round. Since the Daily Double clue annuls the monetary value originally associated with the clue, the Daily Double has to be hidden behind a $200 clue to minimize the annulling, and thus maximizing the overall score. Subtract 200 from 18,000 leaves 17,800. If everything is wagered on Daily Double and the answer is correct, that leaves 17,800*2 = $35,600 just from the first round.

In the Double Jeopardy round, everything is doubled: 6 categories, each with $400, $800, $1200, $1600, $2000. Furthermore, there are two Daily Doubles. Start with the $18,000 figure calculated from the previous round. Double that, since it's Double Jeopardy: 18,000*2 = 36,000. Now, we need to backtrack two of the $400 values to account for the Daily Double. That leaves 36,000 - 2*400 = $35,200 gained purely from Double Jeopardy, without the Daily Doubles. Don't forget to add the amount from first round, and that leaves 35,600 + 35,200 = $70,800. Now for the two Daily Doubles, as well as the Final Jeopardy, everything is wagered and answer correctly. That is 3 times of doubling the score: 70,800*2^3 = 70,800*8 = $566,400.

Now the question is, if suppose someone can ring first on all answers, answer everything correctly, and still bet the maximum amount on all occasions, what's the probability that the Daily Doubles will be located in the correct spot to allow this theoretical maximum score? Well, in the first round, it has to be hidden in one of the $200 clues. That's a 1/5 chance of that happening. Independently from that, the chance of the two Daily Doubles both hidden under $400 in Double Jeopardy is (6/30)*(5/29). The first one can be in any of the six $400 slots, out of the 30 overall clues. After that's taken care of, the second one has to be in any of the five remaining $400 slots, out of the 29 overall remaining clues. Therefore, the overall probability is (1/5)*(6/30)*(5/29) = 0.00689655172, which is exactly 1/145.

So overall, if someone can ring first on all answers, answer everything correctly, still bet the maximum amount on all occasions, and has the 1/145 luck chance, it is possible to get the theoretically maximum score of $566,400 on Jeopardy. To give a comparison, that value is over 1/5 of the amount Ken Jennings earned during his record-setting 74-win streak.

Sources:

• http://www.usatoday.com/life/people/2004-11-30-jennings-jeopardy_x.htm

I-490 Extension Debate in Cleveland

Anyone who lives in the southeastern suburbs of Cleveland driving to Case Western Reserve University or Cleveland Clinic knows this dilemma: there just isn't an effective highway route that connects University Circle to the suburbs. Even for those who don't directly work there, it's an important issue: Cleveland Clinic alone is one of the biggest employers in Northeast Ohio. The following map illustrates the issue:

 

The closest highway interchange is northward at I-90 and MLK Drive. While this actually is a pretty effective route for commuters coming from the northeastern (via I-90 westbound) and western (via I-90 eastbound) directions, it is not an efficient route for people coming from the southeastern direction. Taking I-271 northbound and transferring to I-90 westbound is an enormous detour. Taking I-480 westbound and transferring to I-77 northbound is still quite a detour. Instead, most commuters know to get off I-271 at Chagrin Blvd and drive another 20+ minutes on the local roads. Another option, getting off I-480 at Broadway, takes a bit less time, but most suburban commuters avoid it due to the long stretch of local roads in the Cleveland city proper. Either way, as shown on the map, there's the enormous area enclosed by I-77, I-271, I-480, and I-90 that lacks a highway.

Try to spot I-490. It's only 2+ miles, running west-east slightly south of downtown. It connects onto I-71 and I-90 on its western terminus, but ends abruptly at East 55th St at a grade crossing. Looking at the map, if I-490 continued eastward, it could easily connect University Circle and join I-271 to the north. That would be the ideal route for commuters coming from Beachwood, Gates Mills, or anything southeast of that, which covers notable communities like Orange, Solon, and Twinsburg. It's quite a portion of the Great Cleveland commuters. Alas, no such highway system exists. However, the idea of it has floated around before.

In fact, in the early 1960s, there was a proposal to construct the Clark Freeway that runs east-west through Shaker Heights and connect onto I-271. However immediately, criticism came in about the elimination of  homes and commercial properties that would result. In fact, this reaction was hardly confined to Cleveland. During the 60s and 70s, freeway and expressway revolts spread throughout the nation, as potentially affected neighborhoods voiced their concern of disruption. The unpopularity eventually resulted in the scrap of the proposed Clark Freeway. Instead, the Clark Freeway (I-490) merely stretched for 2+ miles it encompasses today.

Recently there has been rejuvenated efforts to stretch I-490 at its eastern terminus to University Circle, denoted the Opportunity Corridor. While there would be no eastern connection to I-271 and thus would not be the panacea for southeastern commuters, it would bring some relief to all. However, there first is the budget issue. With ODOT putting numerous projects on hold, the fiscal responsibility of the $200+ million project demands an answer. Furthermore, locals living in the path of the route would still be displaced. The area between University Circle and I-490 terminus is quite an economically lackluster region, and leaders hope that an infrastructure upgrade would also stir commerce in the region.

If Great Cleveland can learn something about infrastructure renovation, the recently completed Euclid Corridor has been quite a success. The $200 million development of rapid-bus route and transformation of Euclid Avenue, connecting Downtown Cleveland and East Cleveland while passing University Circle and Cleveland Clinic, has attributed to several billion dollars of new development. One can only hope that the Opportunity Corridor would one day bring similar results. University Circle would also be more easily accessible from highway, the airport, and commuters. Yet despite such, for the commuters coming from the southeastern direction, it doesn't look like they'll get their wish of connection from I-271. Those disgruntled can attribute the demise of a direct connection route to the nationwide phenomenon of freeway protests that rocked the country in the 1960s. That affected area is still heavily populated, and unlike the area affected by Opportunity Corridor that could benefit from revitalization,would likely still face great friction in pushing through any idea of freeway.

Sources:

• http://chuh.net/chhistory/features/freeways.html
• http://blog.cleveland.com/metro/2011/07/post_498.html
• http://blog.cleveland.com/metro/2011/05/post_465.html

Tempur-Pedic International Inc (NYSE: TPX)

It doesn't look like memory foams will not cushion this latest news for Tempur-Pedic (NYSE: TPX). Shares of the company tumbled over 48% on Wednesday, after it was announced that second-quarter profits will drop 50% from last year. Analysts had expected 86 cents per sharing of net income, but the expectation is now only 34 cents. Much of this is due to increased competition from mattress makers. An analyst from Gilford Securities is quoted in saying that Tempur-Pedic "needs to institute a strategy where they drive more brand loyalty, not only from consumers but also retailers."

The history of Tempur-Pedic goes back to the 1970s and NASA, which was "[developing] pressure-absorbing material to help cushion and support astronauts during lift-off." In 1992, the company was formed in Lexington, Kentucky, where it is headquartered now. From 2008 to 2011, profit had climbed over 270% from $58 million to $219 million. However, as CEO Mark Sarvary claims, the environment in North America has seen "unprecedented number of new competitive product introductions which have been supported by aggressive marketing and promotion." Notably competition has come from Sealy Corporation (NYSE: ZZ) and Select Comfort (NASDAQ: SCSS), which also saw losses of over 5% and 20% on Wednesday, respectively.

Sources:

• http://online.wsj.com/article/BT-CO-20120606-714417.html
• http://www.forbes.com/sites/abrambrown/2012/06/06/sleepless-nights-for-tempur-pedic-q2-profit-expected-to-fall-50/
• http://www.tempurpedic.com/our-company/our-history.asp
• http://www.google.com/finance?q=NYSE:TPX

Westport Innovations Inc (NASDAQ: WPRT, TSE: WPT)

On Tuesday, the stocks of Westport Innovations (NASDAQ: WPRT, TSE: WPT) climbed over 21% at NASDAQ to close at 27.02. Based in Vancouver, Westport trades not only on the NASDAQ but also on the Toronto-based TSE. According to its website, Westport "is a global leader in alternative fuel, low-emissions technologies that allow engines to operate on clean-burning fuels." Income statements show that Westport has had negative operating income in not only each of the past 5 quarters, but also annually for each of the past 3 years. Profit margin was Q1-2012 was -20% as the company lost $22.63 million USD.

The stocks climbed Tuesday after a deal was announced between Westport, which has market capitalization of slightly above $1 billion and Caterpillar (NYSE: CAT), which has market cap of over $54 billion. The would focus on "[developing] fuel systems for off-road vehicles," including natural gas engines in locomotives. The incentive to focus on natural gas comes from the substantial price difference between natural gas and diesel fuel, which results in "strong financial incentive to enable off-road applications to take advantage of low ... costs without sacrificing operational performance." Shares of Caterpillar climbed a meager 0.47% for the day.

Sources:

• http://www.bloomberg.com/news/2012-06-05/westport-rises-on-caterpillar-venture-vancouver-mover.html
• http://www.westport.com/is/
• http://www.google.com/finance?q=NASDAQ:WPRT
• http://www.google.com/finance?q=TSE:WPT
• http://www.google.com/finance?q=NYSE:CAT