0
$\begingroup$

This is a bit of a naive question, but I figured it couldn't hurt to ask. I have time series data that looks like:

PHP HIGH DATE PHP HIGH  PHP.OPENDATE PHP OPEN  PHP.CLOSEDATE PHP.CLOSE
8/28/2003     2.935     9/2/2003    2.9855     9/1/2003     2.9865
8/27/2003     2.9503    9/1/2003    2.979      8/29/2003    2.976
8/26/2003     2.9813    8/29/2003   2.957      8/28/2003    2.955
8/25/2003     2.978     8/28/2003   2.9561     8/27/2003    2.9561
8/22/2003     2.984     8/27/2003   2.9748     8/26/2003    2.987
8/21/2003     2.992     8/26/2003   2.9915     8/25/2003    2.9955
8/20/2003     2.9825    8/25/2003   2.988      8/22/2003    2.9865
8/19/2003     2.976     8/22/2003   3.0035     8/21/2003    2.999

The dates are misaligned, and I was wondering what the best way of dealing with this would be. I already have 20MA,50MA downloaded from the source, which adjusts the moving averages to account for these inconsistencies. However, I run into problems if I want to compare last prices to 20-day highs/lows.

I'm thinking of implementing either the two following options:

  1. Should I omit the variables that are misaligned by the dates?

In this case, I would omit the observed values for 9/2,9/1,29 for PHP open prices and 8/29 for PHP Close.

  1. Should I fill in the missing values with the previous value?
$\endgroup$
  • $\begingroup$ You should be able to make it all uniform in under 5 minutes by using very basic Excel. You should definitely not omit anything but rather fill in missing fields with NULL or some other value until you find the missing data. Side question: does your data come like this from the source? If so, look for an alternative source. This is terrible, IMO. $\endgroup$ – amdopt Apr 3 '17 at 19:11
  • $\begingroup$ @amdopt Yes, I actually downloaded all the data from the Bloomberg terminal. I'm actually very confused as to why the data is so bad. I always assumed HLCO prices would be consistent with each other. Filling it in with NULL might not work. I'm actually pulling the data from Excel into R, and NULL doesn't play very nicely with R. In essence, it would be the same case as omitting data because adding NULL to anything will result in a 0 in R. $\endgroup$ – Nikitau Apr 3 '17 at 20:32
  • $\begingroup$ In my experiences I have never found a data source that always gave me what I wanted. BB included. $\endgroup$ – amdopt Apr 3 '17 at 20:35
  • $\begingroup$ @amdopt It's good to know to be a little wary of data, regardless of the source. Also, a bit of naive question but would filling in HLCO prices with the previous last value make any sense? I intend on using moving average crossovers and price breakouts, so I'm thinking if prices are repeated to fill in the gaps then it shouldn't pose a problem. $\endgroup$ – Nikitau Apr 3 '17 at 20:40
  • $\begingroup$ Personally, I would not. I would assign a value that my program knows to ignore. If NULL doesn't work for you than maybe a value that a price should never be (i.e. a negative number). I always want my databases to be a perfect as possible. What if you want to use that data for something else in the future? You don't want incorrect values that you put there purposely. Just my opinion. $\endgroup$ – amdopt Apr 3 '17 at 20:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.