Excel Function: Looking for Alphas

Alpha measures the excess of returns in investments against some benchmark. For Example, it means if an investor is expected to earn X% in Apple, she probably would get 2% extra. This abnormal returns are usually measured using a similar CAPM approach. One way to do it is running a linear regression between the asset return and the market return (SP&500) both adjusted by a fixed return (Treasury Bill). I used 60 months (5 years) of historical data to do the estimation. I calculated the Alphas for the first 20 companies with the biggest market capitalization. In my calculations only Philip Morris International (consumer staples industry) showed an Alpha statistically significant at the 5% level. Its Alpha return was around 1.2% (14% annually). In the same direction, Apple showed the highest Alpha in the sample of around 2% (24% annually), but the alpha coefficient was not statistically significant at the 5% level.

Technical details: I used the Stock Market Functions add-in to get automatically the data for the end of the month (link). The Excel functions that I used were:

• Linest(Range Y,Range X,TRUE, TRUE): Get the main statistics of a regression in array format
• Index(Array, Row, Column): Extract from an array format (Linest) specific indicators. In particular, I used to get the std error from Beta and Alpha
• Slope(Range Y,Range X): Get directly the slope coefficient from a regression
• Intercept(Range Y,Range X): Get directly the intercept from a regression

The excel file with the calculations is here. If you found this helpful, please share.

Alpha Calculations 

(Monthly returns)

Excel Data Table: Optimal Moving Average

Do you believe that the technical analysis in the stock market works? If your answer is yes, you can try to use excel to help you make an informed decision and choose the optimal parameters. I chose the moving average (MA) as an example of technical analysis, the MA and its different combinations have been used for a long time in the stock market to support investments. Now with Excel you can download time series of any stock automatically and start building your own analysis. I calculated the optimal MA for all stocks in the SP&500 using data tables. Excel is very flexible when you want to implement new methods and ideas. Enjoy!  

Optimal Moving Average

Technical details: Install the Stock Market Functions add-in (link) and use data tables. The link to my excel file is here.If you found this helpful, please share.

Excel: GDP Portfolio Optimization

Where would you want to invest your money if the GDP were your only indicator to make your decision? I built a portfolio model using GDP of countries and the Markowitz' Modern Portfolio theory. The results that I got were expected. For a portfolio return of 4% in the efficient frontier, India and China have to have a basket participation of 24% together. It is not a big surprise. Some developed countries also show important participation. If the investor wants higher returns (7%) and higher risk, the portfolio participation of China and India have to increase to 62%.  

Technical details: I applied what I learnt in Spreadsheet Modelling for Finance. For the GDP information, I got the information from the WEO database (link). I calculated the efficient frontier using solver tables (link) and for the computation of big matrices I installed this add-in (link). The link to get my excel file of the calculation is below. Enjoy!

Portfolio Return of 4%

Excel Optimization Portfolio

Excel VBA: Google Map Analytics

Determine the optimal location is an important factor for the success of a business. Imagine that you have a list of potential customers or suppliers, and you want to determine what is the best place to built a warehouse or an store. Well, now it is possible using excel. First, you need a list of addresses, you have to download a VBA program, and run excel solver. Jamie Bull created a VBA program (the link is below) that allow us to get directly in excel the same information that we can get from Google maps inserting the addresses. Some of the list of commands are here:  

• Distance in Miles (By Car):   =G_DISTANCE(Address 1, Address 2)
• Latitude: =L_LAT(Address)
• Longitude: =G_LONG(Address)
• Time in hours (By Car): =G_DURATION(Address 1, Address 2)
• Address: =G_ADDRESS(Latitude&Longitude)

Having the latitude and longitude, we have to determine what are going to be the main factors at the time to choose a place (volume, distance, time, etc). Here I attached one excel example using distance as a main factor. To get the optimal point I used solver. A detail explanation of how to setup excel can be found in Wayne Winston's book (link here). For the map of the optimal point I used Google Fusion Tables.  

If you found this helpful, please share.

Source of the VBA code
Excel Function Maps