Different prices in the data sets

Report a problem or error
1

Marco,

There is an error with some of the open prices in the Compustat data set. See this post: http://www.portfolio123.com/mvnforum/viewthread?thread=5370#25365

I checked 6 stocks with AvgDailyTot(60) > 10000 and they all had zeros for the open price for every day the the volume was zero.

Denny :sunglasses:

2

Denny:

Hi. My ETF Sim uses open prices and the Compustat data is clearly different than what we've been using. See my post. Thanks.

Best,

Bill
3

Yes Open prices are a bit problematic. The source for prices, corporate actions, splits, etc for Compustat is InteractiveData. For whatever reason Open prices are only available since 2004, for all stocks,ETFs etc. See IBM 8/26/2004

For now we had to do a work-around pre-2004. An Open price is set to the average of hi & low when it’s 0.

I’m not sure if Compustat is ever going to fill the open prices. But further down the road we can fill them in ourselves. We have a separate agreement with InteractiveData and we did get a complete dump of prices for 20years with open prices. But at the moment it’s in a different database.

Since we have our hands full at the moment, we did not want to complicate the transaction any further by pulling in data from two databases to fill in open prices. Another future enhancement will also include pulling in last bid/ask prices for the day from InteractiveData (compustat does not capture those). This will allow for automatic slippage calculations.

Marco

4

Marco:

I really like the idea of being able to get approximations for bid-ask spreads automatically. Five years ago I did my own survey of bid-ask spreads for stocks of different prices and different average volumes. For that I used InterActive Broker’s API for Excel to download bid-ask spreads for 100 stocks at a time (the maximum the API would support at the time). I cycled through a list of 2,000 liquid stocks (excluding all the penny stocks and all stocks with very low volume) – so 20 batches of 100 stocks. After downloading the 20 batches, I’d start over and do it again and again all day. I repeated the exercice a few days later to average results.

Here was a key finding – for most lower priced lower volume stocks the spread was very wide for the first 10 minutes and then gradually the spread narrowed until about 11:00 AM. Thus the spread for these lower volume stocks was typically was much narrower between 11:30 am and 4:00 pm than between 9:30 and 10:00 AM.

This raises a question about what formula would be used for approximating the spread for all but the highly liquid stocks. For example, will the bid-ask spread take the time of day into account when giving the automatic spread calculation (ie, wider spread for the “open” price and narrower spreads for the “average” price and “closing” price)? Or will it be a one-size fits all spread calculation?

By the way, I am pleased Portolio 123 will be switching to Compustat data in spite of the transitional challenges.

Regards,
Brian

5

Brian we only get final bid/ask. Would that still be better than using one size fits all for slippage? I reckon it should. Thanks for your input.