Calendar Events with Database Schemas

Calendar Events with Database Schemas

Introduction

In our last article, on these series on category theory, we looked at how order theory can team up with category theory, examined how concepts from this union can be implemented in MQL5, and also considered a trading system case study that uses some of these concepts.

Our last article’s focus was on two order-theory concepts namely partial orders and linear orders. Partial orders, to recap, are set ranking methods that are a focused type of pre orders with antisymmetry. This means they also have reflexivity and transitivity. Linear orders on the other hand are a more focused form of partial orders by additionally requiring comparability meaning undefined relations are not allowed.

For this article we will take a breather from introducing new concepts and take a step back to review some of what has been covered so far with a goal integrating them into language classifier that employs database schemas.

The Need for Effective Classification of Calendar Events

Calendar events are spawned almost daily, with most of them being pre-marked months in advance. Sourced via the MetaTrader Economic Calendar, they highlight currency and macroeconomic indicators for China, US, Japan, Germany, EU, UK, South Korea, Singapore, Switzerland, Canada, New Zealand, Australia, and Brazil. The list seems dynamic so more countries could get added in the future. These indicators are formatted often, but not always with numeric values that primarily feature a forecast value and an actual value and a previous value. Note that I mention ‘formatted often’ this is because not all indicators have numeric values, or even among those that do the number and format of the actual numbers does vary quite a bit. Put differently there is a lot of incomparability and this in a sense presents our problem statement.

In order to use the indicator readings for these currencies and economies, traders need to reliably and consistently be able to read the numeric values and, or accurately interpret their posted text. Let’s illustrate this by looking at a few typical calendar events.

Above we have four events for China, US, Japan and Germany that capture an index, percentage yields, monetary amounts, and an undefined value, respectively. This information can be extracted in MQL5 via simple methods as shown below.

//+------------------------------------------------------------------+ //|                                                                  | //+------------------------------------------------------------------+ void SampleRetrieval(string Currency)
   {
      MqlCalendarValue _value[];
      datetime _stop_date=datetime(__start_date+int(PeriodSeconds(__stop_date_increment))); //--- get events       MqlCalendarEvent _event[];
      int _events=CalendarEventByCurrency(Currency,_event);
      printf(__FUNCSIG__+" for Currency: "+Currency+" events are: "+IntegerToString(_events));
      //       for(int e=0;e<_events;e++)
      {
         int _values=CalendarValueHistoryByEvent(_event[e].id, _value, __start_date, _stop_date);
         //          for(int v=0;v<_values;v++)
         {
            //             printf(__FUNCSIG__+" Calendar Event code: "+_event[e].event_code+", for value: "+TimeToString(_value[v].period)+" on: "+TimeToString(_value[v].time)+", has... ");
            //             if(_value[v].HasPreviousValue())
            {
               printf(__FUNCSIG__+" Previous value: "+DoubleToString(_value[v].GetPreviousValue()));
            }
            
            if(_value[v].HasForecastValue())
            {
               printf(__FUNCSIG__+" Forecast value: "+DoubleToString(_value[v].GetForecastValue()));
            }
            
            if(_value[v].HasActualValue())
            {
               printf(__FUNCSIG__+" Actual value: "+DoubleToString(_value[v].GetActualValue()));
            }
         }
      }
   }

The problem after extraction arises with how to organize and sort the extracted value for use in analysis. If our numeric values were all standard to say between a range of 0-100 like an index then relative comparison among the calendar events would be straight forward as that might easily guide towards the relative importance of each event. The analysis though now needs to be done via a ‘third party’ such as correlations between each individual event and price movements of a particular stock or currency.

Add to that, some events have no comparable numeric value such as speech by Bundesbank executive board member shown above for Germany.

But even more pertinent is text description of the event itself which is supposed to be the way a trader identifies the event. When for instance analyzing a forex pair like EURUSD, you would need to consider EUR events with comparable USD events ideally. But how would you square off events like these:

With their seemingly comparable counterparts on the USD side of:

And:

When making a selection for the EUR do we use Euro sentiment or Germany’s sentiment? Or do we use both with weighting? If weighted what weights do we use? On the USD side which of the Michigan or Philadelphia values do we use?

We therefore need an extra way of classifying our events, besides those already provided by MQL5, that not only helps us easily compare numeric values across economies and currencies, but also specifically for the purpose of executing trades.

The existing classification methods are quite rudimentary. They include: selecting events by id, selecting events by country, and selecting events by currency. There are few other categorizations that consider the values of these events but they do not vary by much from these classifications. Also, the selection by country and by currency does create ambiguity, thanks in large part to the EUR, which is not helpful. Basic aspects of whether the event is backward looking like an index or forward looking like a sentiment, are lacking. And also, the need to compare across currencies, for the same event, is not as clearly defined in some cases as shown above with the EURUSD pair.

Category Theory Concepts and Database Schemas

To quickly recap what we have covered with category theory so far, we started by looking at elements the basic unit of a set (set was referred to as a domain in earlier articles), then looked at sets, then morphisms with their types, and finally composition with the many properties and forms it presents.

Images that are an approximation of graphs, and capture the conceptual layout to which the data conforms, without being prematurely concerned with the individual data that populates the tables are what one could refer to as database schemas. Databases as is established, are indexed storage tools that enforce referential integrity and avoid Data duplicity.

The potential synergy between category theory and database schemas lies in the fact category theory compositions can lend some of their properties to database schemas. So, if we start by classifying our calendar events with a simple database, we can easily look at the schema through different ‘lenses’ whether they be pull-backs, or graphs, or orders, and many more. Here is a diagrammatic representation of the tables that could define our calendar events.

With this basic layout, the events table will have its date and code column both serve as the primary key. Recall the code column the code column holds event code data as read from calendar events. Currencies, Countries, and Events type tables can easily have their primary keys as currencies_id, country_id and event_id columns respectively. The currency pair values table though will need to combine the date and event_id columns for its primary key.

The above is not a schema since no connections between the tables are indicated, it simply shows the tables within our database. We can however have a schema, part of which is shown below.

This schematic arrangement can easily be viewed as a category theory product. What this means is we have a universal property between our events table and the currency pair values view.

To recap, typically the coproduct universal property is useful in handling piecewise curves. Take for instance an experiment where the recorded temperature in two regions A and B varies linearly and quadratically respectively. In order to study the temperature profile of the combined region A u B coproduct universal property would allow individual temperature profiles of each region to be glued into one function that based on the curve data, would be a reasonable proxy for what to expect if someone were to decide to travel to that region without a clearly defined itinerary.

For our purposes as traders, the above composition is quite useful because the event values of each individual currency are never released at the same time. So, if sentiment numbers on the EUR are released today for example, those of USD could come out in a fortnight. We could use old USD (the latest) values to come up with the currency pair value, but with category theory we can actually use the universal property to anticipate or forecast the value for the currency that is not updated since the diagram commutes.

Implementing in MQL5

Since our category theory and schemas can be represented in MQL5 as a square commute we can slightly modify the class ‘CSquareCommute’, from previous articles, as follows:

//+------------------------------------------------------------------+ //| Square Commute Class to illustrate Universal Property            | //+------------------------------------------------------------------+ template <typename TA,typename TB,typename TC,typename TD> class CCommuteSquare
   {
      public:
      
      CHomomorphism<TA,TB>          ab;
      CHomomorphism<TA,TC>          ac;
      CHomomorphism<TD,TB>          db;
      CHomomorphism<TD,TC>          dc;
      
      CHomomorphism<TD,TA>          da;   //universal property       
      virtual void                  SquareAssert()
                                    {
                                       ab.domain=ac.domain;
                                       ab.codomain=db.codomain;
                                       dc.domain=db.domain;
                                       dc.codomain=ac.codomain;
                                       
                                       da.domain=db.domain;
                                       da.codomain=ac.domain;
                                    }
      
                                    CCommuteSquare(){};
                                    ~CCommuteSquare(){};
   };

What we have added to the original is simply an extra homomorphism for the universal property. So, with this defined the next key thing would be how to define elements for each set that capture respective data. This could be done, for the events set (shown as events table) as listed below:

//sample constructor for event set CElement<string> _e_event;_e_event.Cardinality(7); //

Once this element is defined the trader can easily populate it with data using listing-1 shared above or any other appropriate option. I have not gone into the labor of demonstrating that here as am confident the reader can better come up with a method more suited to his strategy. Once an element is filled with data it can easily be added to the respective domain of the commute class shown above, as we’ve covered in previous articles. The event values and even currency set elements can also be constructed as shown below:

//sample constructor for type set CDomain<string> _d_type;_d_type.Cardinality(_types); //data population CElement<string> _e_type;_e_type.Cardinality(1);

 //sample constructor for currency set CDomain<string> _d_currency;_d_currency.Cardinality(_currencies); //data population CElement<string> _e_currency;_e_currency.Cardinality(1);

This then leads to the currency pair value elements. This set pairs currencies into a common traded pair, that has a price chart when selected from market watch, with the addition of a new numeric value that is the effective value of the pair from combining the two event values of each currency. So, for instance if we have retail sales values for the euro area which would map to the EUR currency, and retail sales for the US which map to USD, then the currency pair values set would list the EURUSD pair with its effective retail sales number. The listing for constructing its element is shown below:

//sample constructor for values set CDomain<string> _d_values;_d_values.Cardinality(_values); //data population CElement<string> _e_values;_e_values.Cardinality(4);

With that said one may ask the question why is the currency pair view (which in MQL5 listing above is the currency pair values) significant? Well it unifies event values of two currencies as reported in the events calendar into a single value for the currency pair of these two currencies. This single value could, for example, then form a time series which presents opportunities for further study with say the price time series of the pair or any other indicator series of this pair.

To sort of recap what we have covered so far, the steps involved in designing and implementing this classification are firstly sorting the raw calendar events into a database schema. This identifies repetitive text and allows the use of indexes. A simple schema that has the events table linking to all other tables could be used for this design.

With this design we would then iterate through the calendar events which can be easily extracted as shown in our first listing-1 above, and populate the values in our database. Not to reinvent the wheel but the class for our database, with its attendant tables represented as structs, could look as follows:

//+------------------------------------------------------------------+ //|                                                                  | //+------------------------------------------------------------------+ class CDatabase
  {
      public:
      
      STableEvents               events;
      STableEventTypes           event_types;
      STableCountries            countries;
      STableCurrencies           currncies;
      
      
                                 CDatabase(void);
                                 ~CDatabase(void);
  };

How this differs from SQL is the data gets stored in RAM which is temporary, while SQL as is typically the case stores data on the computer’s storage space (hard drive). This class though does allow us to export it to an existing database and since MQL5 IDE has some database handling capability, you could eventually read these values from a physical database, rather than RAM in order to save on compute resources.

Once we have a database we would then construct our square commute from the class listed above. This process simply involves defining the corner sets as shown in the diagram below. For each set we define its element structure and the listing for this has already been shared above. Once the elements are defined, they are filled with data from our database, and then added to an instance of the square commute class.

Once we have our sets we then get to the fun stuff of defining the homomorphisms between these sets. The homomorphism from events set to event types set would simply map events to their type which from a database design point means we could only have a column of types’ index in events table with the actual types with their indices would be in the event types table and a foreign key relationship between the two would be equivalent to our homomorphism. Since this is not a database, in the types set we simply have all the types already listed as part of the event set but without repetition meaning our event set is the domain and event types is the codomain. The homomorphism could therefore easily be defined using the listing below:

      //ab homomorphisms       CHomomorphism<string,string> _ab;
      
      CElement<string> _e;
      for(int s=0;s<_sc.ab.domain.Cardinality();s++)
      {
         _e.Let();
         if(_sc.ab.domain.Get(s,_e))
         {
            string _s="";
            if(_e.Get(0,_s))
            {
               CMorphism<string,string> _m;
               _m.Morph(_sc.ab.domain,_sc.ab.codomain,s,EventType(_s));
               
               _ab.Morphisms(_ab.Morphisms()+1);
               _ab.Set(_ab.Morphisms()-1,_m);
            }
         }
      }

Likewise, the homomorphism from events to currencies is a straight forward mapping that can be realized with the listing below:

      //ac homomorphisms       CHomomorphism<string,string> _ac;
      
      for(int s=0;s<_sc.ac.domain.Cardinality();s++)
      {
         _e.Let();
         if(_sc.ac.domain.Get(s,_e))
         {
            string _s="";
            if(_e.Get(1,_s))
            {
               CMorphism<string,string> _m;
               int _c=EventCurrency(_s);
               if(_c!=-1)
               {
                  _m.Morph(_sc.ac.domain,_sc.ac.codomain,s,_c);
                  
                  _ac.Morphisms(_ac.Morphisms()+1);
                  _ac.Set(_ac.Morphisms()-1,_m);
               }
            }
         }
      }

The crux though is the remaining three homomorphisms namely the mapping to event types set from the currency pair values set, the mapping to currencies set from the currency pair values set, and finally the universal property mapping to the currency pair values back from the events set. If we unpack this starting with the first two that are relatively simple, we have a mapping from currency pair values to event types and also a mapping from currency pair values to currencies, which makes our composition a product. It is worth noting that as per foundational rules of homomorphisms, an element in the domain can only map to one element in the codomain. So, this implies when looking at multiple currencies, we cannot have a mapping in the reverse direction as that would lead to event values mapping to multiple pairs whenever a pair references its value and likewise for the currencies a mapping from there to the currency pair values set would face a similar repetition problem. Thus, the implementation of this for the mapping to event values could look as follows:

      //db homomorphisms       CHomomorphism<string,string> _db;
      
      for(int s=0;s<_values;s++)
      {
         _e.Let();
         if(_sc.db.domain.Get(s,_e))
         {
            string _s="";
            if(_e.Get(3,_s))
            {
               int _t=TypeToInt(_s);
               CMorphism<string,string> _m;
               //                _m.Morph(_sc.db.domain,_sc.db.codomain,s,_t);
               
               _db.Morphisms(_db.Morphisms()+1);
               _db.Set(_db.Morphisms()-1,_m);
            }
         }
      }

Likewise, the mapping to currencies could look as below:

      //dc homomorphisms       CHomomorphism<string,string> _dc;
      
      for(int s=0;s<_values;s++)
      {
         _e.Let();
         if(_sc.dc.domain.Get(s,_e))
         {
            string _s="";
            if(_e.Get(0,_s))//morphisms for margin currency only             {
               int _c=EventCurrency(_s);
               
               CMorphism<string,string> _m;
               //                _m.Morph(_sc.dc.domain,_sc.dc.codomain,s,_c);
               
               _dc.Morphisms(_dc.Morphisms()+1);
               _dc.Set(_dc.Morphisms()-1,_m);
            }
         }
      }

What is noteworthy here are the weighting parameters for the bid and margin currencies. These could be attained from optimization or the relative weighting of each economy’s benchmark interest rates, or their inflation rates (this list is not exhaustive). The trader would have to make a choice based on his strategy and market outlook. The final homomorphism to currency pair values back from events would map to a single element in the currency pair values to two entries in the events set. Based on the weighting used for the two mappings above, the universal property mapping will be listed as shown below:

      //da homomorphisms       CHomomorphism<string,string> _da;
      
      for(int s=0;s<_values;s++)
      {
         _e.Let();
         if(_sc.da.domain.Get(s,_e))
         {
            string _s_c="",_s_t="";
            if(_e.Get(0,_s_c) && _e.Get(3,_s_t))// for margin currency             {
               for(int ss=0;ss<_sc.ac.domain.Cardinality();ss++)
               {
                  CElement<string> _ee;
                  if(_sc.da.codomain.Get(ss,_ee))
                  {
                     string _ss_c="",_ss_t="";
                     if(_ee.Get(1,_ss_c) && _ee.Get(6,_ss_t))// for margin currency                      {
                        if(_ss_c==_s_c && _ss_t==_s_t)
                        {
                           CMorphism<string,string> _m;
                           //                            _m.Morph(_sc.da.domain,_sc.da.codomain,s,ss);
                           
                           _da.Morphisms(_da.Morphisms()+1);
                           _da.Set(_da.Morphisms()-1,_m);
                           
                           _sc.da=_da; _sc.SquareAssert();
                           
                           break;
                        }
                     }
                  }
               }
            }
         }
      }
      
      _da.domain=_sc.da.domain;
      _da.codomain=_sc.da.codomain;
      _sc.da=_da; _sc.SquareAssert();

This therefore would mark the final step in generating effective weights to a currency pair based on calendar events of individual currencies.

Classifying Calendar Events

In coming up with the table of event types it may be prudent to follow a disciplined methodology that considers a critical mass of data before arriving at the event types. Classification of events is important as was stated in the problem statement therefore a possible methodology in classifying these events into comparable types would involve: data-collection a basic extraction of data using MQL5 inbuilt classes was shared above; event-grouping would then follow where we could use standard groups like indices, sentiment readings, treasury yields, inflation readings etc.; feature extraction would then follow this as each event would be parsed for key words to determine which group it best belongs to; model training and evaluation of our feature classification would then follow where we create training and testing data sets and start by training the model; testing of the model on test data set would follow to see how well it classifies our events; and finally post analysis and iterative improvement would conclude this by looking at ways our model can be fine-tuned without over fitting.

Once our event types are created we would then populate these in our ‘event_types’ table shown in the diagram above. And this would imply the event type id column in the events table would be updated for all events so as to assign their group. A stored procedure that inserts new rows or updates rows can guide in implementing our model above on this.

This addition to the events’ set, since the element data type is a string array with each array index catering to a data column, would mean the only significant change to our composition above is in the homomorphism from events to event values. Rather than including only values for which description text across currencies is identical like ‘retail sales’, we would now accommodate a wider scope of events.

Informing Trade Decisions

So, the creation of currency pair values domain from our composition(s) above does imply we have currency pair values with a timestamp. This time stamp allows us compare the magnitude (and in some cases direction) of these values to eventual price changes. A careful analysis process involving training and testing data sets can look to see how each event type is correlated with eventual price action, and by how much.

By using this data on event value correlation to subsequent price action we can not only set rules for placing trades whether to go long or short based on the results from analysis, but we can set position sizing as well based on the magnitude of the correlation.

The accuracy of a system that uses weighted currency pair values to tag eventual price action could be improved if multiple events are combined in a weighted average and this ‘indicator’ is then correlated to eventual price action. This does pose the question which weights get applied to which event. This answer could be answered by optimization or the trader’s own understanding of macroeconomics could guide this process. Whichever method is chosen this more wholistic approach is bound to yield more accurate forecasts.

Case Study: Implementation and Evaluation in MQL5

For brevity this will not be a complete trade system but only the initial parts of it that consider our composition that comprises of four sets: events, type, currencies and values as already mentioned. We will retrieve calendar event data, directly and not with our database class, populate an instance of the square commute class, and read off what homomorphisms we are able to generate. Again, this is only an initial step that is purely meant to demonstrate potential and to that end our inputs will mainly be three namely the type of event we will focus on, the weighting for the bid/ margin currency and the weighting for the ask/ profit currency. As discussed, these weights are for combining the calendar values of two currencies into one. In generating reads for this study, we consider only PMI related events, and look at only the currencies EUR, GBP, USD, CHF, and JPY; and values only for the pairs EURUSD, GBPUSD, USDCHF, and USDJPY. All code is attached at end of the article. If we run prints for the universal property homomorphism, we should get these logs below:

2023.07.11 13:51:52.966 ct_13 (GBPUSD.i,H1) void OnStart() d to a homomorphisms are... 2023.07.11 13:51:52.966 ct_13 (GBPUSD.i,H1) 2023.07.11 13:51:52.966 ct_13 (GBPUSD.i,H1) {(EUR,USD,45.85000000,TYPE_PMI),(GBP,USD,47.00000000,TYPE_PMI),(USD,CHF,45.05000000,TYPE_PMI),(USD,JPY,48.75000000,TYPE_PMI)} 2023.07.11 13:51:52.966 ct_13 (GBPUSD.i,H1) | 2023.07.11 13:51:52.966 ct_13 (GBPUSD.i,H1) (EUR,USD,45.85000000,TYPE_PMI)|----->(markit-manufacturing-pmi,EUR,44.60000000,44.60000000,44.80000000,2023.06.01 11:00,TYPE_PMI) 2023.07.11 13:51:52.966 ct_13 (GBPUSD.i,H1) | 2023.07.11 13:51:52.966 ct_13 (GBPUSD.i,H1) {(markit-manufacturing-pmi,EUR,44.60000000,44.60000000,44.80000000,2023.06.01 11:00,TYPE_PMI),(markit-manufacturing-pmi,EUR,44.80000000,44.20000000,43.60000000,2023.06.23 11:00,TYPE_PMI),(markit-services-pmi,EUR,55.90000000,55.90000000,55.10000000,2023.06.05 11:00,TYPE_PMI),(markit-services-pmi,EUR,55.10000000,55.50000000,52.40000000,2023.06.23 11:00,TYPE_PMI),(markit-composite-pmi,EUR,53.30000000,53.30000000,52.80000000,2023.06.05 11:00,TYPE_PMI),(markit-composite-pmi,EUR,52.80000000,53.00000000,50.300000 

With our inputs of only PMI events and the above preselected currencies and pairs we arrive at only one morphism, for the margin currency which in this case happens to be EUR. Our combined value was higher than the EUR input value simply because the equivalent PMI number for the USD was higher and the printed value for EURUSD pair was simply the weighted average. For this particular test equal weighting was used for both EUR and USD.

Conclusion

I have not shared a case study to show how this classification could be applied in a trading system as the article would have been too long, but I believe there is sufficient code and material in this article to allow one to have his own implementation. To recap, we’ve looked at how category theory and database schemas can team up and help not just classify calendar events, but can aid in defining compositions like products with universal properties that are instrumental in quantifying the impact of the calendar events on price action.

The benefits of this beyond the standard classification of the events to allow easy pairing, when currency pair values, is the use of the universal property axiom of category theory which helps define a homomorphism that could be mapped directly from the events set to currency pair values set (without using the corner sets of event values or currencies). This as mentioned allows forecasting the currency pair value in the event that only one of the currency’s event value is new and the other is still pending by days or weeks.