Tuesday, January 29, 2013

Inverse Transform Demonstration with Excel VBA

Given F(x) = 1-e^(-λ*x) as the cumulative distribution function, the inverse transform gives -1/λ*ln(U) as the function that has F(x) as its cdf, where U is the uniform distribution from [0, 1]. Here, the following VBA codes allow users to visualize this transformation in Microsoft Excel.

Upon the execution of this procedure, the user inputs a value for lambda. Then 10,000 simulations are run, initially generating a random number from [0, 1] and then inputting that random number into -1/λ*ln(U), and outputting the result in column A. At the end of the execution, column C contains the different x values from 0 to the maximum, in increments of 0.001. Column D reflects the cdf by counting entries of A that are smaller than the corresponding x value. Finally, column E calculates the true value of 1-e^(-λ*x). The idea is that the outputs in columns D and E are similar.

Sub InverseTransform()
'Demonstrates inverse transform of the cdf F(x) = 1-e^(-lambda*x)
Columns("A:E").Clear
Range("B1").Value = 1# * InputBox("Enter a positive value for lambda: ", "Input", 2)

'10,000 simulation trials to be performed
'Transform of F(x) gives -1/lambda*ln(U), where U is uniform distribution [0,1]
For i = 1 To 10000
Cells(i, 1) = -1 / Range("B1").Value * Log(Rnd)
Next i

'Determine the maximum for the range of numbers to work with
Range("B2").FormulaR1C1 = "=MAX(C[-1])"

'To determine the cumulative probability density, use 0.001 gradient from 0 to the maximum value as the counter
i = 1
While i / 1000 <= Range("B2").Value
Cells(i, 3).Value = i / 1000
'In column D, count entries in column A that are smaller than the counter, then divide by the number of trials
Cells(i, 4).FormulaR1C1 = "=COUNTIF(C[-3],""<""&RC[-1])/10000"
'In column E, calculate the true value of 1-e^(-lambda*x)
Cells(i, 5).FormulaR1C1 = "=(1-EXP(0-R1C2*RC[-2]))"
i = i + 1
Wend
Range("B2").Clear
End Sub

After the execution of this procedure, the user can perform further analysis. Graphing columns C through E does reveal that values in columns D and E are similar, as the points almost completely overlap. Error calculations from those two columns illustrate a similar result that the inverse transform method takes a function F(x), exponential function in this case, to produce a function whose cdf is F(x).

Wednesday, January 23, 2013

Monte Carlo Simulation of Pi with MATLAB

Using Monte Carlo simulation, the value of π can be approximated as (1/n) * Σ(4*sqrt(1-U_i^2)), where n is a large number representing the number of simulation trials, and U_i represents the i-th trial result from a uniform distribution in [0,1], for 1 ≤ i ≤ n. The MATLAB codes to run this simulation is quite straightforward:

numTrials = 1000000000;    %number of simulation trials
count = 0;
for trial = 1:numTrials
    roll = rand;    %uniform distribution [0,1]
    count = count + 4*sqrt(1-roll^2);
end
sum(count)/numTrials

In this simulation, n = 1,000,000,000. The output of 3.1416 matches the value of pi in all 4 displayed decimal places.

Monday, January 21, 2013

Intricacies of VLOOKUP

In Microsoft Excel, VLOOKUP is a very useful function that "searches for a value in the first column of a table array and returns a value in the same row from another column in the table array." The basic syntax of the function is =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup), with the range_lookup being optional.

If range_lookup is FALSE, the function will look for only exact matches. In this case, the values in the first column of table_array do not need to be sorted. However, this is not the case if range_lookup is TRUE, which is the selection by default if omitted. Then the values in the first column must be in ascending order, as the function will use the largest value smaller than the lookup_value, if an exact match is not found.

Finally, two wildcard characters allow flexibility in the lookup_value when it is a text and the range_lookup is FALSE. A question mark (?) matches any single character, while asterisk (*) matches any sequence of characters. Use a tilde (~) before either the question mark or asterisk if they are the actual characters in the lookup_value. So as an example, the text value "abc" can be matched by both "a??" and "a*".

Source:

Tuesday, January 15, 2013

Seattle at a Glance

The largest city of the Pacific Northwest region, Seattle is home to over 600,000 people in the city proper and with 3.7 million residents, the Seattle-Tacoma-Bellevue Metropolitan Statical Area (Seattle MSA) is the 15th largest in the nation. The population grew by 8% between the 2000 and 2010 Census. A notable characteristic of the Seattle demographics is the level of education. For people over the age of 25, 56% of the residents have Bachelor's Degree or higher, which ranks Seattle among the top of major cities in the United States. University of Washington, the largest employer in the city proper, has over 40,000 students.

Seattle MSA accounts for 1.93 million jobs and generates an estimated gross metropolitan product of $218 billion. Aerospace, largely due to The Boeing Company, accounts over 80,000 jobs and $32 billion in revenue. Information technology is another important sector to the economy of Seattle MSA, employing over 100,000 and with companies such as Microsoft and Amazon.com headquartered in the region. Other notable companies headquartered in Seattle MSA include Costco, Starbucks, and Nordstrom.

International commerce is crucial to Seattle, which is situated roughly equidistant from Tokyo and London. Washington State ranks first in exports per capita, with Mainland China, Canada, and Japan as the top trading partners. Seattle-Tacoma International Airport is the closest airport on the continental US to Asia and served 31.2 million passengers in 2009. Internally, King County Metro and Sound Transit combine to offer express bus, light rail, and commuter rail services for the region.

In terms of the cost of living, Seattle trails major Northeastern and California cities from Boston to Los Angeles, and is comparable to that of Chicago. In 2010, 2 bedroom /1 bath apartment rental on average cost slightly over $1,100. Average high temperature in July is around 75F, while average low in winter hovers around the freezing point. At 36.2 inches, the average annual precipitation is actually lower than that of New York; summer is the dry season, while light precipitation and partly cloudy skies dominate the winter months.

Sources:

Monday, January 14, 2013

Currency Movements and Global Macro Calls

2013 Year Ahead Report published by Bank of America Merrill Lynch Global Research features 10 macro calls on the world economy. One call regarding interest rates and currencies states that "the U.S. dollar and euro could rally on the global recovery and greater fiscal clarity, pushing the yen lower and emerging market currencies higher." Another recall, regarding the crisis in Europe, states that "the big tail risk of a eurozone breakup has likely passed."

With this theme in mind, it was announced on Monday that Japan would further devalue its currency. It has already fallen 14% since October, and this has been helping the country's exports. Prime Minister Abe has "[stepped] up the pressure for the Bank of Japan to ease monetary policy" and increase the inflation target to 2%. The yen currently trades at 88.95 on the US dollar, which is near its 52-week high. While the yen has been pushed lower, the euro has hit an 11-month high against the US dollar, at $1.3382. Yields on Italian and Spanish 10-year bonds have stabilized to 4.19% and 5.03%, respectively. These numbers do go along the macro call from report that "European economy should stabilize as the year progresses."

Sources:

Friday, January 11, 2013

Principle World Cities by Time Zones

Here are the principle cities by time zones. This is not a comprehensive list of the time zones, as it excludes certain zones in the Pacific and Atlantic that do not have major cities. Most of North America and Europe utilize daylight saving time, as well as some of the major cities in South America and Australia. During the summer months, daylight saving time adds one hour forward.
  • UTC -1000: Honolulu
  • UTC -0900: Anchorage
  • UTC -0800: Los Angeles, Vancouver, Tijuana
  • UTC -0700: Denver, Calgary
  • UTC -0600: Chicago, Mexico City, Winnipeg
  • UTC -0500: New York, Toronto, Lima
  • UTC -0430: Caracas
  • UTC -0400: Santiago, San Juan, Halifax
  • UTC -0330: St. John's
  • UTC -0300: Sao Paulo, Buenos Aires
  • UTC ±0000: London, Lisbon, Casablanca, Accra
  • UTC +0100: Paris, Rome, Berlin, Madrid, Lagos, Zurich, Stockholm
  • UTC +0200: Cairo, Johannesburg, Athens, Istanbul, Helsinki, Jerusalem
  • UTC +0300: Nairobi, Baghdad, Riyadh
  • UTC +0330: Tehran
  • UTC +0400: Moscow, Dubai
  • UTC +0430: Kabul
  • UTC +0500: Karachi
  • UTC +0530: Delhi, Colombo
  • UTC +0545: Kathmandu
  • UTC +0600: Almaty, Dhaka
  • UTC +0630: Yangon
  • UTC +0700: Jakarta, Bangkok
  • UTC +0800: Beijing, Hong Kong, Perth, Singapore
  • UTC +0900: Tokyo, Seoul
  • UTC +0930: Adelaide
  • UTC +1000: Sydney
  • UTC +1100: Vladivostok
  • UTC +1200: Auckland, Suva