Friday, May 26, 2006

Protecting Cells in Excel

Microsoft Excel versions 2002 and prior had one simple cell protection process. Excel 2003 has quite a different process and I will cover that later.
In the earlier versions each cell has the ability to turn protection on or off. By default the protection is turned on. However it will not take affect until you lock the worksheet. Once you lock a worksheet only those cells that have been unlocked will be available for editing.
You can do this individually or for a selection of cells as follows:
Select the cell or cells that you want to unprotect and then choose Format | Cells from the menu and select the Protection Tab. Uncheck the Locked check box to unprotect the selected cells.

Now you need to protect the sheet. Do this by choosing Tools | Protection | Protect Sheet from the menu. Add a password (if required) and Click OK.

Now you should be able to test the locked and unlocked cells. Attempting to edit locked cells should return a message "The cell or chart you are trying to change is protected and therefore read-only. To modify a protected cell or chart, first remove protection using the Unprotect Sheet command (Tools menu, Protection submenu). You may be prompted for a password.
Unprotected cells should be available for editing.


Posted by: Jethro | 10:43 AM |

Thursday, May 04, 2006

Gregorian Dates in Excel

All dates and time is stored in Excel as an integer and declimal places respectively. The dates start with 1 as the first day of 1900 or 1904 depending on which you choose.
Time is represented as a decimal represenation of the time on a 24 hour clock. Eg Midday is represented by 0.5, 6AM as 0.25 and 6PM as 0.75 etc.

The date thing can be all rather confusing thanlkts to Julis Cesear and Pope Gregory XIII. Don't believe me- thean read on.

Doug Klippert from Unoffical Office Stuff explains:

The year -45 has been called the "year of confusion," because in that year Julius Caesar inserted 90 days to bring the months of the Roman calendar back to their traditional place with respect to the seasons. This was Caesar's first step in replacing a calendar that had gone badly awry. Caesar created a solar calendar with twelve months of fixed lengths and a provision for an intercalary day to be added every fourth year. As a result, the average length of the Julian calendar year was 365.25 days.

The Gregorian (Pope Gregory XIII) calendar is based on a cycle of 400 years, which comprises 146,097 days. Since 146,097 is evenly divisible by 7. Dividing 146,097 by 400 yields an average length of 365.2425 days per calendar year, which is a close approximation to the length of the tropical year. The Gregorian calendar accumulates an error of one day in about 2500 years.


Theres more there than that of course, ad a cursory Google search on "Excel time 1904 Gregorian" will return lots of articles on the subject.

So if you are confused by dates, than don't be. Just remember that there is less than 366 days to your next birthday and you are looking good!


Posted by: Jethro | 12:24 AM |

Monday, May 01, 2006

MMULT Formulas in Excel

I vaguely remember solving simultaneous equations back in high school.
Boring Algebra maybe, but Excel can handle it.

I recently came across a spreadsheet that had a 29 by 29 array that need to be solved as a simultaneous equation.

I did some googling and found a bunch of smarter people than I had written excellent methodologies and examples on how to use Excel in this instance.

Kardi Teknomo's Page
Professor Stanley David Gedzelman - City College of New York
dslimited
Duncan Williamson


Bob from xldynamic.com was also of great assistance writing this fantastic explanation for me. Unfortunately I cannot include the spreadsheet for confidentiality reasons.

This looks like formulae for calculating the volatility of a portfolio on N risky assets. To calculate this, there is a standard formula, SQRT(WT.V.W), which is a matrix formula for calculating such volatility. This can be converted to an Excel formula using matrix multiplication, MMULT, and TRANSPOSE.

In this formula, W is the relative weights of each asset in the portfolio (to the overall portfolio value). WT is the transpose of the weights, and V is the variance of excess returns for each asset.

The sheet WT in the attached spreadsheet shows the matrix result of the TRANSPOSE function (=TRANSPOSE(E7:E35), which creates a 1x29 array.

The sheet V shows the calculation of the variance (=TRANSPOSE(sdsrp2)*corrrp2*sdsrp2), which creates a 29x29 array. (note sdsrp2 and corrrp2 are named ranges in the example spreadsheet)

WT.V shows the result of the inner MMULT (=MMULT(TRANSPOSE(E7:E35),TRANSPOSE(sdsrp2)*corrrp2*sdsrp2)). Because WT is a 1x29 array, and V is a 29x29 array, WT.V results in a 1x29 array (see MMULT help for an explanation as to why).

WT.V.T shows the final calculations (=MMULT(MMULT(TRANSPOSE(E7:E35),TRANSPOSE(sdsrp2)*corrrp2*sdsrp2),E7:E35)). Because WT.V is a 1x29 array and W is a 29x1 array, we get a 1x1 array.


Posted by: Jethro | 2:07 PM |