Microsoft Excel for Stock and Option Traders by Jeff Augen

Author:Jeff Augen
Language: eng
Format: epub
Publisher: Pearson Education Limited (US titles)
Published: 2011-03-16T04:00:00+00:00

Decimal Date Conversion

Traditional stock charting programs display each day’s information as if the dates are evenly spaced. They are not. Between Monday and Friday each close occurs 24 hours after the last. However, Friday and Monday closes are spaced by 36 hours. Holidays and weekends represent additional distortions, as do special situations such as the extended market closings that followed the events of September 11, 2001, in the U.S. Proportionally correct stock charts can be created using the sequential serial numbers discussed in the previous section. We can extend this approach by creating more familiar decimal dates that consist of a recognizable year followed by a precisely correct decimal portion of a year. These dates are much easier to understand than the more cryptic serial number format. For example, the Microsoft serial date that corresponds to December 1, 2009, at 4:00 p.m. is 40148.6667. The more comprehensible decimal equivalent is 2009.9169. A quick glance reveals the year 2009 and an additional 91.69% of a year. This number can be generated using Excel’s year fraction function (YEARFRAC) along with some additional calculations.

YEARFRAC calculates the fraction of a year represented by the number of whole days between two dates. It uses the syntax

YEARFRAC(start_date, end_date, basis)

where basis defines various calculation methods: 1 for actual/actual, 2 for actual/360, and 4 for actual/365.

The date we want to convert will be represented by end_date in the function. The following 3 steps transform end_date into a decimal date:

1. Create a string that corresponds to January 1 of the year in end_date. This string will represent start_date in the year fraction calculation.

2. Use the YEARFRAC function to generate the fraction of a year between the 2 dates.

3. Add the result generated by YEARFRAC to the start_date (January 1) to create a decimal date.

We can further adjust the decimal date to represent the 4:00 PM closing time of the market. Since 4:00 PM represents 67% of a single day, the adjustment is equal to (1/365) × 0.66667, or 0.0018. This factor is added to the decimal date to create a final value that will be entered in the table. In the absence of an adjustment, the first day of each year will have a year fraction equal to 0.0000—January 1, 2008, will be represented as 2008.0000. This designation is generally acceptable for most applications. However, attempting to avoid the 0.0000 designation by adding a full day will cause December 31, 2008, to be represented as 2009.0000.

The sequence outlined in Table 2.5 will convert a date (2006/11/20) stored in cell A2 to a decimal date. The conversion uses cell B2 to store the start_date (January 1, 2006), cell C2 to store the year fraction, and cell D2 to store the final result including the 4:00 p.m. adjustment.

Table 2.5 Decimal date calculation (3 steps).

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.