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

Continue reading...

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

Continue reading...

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

Continue reading...