Take the 2-minute tour ×
Quantitative Finance Stack Exchange is a question and answer site for finance professionals and academics. It's 100% free, no registration required.

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:

  1. 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)

  2. 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.

  3. Are there alternative ways to forward adjust prices that you can suggest? Better approaches?

share|improve this question
    
Are you using Excel, R, or something else for the calculations? What is your data source, Yahoo? –  bill_080 Apr 15 '11 at 1:07
    
@bill_080: this is a general question on how to best forward adjust stock prices; obviously, the actual calculations will be done programatically (using a language such as c++/ python etc). the key is how do you forward adjust stock prices? –  user749 Apr 15 '11 at 1:21
    
The reason that I asked about the source is to determine if you're getting a split adjusted close, split/dividend adjusted close, or just a "print close". The calculations depend on the type of close. The reason that I asked about the type of software is that it's easier to just provide the code as the answer (which we can all test/try/fix) rather than a description of the answer. If it's Excel or R, I might be able to help. If it's Python, Matlab, or something else, someone else could probably help. –  bill_080 Apr 15 '11 at 2:50
    
@bill_080: yes, I meant "print close". –  user749 Apr 15 '11 at 3:40
add comment

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
    
@bill_080: thank you. My question is this: suppose that on interval = 6 the stock splits 3-for-1. and say that the return from interval 5 to interval 6 is given to us. Do you agree that this method of adjusting the prices forward would still be valid? same story when there's an X-dividend: if we have the return (from date x to date x+1, where the X-dividend takes place), do you agree that this method of forward adjusting would still hold true? –  user749 Apr 15 '11 at 3:39
    
@user749: I added an edit to my above answer. I used the GE data so you can do the same calculations. –  bill_080 Apr 15 '11 at 4:19
    
@bill_080: thank you. so it appears from your example that my forward adjusting method does work in all situations (including splits / dividends). do you see any situations where it won't work? any pitfalls? –  user749 Apr 15 '11 at 14:24
    
@user749: I added Edit 2 to my above answer. –  bill_080 Apr 15 '11 at 17:39
    
@bill_080: thank you. the need to re-calculate all data is exactly what I want to avoid. Because in such a case, 'past' indicators / calculations become meaningless when you back-adjust. forward adjusting keeps everything in-tact, although, as you say, the 'price' is not the actual traded price. I have the same question regarding 1-min intervals forward adjustment, and hot it will / should influence the correctness of end-of-day figures. I will ask it as a separate question, pointing to this one as a reference. thank you. –  user749 Apr 15 '11 at 18:05
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.