New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Yahoo Finance now providing unadjusted OHL #174

Closed
joshuaulrich opened this Issue Jun 26, 2017 · 5 comments

Comments

Projects
None yet
5 participants
@joshuaulrich
Owner

joshuaulrich commented Jun 26, 2017

As documented in commit 7936018 for #157, Yahoo was initially providing adjusted Open, High, and Low prices. This appears to have been reverted to the prior behavior, as you can see the data in the downloaded file has unadjusted Open, High, and Low prices.

Date,Open,High,Low,Close,Adj Close,Volume
2007-05-25,151.500000,152.020004,151.179993,151.690002,122.545494,83309200
2007-05-29,151.940002,152.500000,151.449997,152.240005,122.989822,82020000
2007-05-30,151.460007,153.539993,151.339996,153.479996,123.991585,129013600
2007-05-31,153.669998,153.889999,153.119995,153.320007,123.862366,114866700
2007-06-01,153.880005,154.399994,153.509995,154.080002,124.476273,107771700
2007-06-04,153.539993,154.389999,153.479996,154.100006,124.492462,78008800
2007-06-05,153.740005,153.899994,152.860001,153.490005,123.999619,126917900

This causes the data returned by getSymbols() to be incorrect, because it had been unadjusting the adjusted OHL prices. Now the OHL prices are incorrect because getSymbols() is unadjusting unadjusted prices.

R> head(getSymbols("SPY", from = '2007-05-31', auto.assign = FALSE))
           SPY.Open SPY.High SPY.Low SPY.Close SPY.Volume SPY.Adjusted
2007-05-31  190.217  190.489 189.536    153.32  114866700     123.8624
2007-06-01  190.477  191.120 190.019    154.08  107771700     124.4763
2007-06-04  190.056  191.108 189.982    154.10   78008800     124.4925
2007-06-05  190.303  190.501 189.214    153.49  126917900     123.9996
2007-06-06  189.214  189.325 187.840    151.84  164096800     122.6667
2007-06-07  187.605  188.768 184.510    149.10  232414600     120.4531

Also need to check if the Adjusted Close is still only split-adjusted or is back to being split- and dividend-adjusted.

Thanks to mql4beginner for noticing this and asking a question on StackOverflow.

@joshuaulrich joshuaulrich added the bug label Jun 26, 2017

@joshuaulrich joshuaulrich self-assigned this Jun 26, 2017

@joshuaulrich

This comment has been minimized.

Show comment
Hide comment
@joshuaulrich

joshuaulrich Jun 26, 2017

Owner

It looks like the adjusted column is now adjusted for dividends again. Take MSFT's $3.08/share special dividend on 2004-11-15 for example. The OHL have the same $3/share drop as the close price, but the adjusted close does not (because the shareholder received the dividend).

R> MSFT["2004-11"]
           MSFT.Open MSFT.High MSFT.Low MSFT.Close MSFT.Volume MSFT.Adjusted
2004-11-01     28.16     28.28    27.96      28.08    72930900      19.06621
2004-11-02     28.26     28.47    28.03      28.24    89417100      19.17484
2004-11-03     28.65     28.65    28.31      28.47    79666700      19.33102
2004-11-04     28.38     29.00    28.38      29.00    87867700      19.69088
2004-11-05     29.21     29.36    29.03      29.31    95337700      19.90137
2004-11-08     29.18     29.48    29.13      29.28   112802100      19.88101
2004-11-09     29.43     29.89    29.35      29.77   100401000      20.21371
2004-11-10     29.92     30.00    29.69      29.73    84097700      20.18655
2004-11-11     29.89     30.08    29.82      29.98    87358900      20.35630
2004-11-12     30.16     30.20    29.80      29.97   162269000      20.34950
2004-11-15     27.34     27.50    27.20      27.39   104468000      20.72789
2004-11-16     27.33     27.34    27.05      27.12    64522600      20.52357
2004-11-17     27.25     27.35    27.06      27.17    58830700      20.56141
2004-11-18     27.13     27.17    27.00      27.07    63249900      20.48573
2004-11-19     27.03     27.07    26.84      26.86    85808600      20.32681
2004-11-22     26.75     26.82    26.10      26.65    92410800      20.16788
2004-11-23     26.52     26.70    26.40      26.53    70459700      20.07708
2004-11-24     26.62     26.73    26.40      26.64    60069200      20.16031
2004-11-26     26.56     26.82    26.55      26.60    24398700      20.13004
2004-11-29     26.64     26.95    26.61      26.77    67079900      20.25870
2004-11-30     26.75     27.01    26.70      26.81    75960400      20.28896

But it seems like the OHL prices are still adjusted for splits. Take MSFT's last 2/1 split on 2003-02-18 for example. The close price drops by half, but the OHL prices do not.

           MSFT.Open MSFT.High MSFT.Low MSFT.Close MSFT.Volume MSFT.Adjusted
2003-02-03    23.965    24.550   23.730      48.56    80129800      16.29344
2003-02-04    23.900    23.970   23.440      47.32    81854800      15.87738
2003-02-05    23.915    24.265   23.365      46.96   101072000      15.75658
2003-02-06    23.430    23.810   23.280      47.42    83312000      15.91093
2003-02-07    23.940    23.975   23.205      46.58    75208000      15.62909
2003-02-10    23.400    23.760   23.270      47.38    74081400      15.89751
2003-02-11    23.650    23.835   23.000      46.44    84292000      15.58211
2003-02-12    23.275    23.550   23.130      46.44    71315200      15.58211
2003-02-13    23.205    23.560   23.065      46.99    73558200      15.76665
2003-02-14    23.625    24.250   23.385      48.30    90446400      16.20620
2003-02-18    24.620    24.990   24.400      24.96    57415500      16.74977
2003-02-19    24.820    24.880   24.170      24.53    46902700      16.51414
2003-02-20    24.770    24.870   24.100      24.14    50897200      16.25158
2003-02-21    24.290    24.800   23.700      24.63    56853200      16.58146
2003-02-24    24.440    24.500   23.840      24.07    62403700      16.20445
2003-02-25    23.540    24.270   23.380      24.19    68113000      16.28524
2003-02-26    24.070    24.470   23.580      23.61    57096000      15.89477
2003-02-27    23.900    24.210   23.300      23.58    75434300      15.87458
2003-02-28    23.740    24.070   23.550      23.70    56585400      15.95536
Owner

joshuaulrich commented Jun 26, 2017

It looks like the adjusted column is now adjusted for dividends again. Take MSFT's $3.08/share special dividend on 2004-11-15 for example. The OHL have the same $3/share drop as the close price, but the adjusted close does not (because the shareholder received the dividend).

R> MSFT["2004-11"]
           MSFT.Open MSFT.High MSFT.Low MSFT.Close MSFT.Volume MSFT.Adjusted
2004-11-01     28.16     28.28    27.96      28.08    72930900      19.06621
2004-11-02     28.26     28.47    28.03      28.24    89417100      19.17484
2004-11-03     28.65     28.65    28.31      28.47    79666700      19.33102
2004-11-04     28.38     29.00    28.38      29.00    87867700      19.69088
2004-11-05     29.21     29.36    29.03      29.31    95337700      19.90137
2004-11-08     29.18     29.48    29.13      29.28   112802100      19.88101
2004-11-09     29.43     29.89    29.35      29.77   100401000      20.21371
2004-11-10     29.92     30.00    29.69      29.73    84097700      20.18655
2004-11-11     29.89     30.08    29.82      29.98    87358900      20.35630
2004-11-12     30.16     30.20    29.80      29.97   162269000      20.34950
2004-11-15     27.34     27.50    27.20      27.39   104468000      20.72789
2004-11-16     27.33     27.34    27.05      27.12    64522600      20.52357
2004-11-17     27.25     27.35    27.06      27.17    58830700      20.56141
2004-11-18     27.13     27.17    27.00      27.07    63249900      20.48573
2004-11-19     27.03     27.07    26.84      26.86    85808600      20.32681
2004-11-22     26.75     26.82    26.10      26.65    92410800      20.16788
2004-11-23     26.52     26.70    26.40      26.53    70459700      20.07708
2004-11-24     26.62     26.73    26.40      26.64    60069200      20.16031
2004-11-26     26.56     26.82    26.55      26.60    24398700      20.13004
2004-11-29     26.64     26.95    26.61      26.77    67079900      20.25870
2004-11-30     26.75     27.01    26.70      26.81    75960400      20.28896

But it seems like the OHL prices are still adjusted for splits. Take MSFT's last 2/1 split on 2003-02-18 for example. The close price drops by half, but the OHL prices do not.

           MSFT.Open MSFT.High MSFT.Low MSFT.Close MSFT.Volume MSFT.Adjusted
2003-02-03    23.965    24.550   23.730      48.56    80129800      16.29344
2003-02-04    23.900    23.970   23.440      47.32    81854800      15.87738
2003-02-05    23.915    24.265   23.365      46.96   101072000      15.75658
2003-02-06    23.430    23.810   23.280      47.42    83312000      15.91093
2003-02-07    23.940    23.975   23.205      46.58    75208000      15.62909
2003-02-10    23.400    23.760   23.270      47.38    74081400      15.89751
2003-02-11    23.650    23.835   23.000      46.44    84292000      15.58211
2003-02-12    23.275    23.550   23.130      46.44    71315200      15.58211
2003-02-13    23.205    23.560   23.065      46.99    73558200      15.76665
2003-02-14    23.625    24.250   23.385      48.30    90446400      16.20620
2003-02-18    24.620    24.990   24.400      24.96    57415500      16.74977
2003-02-19    24.820    24.880   24.170      24.53    46902700      16.51414
2003-02-20    24.770    24.870   24.100      24.14    50897200      16.25158
2003-02-21    24.290    24.800   23.700      24.63    56853200      16.58146
2003-02-24    24.440    24.500   23.840      24.07    62403700      16.20445
2003-02-25    23.540    24.270   23.380      24.19    68113000      16.28524
2003-02-26    24.070    24.470   23.580      23.61    57096000      15.89477
2003-02-27    23.900    24.210   23.300      23.58    75434300      15.87458
2003-02-28    23.740    24.070   23.550      23.70    56585400      15.95536
@selgamal

This comment has been minimized.

Show comment
Hide comment
@selgamal

selgamal Jul 7, 2017

@joshuaulrich suggestion:
How about returning the price history from yahoo as is,
removing "adjust" option if source is yahoo,
and modify warning message with a note that OHL in yahoo are currently adjusted for splits and the price history is returned as is,
Then users can handle the data anyway they like outside getSymbols, maybe reverse adjustments or adjust further for dividends, and any handling can be adapted if changes happen again in the way yahoo provides the price history

For example, I downloaded MSFT yahoo csv from yahoo and reversed adjustments:

> # To simulate returning price history from yahoo as is, importing CSV downloaded from yahoo
> MSFT_yhoo <- read.table("MSFT_yhoo.csv", sep = ",", header = T, stringsAsFactors = F)
> 
> # Convert to xts
> MSFT_yhoo.xts <- xts(MSFT_yhoo[,2:7], order.by = as.Date(MSFT_yhoo$Date) )
> 
> 
> head(MSFT_yhoo.xts)
               Open     High      Low Close Adj.Close     Volume
1986-03-13 0.088542 0.101563 0.088542 28.00  0.065242 1031788800
1986-03-14 0.097222 0.102431 0.097222 29.00  0.067572  308160000
1986-03-17 0.100694 0.103299 0.100694 29.50  0.068737  133171200
1986-03-18 0.102431 0.103299 0.098958 28.75  0.066990   67766400
1986-03-19 0.099826 0.100694 0.097222 28.25  0.065825   47894400
1986-03-20 0.098090 0.098090 0.094618 27.50  0.064077   58435200

> # Users can do whatever they want, adjust for dividends or reverse adjustment
> # For example here I want the reverse existing split adjustment
 
> # First get splits
> splits <- getSplits("MSFT")
> head(splits)
            MSFT.spl
1987-09-21 0.5000000
1990-04-16 0.5000000
1991-06-27 0.6666667
1992-06-15 0.6666667
1994-05-23 0.5000000
1996-12-09 0.5000000

> # Then get split ratio
> splRatio <- adjRatios(splits = splits, close = MSFT_yhoo.xts[,4])

> head(splRatio)
                 Split Div
1986-03-13 0.003472222   1
1986-03-14 0.003472222   1
1986-03-17 0.003472222   1
1986-03-18 0.003472222   1
1986-03-19 0.003472222   1
1986-03-20 0.003472222   1

> # undjust splits
> MSFT.unadjusted <- cbind(Op(MSFT_yhoo.xts)/splRatio$Split,
+                          Hi(MSFT_yhoo.xts)/splRatio$Split,
+                          Lo(MSFT_yhoo.xts)/splRatio$Split,
+                          MSFT_yhoo.xts[,c(4,6)]
+                          )

> head(MSFT.unadjusted)
               Open     High      Low Close     Volume
1986-03-13 25.50010 29.25014 25.50010 28.00 1031788800
1986-03-14 27.99994 29.50013 27.99994 29.00  308160000
1986-03-17 28.99987 29.75011 28.99987 29.50  133171200
1986-03-18 29.50013 29.75011 28.49990 28.75   67766400
1986-03-19 28.74989 28.99987 27.99994 28.25   47894400
1986-03-20 28.24992 28.24992 27.24998 27.50   58435200

> # Compare to MSFT from db  (as it used to be on yahoo previously)
> head(MSFT_db)
        Date    dOpen    dHigh     dLow   dClose    dVolume
1 1986-03-13 25.49952 29.24928 25.49952 27.99936 1031788800
2 1986-03-14 27.99936 29.49984 27.99936 28.99872  308160000
3 1986-03-17 28.99872 29.75040 28.99872 29.49984  133171200
4 1986-03-18 29.49984 29.75040 28.50048 28.75104   67766400
5 1986-03-19 28.75104 28.99872 27.99936 28.24992   47894400
6 1986-03-20 28.24992 28.24992 27.25056 27.50112   58435200
> 
> # Minor rounding differences

Also it might be convenient to change adjustOHLC by

1- Adding an option to select which columns to adjust, something like which.OHLC = character:

which.OHLC = "OHLC" # character containing any combination of OHLC

Open <- if(grepl("O", which.OHLC)) {
  Op(x)*ratio
} else {Op(x)}

High <- if(grepl("H", which.OHLC)) {
  Hi(x)*ratio
} else {Hi(x)}

Low <- if(grepl("L", which.OHLC)) {
  Lo(x)*ratio
} else {Lo(x)}

Close <- if(grepl("C", which.OHLC)) {
  Cl(x)*ratio
} else {Cl(x)}

Adjusted <-
  cbind(Open,
        High,
        Low,
        Close,
        if(has.Vo(x)) Vo(x) else NULL,
       if(has.Ad(x)) Ad(x) else NULL
)

2- Maybe give an option to reverse adjustment, for example reverse.adj = Logical:

ratio <-
if (reverse.adj) {1/ ratio} else {ratio}

selgamal commented Jul 7, 2017

@joshuaulrich suggestion:
How about returning the price history from yahoo as is,
removing "adjust" option if source is yahoo,
and modify warning message with a note that OHL in yahoo are currently adjusted for splits and the price history is returned as is,
Then users can handle the data anyway they like outside getSymbols, maybe reverse adjustments or adjust further for dividends, and any handling can be adapted if changes happen again in the way yahoo provides the price history

For example, I downloaded MSFT yahoo csv from yahoo and reversed adjustments:

> # To simulate returning price history from yahoo as is, importing CSV downloaded from yahoo
> MSFT_yhoo <- read.table("MSFT_yhoo.csv", sep = ",", header = T, stringsAsFactors = F)
> 
> # Convert to xts
> MSFT_yhoo.xts <- xts(MSFT_yhoo[,2:7], order.by = as.Date(MSFT_yhoo$Date) )
> 
> 
> head(MSFT_yhoo.xts)
               Open     High      Low Close Adj.Close     Volume
1986-03-13 0.088542 0.101563 0.088542 28.00  0.065242 1031788800
1986-03-14 0.097222 0.102431 0.097222 29.00  0.067572  308160000
1986-03-17 0.100694 0.103299 0.100694 29.50  0.068737  133171200
1986-03-18 0.102431 0.103299 0.098958 28.75  0.066990   67766400
1986-03-19 0.099826 0.100694 0.097222 28.25  0.065825   47894400
1986-03-20 0.098090 0.098090 0.094618 27.50  0.064077   58435200

> # Users can do whatever they want, adjust for dividends or reverse adjustment
> # For example here I want the reverse existing split adjustment
 
> # First get splits
> splits <- getSplits("MSFT")
> head(splits)
            MSFT.spl
1987-09-21 0.5000000
1990-04-16 0.5000000
1991-06-27 0.6666667
1992-06-15 0.6666667
1994-05-23 0.5000000
1996-12-09 0.5000000

> # Then get split ratio
> splRatio <- adjRatios(splits = splits, close = MSFT_yhoo.xts[,4])

> head(splRatio)
                 Split Div
1986-03-13 0.003472222   1
1986-03-14 0.003472222   1
1986-03-17 0.003472222   1
1986-03-18 0.003472222   1
1986-03-19 0.003472222   1
1986-03-20 0.003472222   1

> # undjust splits
> MSFT.unadjusted <- cbind(Op(MSFT_yhoo.xts)/splRatio$Split,
+                          Hi(MSFT_yhoo.xts)/splRatio$Split,
+                          Lo(MSFT_yhoo.xts)/splRatio$Split,
+                          MSFT_yhoo.xts[,c(4,6)]
+                          )

> head(MSFT.unadjusted)
               Open     High      Low Close     Volume
1986-03-13 25.50010 29.25014 25.50010 28.00 1031788800
1986-03-14 27.99994 29.50013 27.99994 29.00  308160000
1986-03-17 28.99987 29.75011 28.99987 29.50  133171200
1986-03-18 29.50013 29.75011 28.49990 28.75   67766400
1986-03-19 28.74989 28.99987 27.99994 28.25   47894400
1986-03-20 28.24992 28.24992 27.24998 27.50   58435200

> # Compare to MSFT from db  (as it used to be on yahoo previously)
> head(MSFT_db)
        Date    dOpen    dHigh     dLow   dClose    dVolume
1 1986-03-13 25.49952 29.24928 25.49952 27.99936 1031788800
2 1986-03-14 27.99936 29.49984 27.99936 28.99872  308160000
3 1986-03-17 28.99872 29.75040 28.99872 29.49984  133171200
4 1986-03-18 29.49984 29.75040 28.50048 28.75104   67766400
5 1986-03-19 28.75104 28.99872 27.99936 28.24992   47894400
6 1986-03-20 28.24992 28.24992 27.25056 27.50112   58435200
> 
> # Minor rounding differences

Also it might be convenient to change adjustOHLC by

1- Adding an option to select which columns to adjust, something like which.OHLC = character:

which.OHLC = "OHLC" # character containing any combination of OHLC

Open <- if(grepl("O", which.OHLC)) {
  Op(x)*ratio
} else {Op(x)}

High <- if(grepl("H", which.OHLC)) {
  Hi(x)*ratio
} else {Hi(x)}

Low <- if(grepl("L", which.OHLC)) {
  Lo(x)*ratio
} else {Lo(x)}

Close <- if(grepl("C", which.OHLC)) {
  Cl(x)*ratio
} else {Cl(x)}

Adjusted <-
  cbind(Open,
        High,
        Low,
        Close,
        if(has.Vo(x)) Vo(x) else NULL,
       if(has.Ad(x)) Ad(x) else NULL
)

2- Maybe give an option to reverse adjustment, for example reverse.adj = Logical:

ratio <-
if (reverse.adj) {1/ ratio} else {ratio}
@the-tourist-

This comment has been minimized.

Show comment
Hide comment
@the-tourist-

the-tourist- Jul 19, 2017

I'm just mentioning this as a passing comment, I'm not close to a computer so can't cut and paste or do anything reproducible. I will try to reply tomorrow, if necessary, with something more detailed. But anyway, getSymbols.yahoo now seems to be returning adjusted prices for all OHLC. For instances for getSymbols(c('VXX', 'UAA')), the Close and Adj.Close are the same for all of their history, despite them having multiple splits.

Based on the above comment I'm not sure if this involves a breaking change in how getSymbols.yahoo works, or more likely just another change in how Yahoo! Finance works, since it also seems to be adjusting OHLC.

Aug 09, 2016 36.71 37.20 35.88 36.56 36.56 13,481,100
Aug 09, 2016 1/4 Stock Split
Aug 08, 2016 37.60 37.80 37.20 37.20 37.20 46,298,800

Sorry for the formatting, I'm writing this from an iPhone.

the-tourist- commented Jul 19, 2017

I'm just mentioning this as a passing comment, I'm not close to a computer so can't cut and paste or do anything reproducible. I will try to reply tomorrow, if necessary, with something more detailed. But anyway, getSymbols.yahoo now seems to be returning adjusted prices for all OHLC. For instances for getSymbols(c('VXX', 'UAA')), the Close and Adj.Close are the same for all of their history, despite them having multiple splits.

Based on the above comment I'm not sure if this involves a breaking change in how getSymbols.yahoo works, or more likely just another change in how Yahoo! Finance works, since it also seems to be adjusting OHLC.

Aug 09, 2016 36.71 37.20 35.88 36.56 36.56 13,481,100
Aug 09, 2016 1/4 Stock Split
Aug 08, 2016 37.60 37.80 37.20 37.20 37.20 46,298,800

Sorry for the formatting, I'm writing this from an iPhone.

@jmrichardson

This comment has been minimized.

Show comment
Hide comment
@jmrichardson

jmrichardson Jul 31, 2017

Hi, Is there a workaround for this using Yahoo or estimate on when it may be fixed? Thanks for the great package!

jmrichardson commented Jul 31, 2017

Hi, Is there a workaround for this using Yahoo or estimate on when it may be fixed? Thanks for the great package!

@FatFeng

This comment has been minimized.

Show comment
Hide comment
@FatFeng

FatFeng Aug 25, 2017

Agree with @selgamal , or at least, would be great if getSymbols can have one more option to return Yahoo data as it is.

For the moment I am doing a simple copy of getSymbols.yahoo and remove the following lines:

    if (adjust) {
      fr[, 4] <- Ad(fr)
    }
    else {
      fr[, 1:3] <- round(fr[, 1:3] * drop(fr[, 4]/fr[,6]), 3)
    }

It works but it's just a very naive temporary solution.

FatFeng commented Aug 25, 2017

Agree with @selgamal , or at least, would be great if getSymbols can have one more option to return Yahoo data as it is.

For the moment I am doing a simple copy of getSymbols.yahoo and remove the following lines:

    if (adjust) {
      fr[, 4] <- Ad(fr)
    }
    else {
      fr[, 1:3] <- round(fr[, 1:3] * drop(fr[, 4]/fr[,6]), 3)
    }

It works but it's just a very naive temporary solution.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment