Instructions for Weighted Statistics Worksheet

General Instructions

This worksheet has been designed and tested using Netscape 3.0. It should work with later versions of Netscape's browsers but will not work with other browsers. It uses only JavaScript for computations and should cause no problems when used with the appropriate browsers. The operative word here is "should" -- the author cannot guarantee fault-free operation.

You should be able to use the worksheet when not connected to the internet. Save the program file (ws_***.htm) and the accompanying instruction file (wi_***.htm) on your disk using the browser's command to File Save. At a later time you may retrieve the file using the browser's File Open file command; you may then use the page as you would if you were on the network.

When using the worksheet, you may change any inputs. To do so, click inside the appropriate box, then make your changes. When finished, click any area outside the boxes on the form.

You may copy inputs from other sources such as spreadsheets, word processing documents, and other worksheets in this series, then paste the results into the appropriate boxes on this form. To copy an area from an Excel spreadsheet, select it, then select Edit Copy. In the browser, select a position in the input box, then select Edit Paste. To copy an area from a box in the browser to an Excel spreadsheet, select the text in the browser and select Edit Copy. In the Excel spreadsheet, select a position, then select Edit Paste. This brings each row into the spreadsheet as text. To convert to a matrix, select the column in which the information is located (the left-most one shown), then select Data Text-to-columns. Choose Delimited and Spaces as delimiters and the information will appear in the requisite number of cells.

When you save a page on your own disk (using the browser's File Save As command), only the original material in the form will be saved. To overcome this, you may click the MAKE RECORD button. This will create a new page with the relevant inputs and outputs from your most recent case. You may print this or save it on your own disk. At a later time you may open this file in your browser and copy information from it to a regular worksheet, if you wish. When you are through with the record page, choose either the File Close command or click the X in the upper right corner (or equivalent on your platform) to return to the worksheet.

There are two other ways to save and retrieve worksheet information. You can copy the information you wish to save to some other document, such as a spreadsheet, word processing document or text file. You can also load the source (ws_***.htm) file in a word processor and edit it to include your inputs. You will find the default information in blocks marked TEXTAREA and in the VALUE attributes of INPUT tags. Simply replace the default values with your information, then save the page as a file on your disk under any desired name.

Whenever you change an input, the output area will be cleared to avoid having old outputs appear simultaneusly with new inputs. To produce new outputs, click the PROCESS button.


The Values box should contain the data observations, with each observation in a separate row. For each observation the first column contains an identifier (or row label) that is not used in the computations. An identifier may use any characters, but no spaces. The remaining entries in each row give the data values. These must be valid numbers using only digits and an optional decimal point. All entries must be separated by one or more spaces and/or tabs. The top row should contain column identifiers for the data values. Each should use six or fewer characters (of any type), but no spaces. Blank rows may be included in the table and it is not necessary for your data to "line up" in columns as long as it conforms to the rules given above.

The Half-life value controls the weightings used in the analysis. If the value entered here is zero, each observation is assigned the same weight. If another value is entered, each observation is assign a weight equal to 2^(1/h) times the prior observation, where h is the indicated half-life. This has the characteristic that the value h periods prior to the present period will receive half the weight assigned the present period (hence the name).

The Scale factor can be used to annualize monthly or quarterly data. Mean values are multiplied by this factor, while standard deviations are multiplied by the square root of the factor. A scale factor of 1 will, in effect, provide no scaling.

You may choose any of three different Output formats. If you want a table showing only the statistics, select the first. If you wish the output to be in a format ready to be used as input for the reverse optimization worksheet, select the second. To obtain output in a format ready to be used as input for the optimization worksheet, select the third.


The output is in the form of a table with columns for computed statistics and, optionally, other information needed for further analysis.

Each Mean is an arithmetic mean for the data series, using the selecting weighting scheme and scale factor.

Each Standard Deviation (StdDev) uses the selected weighting scheme and scale factor. No adjustment is made for "degrees of freedom", even when equal weights are assigned to the observations.

Each of the Correlation Coefficients uses the selected weighting scheme; the scale factor need not be applied, since it does not affect correlation. The correlation coefficients are given in columns labelled c:*** where *** stands for the name of the asset class.


You may enter any desired text in this box to describe the source of the input data, etc..

Written by Prof. William F. Sharpe, Stanford University
This version January 2, 1997