Importing bid AND offer prices?

Presale / Trial
#1

Hi,
I'm just trialling AMIBroker as I had i recommended to me for backtesting (I have been building my own backtest system but needed to get moving faster!).

I'm trying to import my price data (CSV format) and I manage to import Open, Hight, Low, Close BUT I can only import either my BID prices OR my OFFER prices, not both - I wonder if I'm missing something?

The spread is important to me as it makes a difference to my profit (or loss!).

If anyone can point me towards how to import both prices that would be very helpful - thanks.

Matt :slight_smile:

#2

Should be able to use Aux1 as bid and Aux2 fields for bid/ask.

https://www.amibroker.com/guide/w_impwizard.html

Should be able to import with wizard if it’s a simple .csv file.

https://www.amibroker.com/guide/d_ascii.html
Has some more advanced feautures you likely don’t need.

Probably help if you post a screenshot of your import settings and maybe a line sample of your .csv files .

#3

Thanks for the pointers, below is a sample of my 1-day OHLC data, as you can see, each price (Open, High, Low and Close) has a bid AND ask...

Screenshot%20from%202019-11-23%2014-30-59

prices/snapshotTime instrument prices/snapshotTimeUTC prices/openPrice/bid prices/openPrice/ask prices/closePrice/bid prices/closePrice/ask prices/highPrice/bid prices/highPrice/ask prices/lowPrice/bid prices/lowPrice/ask prices/lastTradedVolume

2018/11/01 00:00:00 FTSE 2018-11-01T00:00:00 7119.5 7123.5 7124.6 7128.6 7166 7167 7073.4 7075.4 130690

2018/11/02 00:00:00 FTSE 2018-11-02T00:00:00 7125.2 7129.2 7108.6 7112.6 7196.2 7197.2 7071.1 7073.1 105632

(sorry about the formatting, struggling to get a table layout)

I presume everyone else using single prices are using the MID price (half way between bid & ask)?

Thanks
Matt

#4

This seems like a weird way to format data. Generally if someone wants bid ask data they have tick data which is every trade on an instrument. Then perhaps the data provider gives the bid/ask data and it’s simple to import it under aux1 and aux2 data.

In Amibroker you have Open, High,Low, Close ,volume, open interest and aux1 and aux2 as time series data.

I have no idea how your approach building a database with your data. I can see if the bid/ask at open and close of a minute bar being perhaps useful. But knowing the bid/ask at the high and low provides no real benefit as that’s hindsight knowledge.

Generally when I’m testing I just assume it’s the slippage bid/ask and compare backtest to live samples and compare.

#5

If you really want to import OHLC for bid and ask then simply make a copy of your data file and name it FTSE_ASK. And the file you copied it from you name FTSE_BID.

Then read the links in post #2 on how to import ASCII file.

So you first import FTSE_BID with columns
Date, Open Bid, High Bid, Low Bid, Close Bid, Volume.
Then you import FTSE_ASK with columns
Date, Open Ask, High Ask, Low Ask, Close Ask, Volume.

If all is imported then you choose symbol FTSE_BID in AmiBroker UI's symbol selection menu and use AFL function SetForeign() to call FTSE ask data if FTSE_BID is selected one.

if (! StrFind(Name(), "_BID")) 
	Error("Select symbol with appendix '_BID'!");

Open_Bid = Open;
Close_Bid = Close;
High_Bid = High;
Lose_Bid = Low;

SetForeign(Name() + "_ASK");
	Open_Ask = Open;
	Close_Ask = Close;
	High_Ask = High;
	Lose_Ask = Low;
RestorePriceArrays();

//... further code below

Other than that if you only need Open,Close then import to AUX1, Aux2 of same symbol name (as mentioned already).

#6

Thanks - perhaps I'm approaching it incorrectly - my base data is ticks, so every trade made - I hadn't realised that ticks could be imported into AMIBroker.

I think the importance for my (any?) strategy is to account for the spread as it impacts my profitability (esp on scalping strategies)

I'll have another look at the import tutorial - so much to learn!

Thanks.

#7

Ticks still have open/high/low/close. Depending on the size of the trade, that tic can take out multiple prices. Generally you’ll see a small lot just have the same OHLC value. Then use aux1 and aux2 for bid/ask at the time of that tick.

#8

Cheers....

here's what my raw tick data looks like, sometimes there's 10+ prices per second, sometimes there's only a few every minute:

Instrument, date, time, bid, offer
IX.D.FTSE.IFM.IP,2018-07-31,23:00:00.389,7757.8,7761.8
IX.D.FTSE.IFM.IP,2018-07-31,23:00:00.561,7756.6,7760.6
IX.D.FTSE.IFM.IP,2018-07-31,23:00:00.639,,7757.2,7761.2
IX.D.FTSE.IFM.IP,2018-07-31,23:00:01.032,,7756.6,7760.6

So it sounds like to get it into AMIBroker I should take say the BID price (column 4), duplicate it to make it OPEN_BID, HIGH_BID, LOW_BID & CLOSE_BID, then put the offer into Aux 1 column (or I could calculate the difference between bid & offer i.e. the spread and but that in Aux 1)?

Then when writing my backtesting code I can make sure I always buy at the OFFER and sell at the BID (for long positions)? That way my profit calculation will always have accounted for the loss due to the spread.

Matt

#9

No, you do not have to duplicate anything inside file.

1-tick is single value. It is not OHLC.
If you import 1-tick data then you only need to import to close field (either bid or ask).
The OHL fields get populated with same 1-tick value per timestamp.
1-tick is single value and so O==H==L==C always.
Then if changing interval in AB OHLC bars (e.g. 1-min, 5-min, 10R, etc) are created out of that base interval 1-tick.

Also you need to enable $TICKMODE 1 of ASCII importer (if you need duplicate timestamp data). So if your file contains duplicate timestamp(s) (rows with same date and time) then duplicate stamps data will be imported to AB.

And FYI... yes, sub-second data is supported by AB too.

So one way is to import bid to Close field, ask data to Aux1 and Volume to Volume field.
Then you can use timeframe functions to compress to longer intervals such as 1-min, 5min, Hourly... (e.g. if trying to exit at profit/loss target levels).

Sample import format in that case:

$FORMAT Ticker, Date_DMY, Time, Close, Aux1
$SKIPLINES 1
$SEPARATOR ","
$CONT 1
$GROUP 255
$AUTOADD 1
$DEBUG 1
$TICKMODE 1
$TIMESHIFT 0

Or you do as I have explained in my previous post of this thread. First import bid data to Symbol_Bid (to close field) then import ask data to Symbol_Ask (to close field). Then use SetForeign(). That way for both bid and ask data OHLC bars are created out of 1-tick. And of course you may still use time frame functions looking from 1-tick level, too.

As aside please read the pinned thread How to ask good question.
You said the whole time (before) that you have EOD data ("my 1-day OHLC data") with bid and ask info for OHLC fields. Also you have shown EOD data (one row per day in your picture). So if you would have explained correctly right at the start (which means taking time to create post) then you would not have made others wasting time with multiple posts. You expect others taking time for you and getting detailed response by others. So please take your time too to create post containing all info at start.

2 Likes
#10

Thanks for the very comprehensive reply - I'll read it tonight to properly understand. (Thanks also for the link to "asking good questions").