Note to self, migrating Wordpress to a new web host is not as simple and straightforward as it looks. Do not try upgrading and migrating at the same time. And most importantly, do not change the directory structure while migrating and upgrading. Sigh.
This may only be of interest to civil engineers and planners, but topographic maps generated by the U.S. Geological Service are public domain because tax payer monies are used to generate the maps. However, map services have charged for these maps because it costs money to print and distribute the physical copies.
This has changed somewhat recently when a collection was taken up by these guys to liberate these maps. The maps are now distributed by the Internet Archive and can be downloaded for free from this “temporary” directory. It is organized by state, and the maps are in TIF format. XML meta-data files area also available.
About six months ago I bought a 500 GB hard drive to store my ever growing podcast collection. At the time I wanted to see how full disk encryption would affect my system. I’ve been considering adding encryption to main file servers at work, when the time comes to upgrade/replace the hardware. Hopefully, that will be a year or more down the road, so the experimenting and testing has to be done now rather than later.
I chose to keep my C drive unencrypted and store only the OS and applications on it. All other data were stored on the new drive which was encrypted with Truecrypt. I chose to create a single Truecrypt file that took up essentially all of the space on the hard drive. A few months prior to this I had played with encrypting the entire partition on an external USB hard drive. Every time I plugged in that drive, Windows would think that the drive was unformatted and kept bugging me to format it. I figure it’s just a matter of time before I or someone else formats that drive accidentally. I keep a fair number of wiped hard drives around my desk, so it wouldn’t be too unusual to mistake an encrypted disk with a wiped disk. I figure this way, I could always identify an encrypted disk by the file name.
So I load up the drive and all goes well up to about two weeks ago. I’ve also been using removable disk trays to make swapping hard drives easier. I think the tray or the IDE ribbon decided to die on me and corrupt the hard drive for good measure. I tried everything I could think of to repair the drive without formatting it. Most of the data on the hard drive wasn’t critical, and my important data was safely backed up. So I gave up and formatted the hard drive. It works just fine now.
The whole experience made me think a bit more carefully about encryption. Although none of the problems were caused by encryption, it does limit troubleshooting options. I have a few data recovery software packages. None of them could really be put to use in this case because I couldn’t get the corrupted drive to mount correctly. Even if it did mount, I’m starting to think that they would not be useful anyway. Since the data would be fragmented all over the disk and encrypted, how could a data recovery tool figure out a way to reassemble the encrypted volume. Losing any part of that volume could and probably will mean losing the whole volume. So now I’m thinking of compartmentalizing the encrypted volumes somehow. Not sure how many or how large the volumes should be, but it’ll make a recovery somewhat more likely.
Marv
Common Error Messages
#NUM!
Occurs when the numeric inputs for the formula are unacceptable or cause a problem. For example =SQRT(-1) will cause a #NUM! error because the result is not a real number. =10^500 will also cause a #NUM! error because the result is too large for Excel to handle.
#NAME?
Occurs when the text in the formula is not understood. It could be a typo in the name of a named range of cells or a function like =SQT() instead or =SQRT(), omitting a colon (:) in a cell range, or entering text in a formula without enclosing it in ” “. This error also occurs when using a function that has not been loaded in Excel. This is likely to happen if you use XIRR() without loading the Analysis Toolpak.
#REF!
Occurs when a cell reference is missing because the referenced cell was deleted.
#DIV/0!
Occurs when the formula divides by 0. It usually happens when the formula is something like =A1/B1 and B1 is empty.
#VALUE!
Occurs when the wrong type of input is fed into a formula.
For example, formula is expecting numeric inputs and text is entered. =A1*B1, when the contents of either A1 and B1 is not a number. The exception to this rule is TRUE and FALSE which Excel reads as 1 and 0 respectively.
A range of cells is entered when the formula is expecting a single cell.
Typing an array formula without using Ctrl+Shift+Enter.
Circular References
When one or more formulas reference each other such that a logical loop is formed, Excel will trigger an error message telling you that you’ve created a circular reference. For example, if cell A1 has the formula =B1 and cell B1 has =A1, this will create a circular reference and Excel will ask if you are sure you want to use this formula. Excel will allow circular references if you really want them. Circular references are an infinite loop, but they will not crash Excel because Excel caps the number of loop iterations. There are some useful applications of circular references, they can be used to calculate solutions that can only be found through successive iterations. But more often than not, they are errors in a spreadsheet.
Debugging and the Audit Tool
When writing a spreadsheet, typos inevitably occur. The problem is you might not figure out where they are until after you’re done. The trick to spotting an error in your spreadsheet is to test it using simple data with round figures. The data should be easy enough for you to figure out what the formulas should spit out. For example, if you’re debugging financial ratios from an income statement, input $10 M revenue, $5 M cost of sales, $1 M Net Income, & 2 M shares. You can see that Gross Margin should be 50%, Net Margin 10%, and EPS $0.50. Any errors in the spreadsheet will be easy to pick out.
Once you’ve found a formula that has a bug in it, fixing it is usually pretty simple. But, if the spreadsheet is large enough, it may be difficult to trace the references in a formula when you’re debugging the sheet. To make this easier, Excel has the Audit command. It will draw a line and arrow from the cell you select to all precedents (inputs into the formula) or decendants (all cells that use the selected cell as an input).
Click on the buggy cell and go to Tools –> Auditing –> Trace Precedents. Several lines and arrows will appear on the spreadsheet pointing to the inputs in the formula. Similarly, Tools –> Auditing –> Trace Dependants will trace a path to all formulas that use that cell as an input.
These are some of the common statistical functions in Excel that are useful in investing. Again, these are not the only ones to be found in Excel, I’m just filtering the onses most useful to investors.
AVERAGE(number1,number2...) Returns the average or arithmatic mean of the series.
GEOMEAN(number1,number2...) Returns the geometric mean of the series. See Note 1.
for difference between geometric and arithmatic mean.
MEDIAN(number1,number2...) Returns the median (i.e. midpoint) of the series
MAX(number1,number2...) Returns the maximum value in the series
MIN(number1,number2...) Returns the minimum value in the series
COUNT(number1,nubmer2...) Returns the number of values in the series.
FORECAST(X,known_X,known_Y) Extrapolates Y given a series of known X and Y values.
Uses linear regression (i.e. fitting a straight line
through the data) to project forward results.
TREND(known_y,known_x,X,const) Extrapolates Y given a series of known X and Y values.
Similar to FORECAST(), TREND() uses linear regression.
GROWTH(known_y,known_x,x,const) Extrapolates Y given a series of known X and Y values.
Similar to TREND(), but GROWTH() fits an exponential
curve through the data to project forward results.
LINEST(known_y,known_x,const,stats) Returns the statistical information on the line that
best fits the data. This function must be entered as
an array formula. See Note 2.
LOGEST(known_y,known_x,const,stats) Returns the statistical information on the exponential
curve that best fits the data. This function must be
entered as an array formula. See Note 2.
number1, number2, etc. are variables that can be entered directly into the equation or can be replace with a cell reference or range of cells.
known_X, known_Y are variables (usually a range of cells) that are input into the regression analysis.
X is the next X variable. Typically TREND(), GROWTH() etc. solve for an extrapolated Y value given the known_Xs & known_Ys and an assumed X. For example the annual returns on a portfolio are 3%, 17%, 23%, & -15%. These would be the known_Y values, the known_X values are the years 1, 2, 3, & 4. X would be the year for which you want TREND() to project a corresponding Y value.
Const is an optional TRUE/FALSE variable which can be excluded. It is used in the line/curve fitting function to force the y-interect to go through 0. If the flag is set to TRUE, the function will return a slope for a line/curve through the origin.
Stats is an optional TRUE/FALSE variable which can be excluded. If it is set to TRUE, additional statistical information (mn,mn-1,…,m1,b;sen,sen-1,…,se1,seb;r 2,sey) will be provided by the function.
Note 1:
When talking about average growth rates there are two types, arithmatic mean and geometric mean. Arithmatic mean is what is commonly known as the average, and is calculated from summing all the terms and dividing by the number of terms (i.e. (X + Y + Z) / 3). Geometric mean is calculated by multiplying all the terms and taking the n_th root of the product (i.e. (X * Y * Z) ^ (1/3) ).
The significance of this is in growth rates. Growth rates fluctuates from year to year and trying to nail down an average growth rate over a number years is a bit difficult if you use arithmatic mean. Take the following example of a portfolio’s 10 year performance.
Year Growth Value 0 $10,000 1 5% $10,500 2 8% $11,340 3 12% $12,701 4 23% $15,622 5 -2% $15,310 6 -6% $14,391 7 35% $19,428 8 10% $21,371 9 16% $24,790 10 1% $25,038
Average Growth (arithmatic mean):
(5% + 8% + 12% + 23% - 2% - 6% + 35% + 10% + 16% + 1%) / 10 = 10.2%
Now, according to the law of compounding, the ending value of the portfolio could be calculated given the starting value, growth rate and time period. Since there were no deposits or withdrawls to the portfolio, the compound interest equation can be use. Starting_Value * (1 * Growth_Rate) ^ Time. But if you plug in the values from this example you get
$10k * (1 + 10.2%) ^ 10 = $26,413.
$1,375 higher than the actual ending value.
To get the true average growth rate, also known as the compounded annual growth rate (CAGR), you need to use the geometric mean. It is calculated a bit differently because only positive numbers can be input into the equation.
(1.05 * 1.08 * 1.12 * 1*23 * 0.98 * 0.94 * 1.35 * 1.1 * 1.16 * 1.01) ^ (1 / 10) = 9.6%
Using the geometric mean in the compound interest equation we get
$10k * (1 + 9.6%) ^ 10 = $25,038
which matches the actual results. For further discussion on the difference and applicability of the geometric mean, see this article
Note 2:
Array formulas are for functions that return several pieces of data. LINEST() return two pieces of information, the slope and y-intercept of the best fit line. Since two separate numbers cannot occupy the same cell, the function needs to be spread across two cells. Example:
A B
Row 1 1 1
Row 2 2 2
Row 3 3 3
Row 4 4 5
Row 5 5 8
Row 6 6 13
Row 7 7 21
Row 8 8 34
Row 9 9 55
Row 10 10 89
To enter the LINEST() function, highlight cells A11 & B11. Then type
=LINEST(B1:B10,A1:A10)
but instead of hitting ENTER at the end, hold down SHIFT + CTRL + ENTER.
The results should be 8.31 and -22.60.
Similarly, =LOGEST(B1:B10,A1:A10) should return 1.63 and 0.69.
To project values of Column B for values of 11, 12, 13, 14 & 15 in Column A, either the TREND() or FORECAST() functions can be used for linear regression or the GROWTH() function can be used for exponential growth projections.
Example of TREND():
A B
Row 1 1 1
Row 2 2 2
Row 3 3 3
Row 4 4 5
Row 5 5 8
Row 6 6 13
Row 7 7 21
Row 8 8 34
Row 9 9 55
Row 10 10 89
Row 11 11 =TREND(B1:B10,A1:A10,A11)
Row 12 12 =TREND(B1:B10,A1:A10,A12)
Row 13 13 =TREND(B1:B10,A1:A10,A13)
Row 14 14 =TREND(B1:B10,A1:A10,A14)
Row 15 15 =TREND(B1:B10,A1:A10,A15)
Results:
A B
Row 1 1 1
Row 2 2 2
Row 3 3 3
Row 4 4 5
Row 5 5 8
Row 6 6 13
Row 7 7 21
Row 8 8 34
Row 9 9 55
Row 10 10 89
Row 11 11 68.80
Row 12 12 77.11
Row 13 13 85.42
Row 14 14 93.73
Row 15 15 102.04
If the GROWTH() function was used in place of TREND(), the results would be:
A B
Row 1 1 1
Row 2 2 2
Row 3 3 3
Row 4 4 5
Row 5 5 8
Row 6 6 13
Row 7 7 21
Row 8 8 34
Row 9 9 55
Row 10 10 89
Row 11 11 147.74
Row 12 12 240.69
Row 13 13 392.11
Row 14 14 638.79
Row 15 15 1040.67
To get an idea of how well the TREND() and GROWTH() projections work, I created some charts plotting the projected values against the real values. In this example I used Fibonacci numbers as a pattern to fit a regression line.
You can see from the charts that the exponential regression works much better than linear regression for fitting this data set. If LOGEST() had some more data points to work with, the slope of the regression line would go to phi.