Tip #2002-9: User Defined Function To Find End Of Quarter Date
One of the reasons market professionals need historical pricing information is to calculate
performance returns for specific periods against specific benchmark indices. Although some
real-time data vendors offer analytical fields which provide the price for the end of the
previous year, or 3 or 6 months ago, many traders and portfolio managers need to perform
quarter to date calculations.
In order to perform this calculation you need to find the business date for the previous
quarter, and then look up the value in a table of historical data. For a list of available functions,
click a cell and press SHIFT+F3. Although Excel has many useful date functions, it does not offer
a function which finds the last business day for the previous quarter. In this Willow Tip we
are going to discuss how to create a user-defined function which will calculate the last business
date (Monday through Friday) for the previous quarter.
This function will save a lot of time and greatly simplify the process of calculating quarter
to date returns. Our function requires a single argument which is a cell reference containing a
date. Once you create a function you can use it just like any of Excel’s built-in functions. If
you select Function from Excel’s Insert menu and then choose the
User Defined category, the name
of your function will appear. You will be able to insert it in any cell on the worksheets in
your workbook. You will be able to copy and paste it or nest it within other functions just
like any of Excel’s built-in functions.
You create a function with the Visual Basic for Applications editor. It is very similar to
creating a VBA macro. We have created a mini-app which has a user defined function called
EQDate, which takes a cell containing a date as an argument and calculates the last business
day for the previous quarter. The mini-app is similar to the min-app we created for Willow Tip
2002 – 8. It retrieves historical data from the internet but rather than displaying the price
on a chart we have replaced the chart with a table which displays the last business date for the
previous quarter. We then use Excel’s VLOOKUP function to find the price on that day in a range
containing the historical data. Once you have the price on the last business day of the previous
quarter you can easily calculate the quarter to date percent return.
Creating a User Defined Function
Willow Solutions Inc. is a Microsoft Certified Partner with proven expertise and knowledge in developing
proprietary applications used primarily in the financial services industry. The company specializes in solutions
that combine the power of Microsoft Excel and Visual Basic, with an in-depth knowledge of financial markets.
Contact us if you would like to employ our services to help you enhance this model or help you design financial
models or customized applications.
|
