Issue executing Daily Backtest on 5m Data base Interval

choco · August 15, 2021, 10:35am

Hi

I am currently working on Backtest Portfolio with Amibroker 6.30.5 64bit.
I run Backtest with Daily Periodicity on 5m base interval data.

However, the result list show unexpected information. Since I run it with Daily Periodicity, I was expecting that the signal evaluation will be done daily. But the result list show signal evaluation per 5m, as can be seen in the picture below. Am I expecting the wrong behavior, or I miss something here?

Does any one have any clue about this issue? Or If there are anyone can pointing me to the right direction on knowledge base or else, I will appreciate that.

Thank you

Tomasz · August 15, 2021, 10:37am

You have to ALWAYS tell WHAT DATA SOURCE you are using. Please follow this advice: How to ask a good question

Without crucial information about data vendor I can only say, you have to:

Go to File->Database Settings and make sure that 5-minute is selected for "Base time interval")
After you change Periodicity to "DAILY" in Analysis settings, you have to RE-RUN BACKTEST, so new periodicity is actually used.

choco · August 16, 2021, 1:34am

Hi @Tomasz ,Thanks for the response

Responding to your points:

I keep the Base time interval as is (5 minutes without modification)
every time I playaround with the periodicity, I did re-run the backtest. I wont raise this issue if I did not test it right after modification.

Here are the detail:

Data Source

Intraday setting:

Analysis setting:

The result after run the backtest with Daily periodicity

I tried Hourly periodicity (I wont show the screenshot, the modification is just the periodicity), the result is as expected:

I run on 30 minute periodicity, it also worked as expected:

When I tried run on Weekly periodicity, the issue happen too:

Here is part of the afl code associated to setting modification. I am not sure it is relevant or not

// 1 lot = 100 shares
RoundLotSize = 100;

SetOption("InitialEquity", InitEquity);
SetOption("AllowPositionShrinking", True);
SetOption("AllowSameBarExit", True );
SetOption("MaxOpenPositions", MaxPos);
SetOption("WorstRankHeld", MaxPos );
SetOption("HoldMinBars", 1);
SetOption("MinShares", 100);
SetOption("CommissionMode", 1); 	//  percent 
SetOption("CommissionAmount", 0.2); //

If you need anything else to make it easier to analyze the issue, please let me know.

Thank you

Tomasz · August 16, 2021, 7:54am

You did not say what I asked for: what is the DATA SOURCE?
From WHERE did you get data from. This obviously is data error and knowing data source is crucial.
Guessing from names of the path, you might have imported Metastock data from unknown source (again you did not say).
Looks like the data were imported incorrectly. Instead of importing Metastock data use the MS plugin as instructed here
http://www.amibroker.com/guide/h_extsources.html

choco · August 16, 2021, 8:07am

Hi Tomasz,

I am importing data manually using Import ASCII, which contain common field (OHCL and Volume, and OpenInt).

Here is the chart, 5minutes

and daily

Here is my format file:

# Format definition file generated automatically
# by AmiBroker's ASCII Import Wizard
$FORMAT Ticker, Date_MDY, Time, Open, High, Low, Close, Volume, OpenInt
$SKIPLINES 1
$SEPARATOR ", "
$CONT 1
$GROUP 255
$AUTOADD 1
$DEBUG 1
$ALLOWNEG 1

How can I know if my data is error or not? based on the chart visual above, what do you think?

Regarding metastock, I am not using that data. I've got the data from local provider here, and imported using above format. Do you think the format that I used incorrect?

What other information do you think you will need to be able to help me on this issue?

Thank you

choco · August 16, 2021, 8:27am

@Tomasz,

I tried to include the raw data quote as example, but cannot do it. here is a temporary link for the data (max 2 hours) https://tmpfiles.org/dl/94205/data-example.zip

Hope this help

Thank you

Tomasz · August 16, 2021, 8:34am

Please send a screenshot from Tools->Preferences, "Intraday" tab.

choco · August 16, 2021, 8:35am

@Tomasz

Here is the ss:

Tomasz · August 16, 2021, 8:36am

And
Symbol->Quote Editor (enlarge it to display ALL columns).

choco · August 16, 2021, 8:38am

@Tomasz

Tomasz · August 16, 2021, 8:47am

I can't reproduce any problem on my end using the data that you supplied - I am getting one bar per day all the time when Periodicity is set to daily.

What you can do however is to run Exploration using this simple code:

Filter = 1;
AddColumn( Close, "Close" );

You should see one bar per day if Periodicity is set to "Daily". If you don't, you can try the following:
In File->Database Settings change "Base time interval" to 1-minute, then press OK, re-run Exploration

Once you got to the point that Exploration gives you one bar per day, Backtest would do the same.

choco · August 16, 2021, 9:02am

@Tomasz

I run the exploration with your code, Daily periodicity and 1 recent days of Range: it worked, only show 1 bar per quote. ( I did not change the base time interval to 1m, since the previous step works)

I run individual backtest, it showed 1-2 evaluation per day

However if I do portofolio backtest, it showed multiple evaluation per day

Tomasz · August 16, 2021, 9:06am

This means that the OTHER symbol (included in portfolio backtest), NOT BMRI, but DIFFERENT one, has BAD data. This is so because portfolio backtest creates bars for EVERY bar present in ANY security under portfolio test. If one symbol included has bad data it will affect entire portfolio backtest.

One thing to watch for:
Your data file is using Date AND Time in ONE field
Such field can NOT be imported as date only field (MDY).

// NO comma between date and time part !
BMRI,1/2/2019 9:05:00 AM,7400.00,7400.00,7350.00,7350.00,456200,-149992496

To import intraday data YOU MUST HAVE separator (comma) between DATE and TIME part.
You should insert COMMA between date and time part of your data file or use 24 hour format without AM/PM and "Comma or space" as separator

Alternatively you can try to use (Comma or space) as separator in Import Wizard, but this may be problematic because you are using time format that has AM/PM so there is ANOTHER space between TIME part and AM/PM string.

So it is recommended to use proper INPUT format, i.e with COMMA between date and time part.

choco · August 16, 2021, 9:17am

@Tomasz

I am sorry, it seems like the data I sent to you different with the original data on the datetime part. the original data already use 24 hours format (https://file.io/msBt18z4dqTQ) (todays data), and the way I import the data exactly the same as what you suggested, using comma or space.

The one I sent to you is from the database I exported to get 2 years of data. the original data is chucking daily, so for easier sharing, I exported from the database.

I run backtest to single quote BMRI, it worked

In this case, it seems like the other quotes in the portofolio may have bad data.

let me check. By the way, thank you for your prompt response

Tomasz · August 16, 2021, 9:27am

You can use Tools->Database Purify

choco · August 16, 2021, 11:02am

Hi @Tomasz would you help pointing me to the user manual about the Database Purify. I went through this AmiBroker 6.20 User's Guide and cannot find the detail for this feature. I want to understand more about each of the setup for purification process.

Thank you

Tomasz · August 16, 2021, 11:11am

It's usage is pretty easy:

enter "good" symbol in the "reference symbol" field, in your case it can be BMRI
press "Analyse"

It will report all unaligned quotes in other symbols.

choco · August 16, 2021, 11:20am

I followed some information from other threads I found. I used index ( in this case COMPOSITE) and checked all the checkbox for all symbols, since I am not sure BMRI is "align". it's half the way (50%) for about an hour. quite long time to process. while the app analyze the quote, I was planning to understand more about each of the option such as:

invalid OHLC relationship. (what it is and how to resolve it if it happen?)
missing quote (is this about missing row data for particular bar in referred symbol)

I think the rest is ok for me.

Thank you

Tomasz · August 16, 2021, 6:24pm

Composite is bad choice because it is likely to have those invalid accumulated invalid bars if you created it from same set of symbols that you run portfolio backtest on.
Use BMRI as I wrote you before.

Also process should be quick - it is generally as long as it takes to read those data files (depends on disk speed). Definitely not hours. Either you have very slow disk or the size of data is gigantic.

choco · August 17, 2021, 1:33am

Composite is bad choice because it is likely to have those invalid accumulated invalid bars if you created it from same set of symbols that you run portfolio backtest on.
Use BMRI as I wrote you before.

Understood, will do this.

Also process should be quick - it is generally as long as it takes to read those data files (depends on disk speed). Definitely not hours. Either you have very slow disk or the size of data is gigantic.

the data is 5 minutes TF for 2 years, yes I dont think it is gigantic in size. The whole process take almost 2 hours for ~900 symbols (total folder size ~450MB). My computer use SSD, and it should be fast enough.

The thing is, after it's done, I export the result into watchlist. The symbols that has issue reported have the same number with the symbol in the database... it seems like every symbol does not aligned well with the reference. since the number is alot, I dont know how to fix it.
Are there any way to export the result into csv, instead of watchlist. exporting to watch list remove the issue detail.

I will do analyze one more time with BMRI

Thanks @Tomasz