Monday, July 30, 2012

10PM, Tower City Terminal

On a Monday night at 10 PM, the RTA Green Line departs from Tower City Terminal, sending many fans, who had just witnessed the Cleveland Indians avoid a sweep in the hands of Baltimore Orioles, back home away from downtown. It is packed. It is loud. It is lively. Passengers who just barely made it to the train cannot find a seating spot. But at least, standing was better than sitting in a car jam trying to get onto the highway, right? The train goes on, and the speedometer on the iPhone measures it at over 45 mph during the peak. Barely anyone gets out of the train in the first few steps, during the inner-city portion of the track. At Shaker Square, the first station outside of the Cleveland city proper, a noticeable number of passengers leave. Then another bunch gets off at Warrensville Station, which houses a sizeable parking lot. Finally, just under 30 minutes since departure, the remaining people get off at the Green Station, the terminus.

Despite being named the best public transportation system in North America in 2007, RTA still remains far removed from many Greater Clevelanders who don't live within the city proper or inner-ring suburbs. A reaction I received recently, upon suggesting driving from the a western suburb and park-and-riding at one of the Red Line stations, was a candid "Cleveland has trains?" It was hard to blame that reaction. Unless ones goes along Shaker or Van Aken Blvd occasionally, or is truly interested in this field, it is plausible for suburbians here to not even know the extent of the rail system. Does anyone here know that Cleveland was the first North American city to have a direct rail line between the international airport and downtown? Speaking from experience, the journey on the Red Line from Case Western to Hopkins Airport is beyond comparison to heading to LaGuardia or JFK from Columbia University. But as population density usually dictates the usage of public transportation system, Cleveland is not New York. Rail lines are more limited, the trains less frequent, stations less crowded, seemingly reinforcing what introductory economics book would say, that public transportation is an inferior good; that is, the demand for it increases as wage decreases.

But the scene aboard that Green Line Monday night told of a different story. These people, myself included, just didn't want to deal with hassle, not to mention cost, of parking and construction. It may have meant a faster journey overall, but the 30-minute ride was reasonable, coupled with the free park-and-ride system. Every year, there would be a few events, St. Patrick's Day most notably, that cause massive inflow of people downtown, leading to overflow of passengers on the RTA, that ultimately make the newspaper headlines. But this wasn't one of those nights. It was just another Indians game, and a pretty mediocre one too, in terms of attendance: 18,264. While the Indians haven't fared well in overall attendance, it truly is capable of drawing huge crowds: just three days later, over 34,000 fans attended the Thursday game against Detroit Tigers. This was also Monday night, not the most lively day-of-the-week when it came to downtown entertainment that drew crowds, such as the casino. Furthermore, this game on Monday night was before ODOT closed the Ontario Street ramp to westbound Interstate 90 for a year, causing potential havoc for drivers coming from Indians games. All these are to say that the scene on Monday night is by far from the busiest downtown Cleveland can see.

The passengers on the Green Line that night, most of whom got off at the park-and-ride stations in Shaker Heights, illustrated that the traffic was predominantly comprised of those who had driven from their home to the stations. Few took the train to get home directly. This is crucial in understanding the usage and service of the rail lines. They do indeed appeal to, even if still to a small portion of, the suburban residents. RTA recently initiated program to provide ticket discounts to families going to the games, trying to address the issue that carpooling will reduce the costs of driving. It may take more awareness, or continued frustration of parking downtown, to convince more suburbanites to ride the train. But until then, those who do enjoy the system got a glimpse, on what but an average Monday night, of what it has to offer. This may not be New York, and people aren't out at Public Square at midnight like they are in Times Square. But nevertheless, there is indeed an urban livelihood to be experienced.

Crowded Green Line train departing Tower City immediately following Indians game on 7/23/12
Sources:

Sunday, July 29, 2012

NBC's Disgraceful Decision to Skip Opening Ceremony Tribute to Terror Victims

Having exclusive rights to broadcast Olympic coverage in the United States is a double-edged sword for NBC. It draws massive viewership, but its decisions are heavily scrutinized, particularly due to the time difference between London and United States, and hence NBC's ability to control what is broadcast to the American audience. Many objected to NBC's decision not to live stream the Opening Ceremony, which happened in the early afternoon hours in the United States. Instead, NBC tape-delayed the show for prime time on both coasts, explaining that the Opening and Closing Ceremonies are "entertainment spectacles best shown at a time when friends and family are able to gather together to watch." Criticized or not, the decision paid off for NBC. The Opening Ceremony Friday drew "the best overnight ratings ever for an Olympics held outside the United States," a 7% increase in ratings from the Beijing Opening Ceremony.

The debates will continue, but one of its decisions Friday night deserves heavy criticism. When a tribute was played during the ceremony for terror victims, NBC instead decided to showcase an interview with Michael Phelps. This was purely a monetary decision aimed to boost its ratings, as the cost of US TV rights fees topped $1.18 billion. Upon being questioned for this particular decision, NBC spokesman Greg Hughes admitted that its "programming is tailored for the US audience." However, the crucial issue is that the tribute was not irrelevant to United States. In fact, the tribute was more relevant to United States than any other countries of the world. Great Britain has been United States' staunchest ally in the War of Terror. In Afghanistan alone, over 400 British troops have sacrificed their lives, more than the toll Britain suffered from its recent Falklands War. The tribute on Friday was widely interpreted as a memorial for the 52 victims of terrorist bombings in 2005, which occurred on July 7, the very next day after London was awarded these 2012 Olympic Games. The 2005 bombings were believed to be al-Qaeda retribution for British involvement in the conflicts in Afghanistan and Iraq.

While it is understandable that NBC has financial incentives to boost its audience with its selection of content exclusively broadcasted to Americans, this particular decision was ignominious. July 7 bombings were the 9/11 attacks for Britain, and occurred while the country was still in jubilation knowing it would host the 2012 Olympics. For any country to purposefully avoid such dedicated tribute at that moment for the marginal financial gain is classless, but in particular for NBC on behalf of the United States to do so was even more disgraceful.

Sources:

Friday, July 27, 2012

2012 Olympics Athlete Rate Per Country Population

As athletes match into Olympics Stadium in London for the Opening Ceremony, here's a look into the number of athletes by country or territory. Specifically, which countries have the most and least number of athletes, per population of the country? A report by Guardian has compiled the raw data of the athletes for analysis.

The top countries of territories on the list of athletes per population are dominated by very small countries that send a few athletes. Atop the list of Cook Islands, an associated state of New Zealand located in the South Pacific. With 8 athletes representing its population of 10,777, according to CIA World Factbook as of July 2011, Cook Islands have one athlete representing around 1,350 people. At the bottom of the list is Bangladesh. With 5 athletes representing the country of more than 161 million people, that is a rate of over 32 million people per athlete. The factor of difference between the rate of Cook Island and that of Bangladesh is nearly 24,000.

Of the countries or territories on top of the per capita list, if a filter of 100,000+ population is enacted, Iceland tops the list. With 28 athletes for a population of over 300,000, the rate is over 11,000 people per athlete. When a filter of 1,000,000+ population is enacted, New Zealand tops the list. In comparison, the United States, with 534 athletes, has over 580,000 people per athlete.

Sources:

Thursday, July 26, 2012

Space and Tab: How to Process Data into Excel

See previous related post: Processing List of Numbers into Excel

For anyone who has done analysis in Microsoft Excel, it should obvious that being able to successfully process raw data, whether from websites or text files, into the spreadsheet is the first crucial step. Each discrete entry should be in its own cell. Unfortunately, copying and pasting will not always work. Often times upon pasting, everything goes into the one currently highlighted cell. Often times, spacing and tabbing issue cause improper processing of data.

We'll work with these samples of data: historical S&P 500 index prices from Google Finance, and stats on Zack Greinke, a Milwaukee Brewers pitcher, from ESPN. In both cases, a mere selecting, copying, and pasting the data into an Excel sheet will result in inscrutable mess of everything in the one cell. Instead, select and copy the original table from the websites. Open Notepad, and paste the values. Being simplistic in nature, Notepad is able to rescind the formatting of the text pasted into it. Then, highlight these pasted items on the Notepad, and copy. Although the the values of these items are the same as the original, the different formatting will make all the difference. Now go to the Excel cell, make sure the formula bar is not selected (make sure the cursor is not flashing anywhere). Paste, and the values will be nicely displayed in the table.

Why does this work? There are no visual ways to distinguishing tabbing and multiple spacings, but use the left and right arrows to traverse through the values in Notepad, and if the cursor jumps over the blank spaces, the empty space is due to tabbing. If the cursor only moves one position per arrow click, the spacing is just a space. Either way, Excel recognizes tab as sign to break the data into separate columns. Spaces will not trigger splitting the data separate cells. Instead, the text, which includes the spaces, will be pasted into the one cell. Lastly, line break trigger breaking the data into separate rows.

Sometimes, text-to-columns processing may be needed (see the previous related post). But often while retrieving data from websites, this method of copy from website, paste onto Notepad, copy from Notepad, paste onto Excel allows for the processing of data necessary to perform further analysis.

Sources:

Wednesday, July 25, 2012

Correlations Among Facebook and Zynga

See previous related post: Facebook: Two Months Since IPO

After the market closed Wednesday, Zynga (NASDAQ: ZNGA) delivered a disappointing Q2 earnings report and outlook. Revenue was $332.5 million, short of the $343.1 million predicted by analysts. Profit was 1 cent a share, short of the 6-cent average estimate. Furthermore, Zynga cites that due to "delays in launching new games, a faster decline in existing web games due in part to a more challenging environment on the Facebook web platform, and reduced expectations for Draw Something," 2012 bookings will be around $1.15 billion to $1.225 billion, short of the projection from April of $1.43 billion to $1.5 billion. In the after hours, shares of Zynga have fallen by over 38%, but it wasn't the only one negatively affected by this news. Facebook saw its shares dip over 7% in the after hours.

The link between Zynga and Facebook (NASDAQ: FB)  is evident in the latter's mention in Zynga's report today. Just how correlated have the stock movements of the two companies been? How about their correlations to LinkedIn (NYSE: LNKD)? Historical prices, since Facebook's IPO in mid-May, were compiled from Google Finance, and the daily changes were documented:

Date  Zynga Facebook LinkedIn Boeing
25-Jul 3.25% 3.13% -0.78% 2.78%
24-Jul -3.34% -1.04% -1.16% -1.21%
23-Jul 6.04% -0.03% -1.94% -1.33%
20-Jul 5.26% -0.83% -2.20% -1.30%
19-Jul -1.08% -0.38% 3.86% 1.31%
18-Jul 0.66% 3.63% 0.74% 1.07%
17-Jul -5.18% -0.53% 0.61% 0.19%
16-Jul -1.43% -8.07% -2.25% -0.73%
13-Jul -2.39% -0.29% 0.91% 2.51%
12-Jul 1.41% -0.52% 3.52% 0.27%
11-Jul -1.20% -1.59% 1.56% -2.32%
10-Jul -4.39% -2.18% -3.35% -1.09%
9-Jul -2.24% 1.39% -5.40% 0.46%
6-Jul -1.47% 0.83% 0.57% -1.01%
5-Jul 0.74% 0.87% -0.28% 0.23%
3-Jul -2.88% 1.40% 0.85% 1.49%
2-Jul 2.21% -1.06% 1.28% -1.51%
29-Jun 1.12% -0.83% 3.44% 3.80%
28-Jun -4.44% -2.70% -2.31% -0.40%
27-Jun -2.26% -2.63% -1.17% 1.33%
26-Jun -4.95% 3.24% 3.27% -0.17%
25-Jun 1.00% -3.00% -3.56% -1.26%
22-Jun 4.90% 3.80% 4.56% 0.83%
21-Jun -2.89% 0.76% -2.93% -2.25%
20-Jun -1.34% -0.97% -0.07% 0.12%
19-Jun 3.29% 1.59% 0.17% 1.42%
18-Jun 3.96% 4.67% 3.09% -0.13%
15-Jun 10.76% 6.08% 3.78% 0.19%
14-Jun -0.40% 3.74% 2.89% -0.29%
13-Jun 1.20% -0.47% 1.13% -0.72%
12-Jun -10.27% 1.48% 0.18% 3.52%
11-Jun -8.26% -0.37% -2.05% 0.24%
8-Jun 0.33% 3.00% 2.26% -0.01%
7-Jun -2.11% -1.86% 1.13% 1.35%
6-Jun 7.50% 3.63% 0.09% 2.13%
5-Jun 0.35% -3.83% 2.10% 0.12%
4-Jun -4.99% -2.96% -0.46% 0.39%
1-Jun -3.99% -6.35% -4.78% -3.40%
31-May 6.64% 5.00% -2.07% 0.32%
30-May -3.61% -2.25% -1.81% -1.43%
29-May -7.87% -9.62% 1.44% 0.57%
25-May -2.79% -3.39% -0.28% -1.95%
24-May -3.82% 3.22% -4.60% -0.25%
23-May 3.97% 3.23% 2.20% 0.13%
22-May -4.09% -8.90% 4.64% -0.42%
21-May -0.98% -10.99% -2.20% 3.80%
18-May -13.42% 0.61% -5.65% -0.83%

As a control group, Boeing (NYSE: BA) was also selected to represent a company from the vastly different sector of industrial. The correlations among these vectors were calculated using the =CORREL() Excel function, as the results are as follows:
  • ZNGA / FB: 0.41262114
  • ZNGA / LNKD: 0.327412705
  • FB / LNKD: 0.187973772
  • ZNGA / BA: 0.067813629
  • FB / BA: 0.112054524
  • LNKD / BA: 0.285873686
Among these results, Facebook and Zynga did have the highest correlation, greater than either one's to LinkedIn. At the same time, Boeing had the highest correlation to LinkedIn, compared to either Zynga or Facebook. Given the high correlation between Zynga and Facebook, the latter of which is due to deliver its first earnings report as a public company on Thursday, it looks like Zynga, right after its disappointing report, has a good immediate chance to rebound back up or dip further.

Sources:

Tuesday, July 17, 2012

Facebook: Two Months Since IPO

See previous related post: Facebook Ready for IPO

To truly visualize the disappointment of Facebook (NASDAQ: FB) two months after its initial public offering, compare it with nothing else but LinkedIn (NYSE: LNKD) during the same period. From the IPO price of $38 launched on May 18th, Facebook has closed as low as 25.87. It made modest gains in June, climbing back as high as $33.10 on June 26th, before falling back down to close Tuesday at $28.09. This comes over the news that users in both the United States and Europe had fallen over the past six months. The research by Capstone, reported that US users declined by 1.1%, while 14 of 23 countries in which Facebook had over 50% penetration experienced "fewer users or saw little change." This report comes days after Warren Buffett, who's known to hold onto stocks for the long-term value, stated that "investors frustrated with the stock’s decline since its public offering are paying the price for betting on a short-term rally." Given the closing price of $28.09, that is a 26.08% drop during the period.

LinkedIn, the professional network, in the meantime saw its shares fall from the May 17th closing price of $104.95 to the present $103.84. That is only an 1.06% drop. What may be surprising is that the financial statements of Facebook look much more healthy. While people may claim is overvalued at its P/E ratio approaching 90, LinkedIn still has its ratio soaring above 600. Facebook's Q1 profit margin also trumped that of LinkedIn, 19.38% to 2.65%. But it's the growth that's raising the concern. Facebook's profit margin is actually down from 2011, when it was 26.95%, while LinkedIn has seen it soar from 2.28%. It's not just Wall Street that is punishing Facebook. In the latest American Customer Satisfaction Index E-Business Report, Facebook sank to a record low, falling behind Google+ and LinkedIn. Among the complaints of Facebook included "an excess of ads and privacy concerns."

Tumbling with Facebook over the past two months is Zynga (NASDAQ: ZNGA). From the $8.27 closing price of May 17th, it is currently at $4.58, representing a whopping 44.62% decline. Zynga is heavily reliant on Facebook to launch its social game services, and the Q1 profit margin was a dismal -26.59%.

Sources:

Sunday, July 15, 2012

Location of MLB Stadiums with Respect to City Centers

Sports stadiums are usually located at the center of metropolitan areas, but that is not always the case. Some are outright not even located in the city proper of metropolitan area's largest city. Here we examine the 30 Major League Baseball stadiums and their location with respect to the city center. While there may be difficulty in pinpointing one single city center, we will use the specific location given by Google Maps upon typing merely the city name. That yields good approximations of the core of the cities' downtown area. The distance from the baseball stadium to that area is calculated for each of the 30 stadiums.

There are a few cases that necessitate some remarks, in which the city that the ballpark is located in isn't the largest in that city's metropolitan area. Texas Rangers is based in Arlington, the third largest city in the Dallas-Ft. Worth area. Home of the Rays, St. Petersburg, is smaller than the city of Tampa. Anaheim is also part of the Los Angeles area. Nevertheless, because all of these cities are of fairly large size (all around 200K - 300K in population), it's fair to consider the distance from their center to the baseball for this exercise. Finally, it's worthy to note that the center location for New York was given as Broadway & Chamber St. in Lower Manhattan. While this location was indeed used for this exercise, it's important to note that there are tenable arguments for pushing Midtown Manhattan as the "center" of the city.

Here are the ranking in distance from the baseball stadium to the chosen city center:

Team City BallPark Mile
Mets New York Citi Field 10.1
Yankees New York Yankee Stadium 9.3
Royals Kansas City Kauffman Stadium 7.6
Athletics Oakland The Coliseum 5.8
Cubs Chicago Wrigley Field 5.1
Brewers Milwaukee Miller Park 4.2
White Sox Chicago US Cellular Field 3.8
Phillies Philadelphia Citizens Bank Park 3.6
Angels Anaheim Angel Stadium 3.5
Nationals Washington, DC Nationals Park 2.6
Rangers Arlington Rangers Ballpark 2.3
Red Sox Boston Fenway Park 2.2
Dodgers Los Angeles Dodgers Stadium 2.1
Giants San Francisco AT&T Park 2.0
Rockies Denver Coors Field 1.4
Blue Jays Toronto Rogers Centre 1.2
Mariners Seattle Safeco Stadium 1.1
Braves Atlanta Turner Field 1.0
Astros Houston Minute Maid Park 1.0
Marlins Miami Marlins Park 1.0
Rays St. Petersburg Tropicana Field 1.0
Orioles Baltimore Oriole Park 0.9
Twins Minneapolis Target Field 0.9
Pirates Pittsburgh PNC Park 0.8
Indians Cleveland Progressive Field 0.7
Padres San Diego PETCO Park 0.7
Tigers Detriot Comerica Park 0.6
Diamondbacks Phoenix Chase Field 0.4
Cardinals St. Louis Busch Stadium 0.4
Reds Cincinnati Great American Ballpark 0.3

Over half of the stadiums are located within 2 miles of the city center. At a first glance, it seems as though big cities have stadiums further out of the city center. This certainly is true for New York and Chicago. Indeed, Yankees and Mets have stadiums in The Bronx and Queens, respectively, away from the central business locations of Midtown or Downtown. White Sox and Cubs are known for being the team of "South Side" and "North Side," respectively. It may seem intuitive that for cities like New York and Chicago, with already heavily crowded downtown centers and limited parking spots, it may be more reasonable to have the ballpark crowds away from these centers. For smaller cities like Cleveland, sport events are great sources to attract people to be around downtown.

This is merely a hypothesis. In any event, even under such assumption, cities like Kansas City and Milwaukee are high on the rankings, seeming to contradict the trend. Bigger cities like Houston and San Diego are near the bottom of the rankings. It's important to note that other factors play into the location of the stadiums. While this exercise may give some insight, it's far from the comprehensive picture.

Source:

Saturday, July 14, 2012

Distribution of S&P 500 Constituent Market Cap

The S&P 500 index, according to Standard and Poor's website, "remains a leading indicator of U.S. equities, reflecting the risk and return characteristics of the broader large cap universe on an on-going basis." Presently, companies with market cap over $4 billion are considered. But given the 500 companies, what exactly is the distribution of their market cap like?

The short response is enormously positive skew. The data used for this analysis was as of June 28, 2012, factoring in the recent inclusion of Monster Beverage Corp. (NASDAQ: MNST) over Sara Lee Corp. (NYSE: SLE). As a note of interest, after the trading day of June 29th, Progress Energy (NYSE: PGN) was removed from the index, while Seagate Technology (NASDAQ: STX) was added, but this transition had not been factored into the data for this analysis.

The 500 companies totaled over $12.89 trillion in market capitalization. However, the top 10 companies comprised over 20% of that figure, led by Apple, Exxon Mobil, and Microsoft at $554B, $398B, and $255B, respectively. The average market cap of the 500 companies was over $25.7 billion, while the median figure was only around $12 billion, giving a skew value of over 5.48. In fact, the average value of $25.7 billion fits into the 77.7% percentile. Further remarkable is that even while taking the logarithm of all of the market cap values, the skew is still positive at 0.668.

Here is the summary table of these 500 market cap values, along with the box-and-whisker plot of the logarithm of the values:

Max: $544,206,062,500.00
95% P-tile: $91,226,411,328.12
75% P-tile: $23,949,059,570.31
Median: $11,998,596,191.41
25% P-tile: $6,461,437,377.93
5% P-tile: $2,973,694,091.80
Min: $1,301,555,053.71
Sum: $12,892,289,301,269.50
Average: $25,784,578,602.54
P-tile of Avg: 77.70%
Stdev: $46,745,814,552.27
Skew: 5.484828298
Skew of Log: 0.668860873
Top 10: 20.93%

The market values were obtained through Bloomberg on June 29th.