Saturday, March 08, 2008
Testing a String for Proper Case
Have you ever needed to check a string in a worksheet cell to see if it is in proper case? By "proper case" I mean that the first letter of each word in the string is capitalized and the other characters are lowercase. For example, you may have a list of people’s names (maybe for a mailing list) and you need to be sure that the names are written properly (e.g., John Smith, not john Smith or John sMith). It seems that this should be easy in Excel, but it isn’t obvious (at least there is no easy, built-in function that you can use to validate the string). In this post I will demonstrate three
Comments (1) • Permalink • Tell-a-Friend
Monday, January 14, 2008
Disallow Special Characters Using Data Validation
Excel’s data validation is a wonderful tool for ensuring that only acceptable data is entered into a cell. This is particularly helpful when users other than the one who created the worksheet will be filling in the data. The data validation tool allows you to set up rules that govern the types of data that are allowable, to present input messages that inform the user about the rules, and to display error messages if the rules are violated. Quite obviously, this can reduce errors in the worksheet.
The most powerful type of data validation is the Custom type, which is based on formulas. In
Comments (0) • Permalink • Tell-a-Friend
Friday, January 04, 2008
Chart the Yield Curve Using Yahoo! Finance Data
The U.S. Treasury yield curve is of tremendous importance in the financial world, so those of us who teach finance often find it desirable to show a chart of the current yield curve. I’ve used many sources over the years, but my favorite disappeared a few years ago. The data is readily available, so creating your own yield curve isn’t that difficult. It is a bit of a hassle, though. In this post I’m going to show how you can create a “live” U.S. Treasury yield curve using Excel’s Web Query feature and data from Yahoo! Finance.
If you go to the Yahoo! Finance Bonds Center you will see a
Comments (8) • Permalink • Tell-a-Friend
Tuesday, August 28, 2007
Dealing With Unusual Date Formats
I’m sure that you have all been through this: You go to a Website (or a database, probably from a mainframe) to get some data. The problem is that the dates are formatted in some obscure way that Excel doesn’t understand. In this post, I’ll give you an idea of how to convert these dates into something that you can work with.
Yesterday I was working on a project that involves charting some economic data series. Frequently, charts of economic time series show shaded areas that indicate a period of recession (I’ve got a post that shows how to do that). So, I needed to know the dates for
Comments (2) • Permalink • Tell-a-Friend
