How should one correctly forward adjust historical prices given a time series of Open, High, Low, Close, Return?
Suppose that the data series is given below ('1' is the oldest interval; '5' is the latest one):
interval open high low close return
---------------------------------------
1 17.36 17.54 17.17 17.19 0.00%
2 17.38 17.41 17.2 17.28 0.52%
3 17.62 17.64 17.35 17.36 0.46%
4 17.42 17.6 17.34 17.58 1.27%
5 17.41 17.61 17.29 17.45 -0.74%
I thought of using the following approach. Start with an arbitrary 100 value for all fields on interval 1. So for interval = 1, we have:
interval adj_open adj_high adj_low adj_close return
---------------------------------------------------
1 100 100 100 100
Then, for the following interval (interval 2), we first calculate the adjusted close based on the return from interval 1 to interval 2:
adjusted_close_on_interval_2 = 100 * (1 + 0.52 / 100) = 100.5235
Then, we can calculate the adjusted open, high, and low on interval 2 based on the percentage distances from the actual close on interval 2 to these figures:
open_to_close_ratio_on_interval_2 = (17.38 - 17.28) / 17.28 = 0.5787%`
hence,
adjusted_open_on_interval_2 = 100.5235 * ( 1 + 0.5787% ) = 101.10529
In the same manner:
high_to_close_ratio_on_interval_2 = (17.41 - 17.28) / 17.28 = 0.7523%
hence,
adjusted_high_on_interval_2 = 100.5235 * ( 1 + 0.7523% ) = 101.2798
and
low_to_close_ratio_on_interval_2 = (17.2 - 17.28) / 17.28 = -0.4629%`
hence,
adjusted_high_on_interval_2 = 100.5235 * ( 1 + ( -0.4629% ) ) = 100.05817
In the same manner, we continue for the rest of the intervals and get the table of the forward adjusted prices:
interval adj_open adj_high adj_low adj_close
--------------------------------------------
1 100 100 100 100
2 101.11 101.28 100.06 100.52
3 102.5 102.62 100.93 100.99
4 101.34 102.39 100.87 102.27
5 101.28 102.44 100.58 101.51
(imagine that this time series continues for thousands of intervals...)
My questions:
Is this a valid approach for forward-adjusting of prices? Do you see any flaws in it? For example, suppose I adjust this way over the course of (say) 10 years of data. And suppose that I have a simulated trade where I buy in the ADJUSTED_LOW price of interval = 50 and sell at the ADJUSTED_CLOSE of interval = 359; will the trade % return that will be calculated from the adjusted prices, be the same as the return I would have gotten in practice, trading 'normal' prices? (neglecting t-costs etc)
Do you agree that this method would be valid even when a stock has a split or a dividend? (the new adjusted prices are ALWAYS calculated based on the interval's return, and this will be correct as it is independent of any corporate actions). If you disagree with this statement, please explain.
Are there alternative ways to forward adjust prices that you can suggest? Better approaches?
Answer
Assuming your data provides the "print close", and there's no dividends and no splits, here's the R code where all values are divided by the first "print close" of 17.19. This appears to match your results, except for the first line.
texinp <- "
interval open high low close
1 17.36 17.54 17.17 17.19
2 17.38 17.41 17.2 17.28
3 17.62 17.64 17.35 17.36
4 17.42 17.6 17.34 17.58
5 17.41 17.61 17.29 17.45"
dat <- read.table(textConnection(texinp), header = TRUE)
dat1 <- dat
dat1$open <- 100*dat$open/dat$close[1]
dat1$high <- 100*dat$high/dat$close[1]
dat1$low <- 100*dat$low/dat$close[1]
dat1$close <- 100*dat$close/dat$close[1]
dat1
The output from the above "dat1" statement is:
interval open high low close
1 1 100.9889 102.0361 99.88365 100.0000
2 2 101.1053 101.2798 100.05817 100.5236
3 3 102.5015 102.6178 100.93077 100.9889
4 4 101.3380 102.3851 100.87260 102.2688
5 5 101.2798 102.4433 100.58173 101.5125
As an example of an "adjusted close", here's GE's dividend/split history:
http://www.ge.com/investors/stock_info/dividend_history.html
Notice that on 05/05/2000 they had a 3-for-1 split and had an X-dividend date of 07/05/2000. The Yahoo data for that time frame shows:
http://finance.yahoo.com/q/hp?s=GE&a=03&b=1&c=2000&d=07&e=1&f=2000&g=d
If you download that data into a spreadsheet, you'll see the "Close" drop from 158 on 05/05/2000 to 52.44 on 05/08/2000 (while the "Adjusted Close" already accounts for this split). On 07/05/2000, the 0.137 dividend is paid. Yahoo's "Adjusted Close" already accounts for this dividend.
Edit 1 ========================================================
Data on GE from Yahoo
Date Open High Low Close Volume Adj Close
5/03/2000 159.50 160.00 154.56 156.06 16594800 37.66
5/04/2000 157.44 157.50 152.75 154.00 15411000 37.16
5/05/2000 154.00 160.00 153.50 158.00 20685900 38.13 <----3-for-1 split
5/08/2000 52.13 52.88 51.63 52.44 11676500 37.96
5/09/2000 52.38 52.69 50.88 52.13 13439400 37.74
5/10/2000 51.50 52.06 50.06 50.63 15059400 36.65
6/30/2000 49.25 53.11 49.06 53.00 19076300 38.37
7/03/2000 52.50 52.50 51.38 52.00 6604600 37.64
7/05/2000 52.25 52.25 49.50 49.94 13558000 36.25 <----0.137 Dividend Paid
7/06/2000 50.06 51.00 49.81 50.19 9616500 36.43
7/07/2000 50.75 51.50 50.31 51.31 9937800 37.24
On 05/05/2000, the "Close" was 158.00. A 3-for-1 split gives a "split" close of 52.67 (158.00/3). So, from 05/05/2000 to 05/08/2000, the price dropped from 52.67 to 52.44 or 0.995633 (52.44/52.67). Check this against the change in the "Adj Close", 38.13 * 0.995633 = 37.96 which is the "Adj Close" for 05/08/2000.
The same idea holds for the dividend on 07/05/2000. The "Close" for 07/05/2000 was 49.94. Add to that the 0.137 dividend that was paid on that day, gives a close of 50.077. The "Close" for 07/03/2000 was 52.00 or 0.963019 (50.077/52.00). Check this against the change in the "Adj Close", 37.64 * 0.963019 = 36.25 which is the "Adj Close" for 07/05/2000.
Edit 2 ================================================
Using "forward" to describe your method versus Yahoo's backward method, the good part of Yahoo's method is that the most recent "adjusted close" is the same as the "print close", so that it makes sense to just about everyone. Using GE as the example:
http://finance.yahoo.com/q/hp?s=GE+Historical+Prices
Yesterday's (04/14/2011) 20.00 "adjusted close" is the same as the 20.00 "print close". The bad part of Yahoo's method is that ALL adjusted close values must be recalculated when a dividend is paid or there's a split. Whereas, your "forward" method would not require a complete recalculation.
From that link, notice that Yahoo's GE data starts at Jan 2, 1962. When you look at data from that time frame you'll get:
http://finance.yahoo.com/q/hp?s=GE&a=00&b=1&c=1962&d=01&e=1&f=1962&g=d
Notice that the starting "adjusted close" is 0.17 while the starting "print close" was 74.50. That's a ratio of 438.2 (74.50/0.17). This same ratio would apply if the data was "forward adjusted", but started at 74.50. That means your "adjusted close" for yesterday's (04/14/2011) "print close" of 20.00 would be 8764.7 (20.00 * 438.2), a very large number that bears little resemblance to the 20.00 "print close". My guess is that most people would be confused by that number.
If you index the starting "adjusted close" to 100 (as shown in your original question), the starting ratio would be 588.2 (100/0.17) giving you an "adjusted close" yesterday of 11764.7.
As far as other problems go, the two techniques are essentially the same, and both methods have been used since the beginning. You do have to get into the details of mergers/acquisitions, especially when there's a "return of capital" involved. Even relatively simple mergers can be a pain (for example, the Exxon/Mobil merger). It's not the "forward" or "backward" technique that's the pain, it's collecting/sorting/applying the data.
No comments:
Post a Comment