Where to Find Average Bid/Ask Spreads of an ETF/Stock?

[Cyrix]

I am building a stock/ETF model in Excel that is re-balanced daily.
I want to include the bid/ask spreads as a cost assumption into the backtest.

Is there any online source where can I find the average Bid/Ask spread of a given ETF/Stock?

My broker is Interactive Brokers, and it only shows the current bid/ask spreads which fluctuate a lot throughout a day. That's why I am looking for some sort of average (such as of the past week or month) as a better assumption for modeling.

Thanks.

 

[Robert Morse]

If you are rebalanced daily, why not use the close of the primary exchange. Unless your volume is very large, it is fair assumption that a MOC order would have received that exact price. If your AUM is a large number and you would have rebalanced throughout the day, use the VWAP for that day.

Just a suggestion that might make it easier to back test and moving forward for execution.

 

[Metamega]

For rebalancing daily maybe simulating balancing on the close or the open of the next day.

To do what you ask you'd need Tick data with bid and ask so you could average the difference. Don't think you'd get much out of it.

Best way would backrest with close or open then live test and compare your backrest costs with real costs

 

[DaveV]

I agree with RM that simply using the VWAP is easier, and, btw Interactive Brokers does provide the VWAP in their historical EOD data.

If you simply must have the bid/ask spread, here are two suggestions:
1) If you are using Interactive Brokers API to fetch historical EOD quotes, in the reqHistoricalData call set HistoricalDataType to Bid, then run again using HistoricalDataType Ask.
2) Go to the sponsor's web page for the ETF. Frequently there is a section or tab called "Overview" that will list closing bid/ask. Some ETF sponsors, even have the history of several days of closing bid/ask, or the difference between the closing price and the Net Asset Value.