X++ to run XIRR

I created a small X++ wrapper to call XIRR function from the C# project provided by https://github.com/klearlending/XIRR (Thank you!) 

You can test XIRR function in Excel, btw.

using tmxExcelFinance;
/// <summary>
/// Implements various financial functions
/// </summary>
public final class mgcFinanceFunction
{
    /// <summary>
    /// Calculates XIRR value
    /// </summary>
    /// <param name = "_cashFlows">List of containers kind [cashflow amount, its date]</param>
    /// <param name = "_decimals">how many decimals to calculate</param>
    /// <param name = "_maxRate">Maximum rate</param>
    /// <returns>XIRR value</returns>
    public static real calculateXIRR(List _cashFlows, int _decimals, real _maxRate = 1000000)
    { 
         
        var                                 cashFlows       = new System.Collections.Generic.List<CashFlowDates>();
        System.Collections.IEnumerable      cashFlowsI;
        ListEnumerator                      leC             = _cashFlows.getEnumerator();
        System.Double                       dbl;
        System.DateTime                     dt;

        while(leC.moveNext())
        {
            [dbl, dt] = leC.current();
            cashFlows.Add(new CashFlowDates(dbl, dt));
        }
        // convert to iterable
        cashFlowsI = cashFlows;

        return XIRR::Calc(cashFlowsI, _decimals, _maxRate);
    }

}

This is how we can call the wrapper

 public static void calculateXIRRFromInvestmentDetailsLineTmp(RefRecId _projTableRecId, mgcInvestmentDetailsLineTmp _mgcInvestmentDetailsLineTmp)
    {
        List listCashFlowsDates = new List(Types::Container);

        mgcInvestmentDetailsLineTmp mgcInvestmentDetailsLineTmpLocal;

        mgcInvestmentDetailsLineTmpLocal.linkPhysicalTableInstance(_mgcInvestmentDetailsLineTmp);

        while select mgcInvestmentDetailsLineTmpLocal
        {
            listCashFlowsDates.addEnd([mgcInvestmentDetailsLineTmpLocal.TransactionCurrencyAmount, DateTimeUtil::newDateTime(mgcInvestmentDetailsLineTmpLocal.LineDate, 0)]);
        }
        mgcProjXIRRTable.XIRRValue = mgcFinanceFunction::calculateXIRR(listCashFlowsDates, 4);
...

Or

 cashFlows1 = new List (Types::Container);
        
        cashFlows1.addEnd([-10000,str2Date("01/01/2008", 123)]);
        cashFlows1.addEnd([2750, str2Date("01/03/2008", 123)]);
        cashFlows1.addEnd([4250, str2Date("30/10/2008", 123)]);
        cashFlows1.addEnd([3250, str2Date("15/02/2009", 123)]);
        cashFlows1.addEnd([2750, str2Date("01/04/2009", 123)]);

res = mgcFinanceFunction::calculateXIRR(cashFlows1, 10);

Notes about the solution creation.

D365FO project must be <TargetFrameworkVersion>v4.6</TargetFrameworkVersion>

Last remark. I tried to implemented the same algorithm in X++, but I bumped into two limitations.

First, there is a limit for recursion depth - 400 levels; we can flatten it, though. 

Second, real loses required decimals and ends up with division by zero.

Two outer joined tables in a report without duplicates from both sides

 I cannot find a particular term for this type of join; so I called it 'hanging left outer join'.

Let's state the problem.

We have two tables:

- LedgerJournalTrans (trans) with a field mgcProjId, which is a reference to ProjTable.

- mgcWarrantsDetails (details), which may have 0..N records for certain records from ProjTable.

Examples of data in both may look like the following:

If we apply standard outer join, the output will be as follows

But we need to print all details records by 'hanging' them against the same projId, like this

So, no duplicates from both tables must be present in the merged table.

This is how you can achieve it. 

/// <summary>
    /// Process report data.
    /// </summary>
    public void processReport()
    {
        Query                   query;
        QueryRun                qr;
        mgcWarrantsDetails      details;
        LedgerJournalTrans      trans;
        ProjId                  prevProjId;
        // merge trans and detail even if details are empty
        void insertWithTrans()
        {
            mgcProjWarrantDetailTmp.clear();
            mgcProjWarrantDetailTmp.RefRecId            = trans.RecId;
            mgcProjWarrantDetailTmp.JournalNum          = trans.JournalNum;
            mgcProjWarrantDetailTmp.TransDate           = trans.TransDate;
            mgcProjWarrantDetailTmp.Voucher             = trans.Voucher;
            mgcProjWarrantDetailTmp.ProjId              = trans.mgcProjId;
            mgcProjWarrantDetailTmp.WarrantDetailId     = details.WarrantDetailId;
            mgcProjWarrantDetailTmp.WarrantsWithPrice   = details.WarrantsWithPrice;

            mgcProjWarrantDetailTmp.insert();
        }
        // merge empty trans and detail; it will be 'a hanging' detail
        void insertWarrants()
        {
            mgcProjWarrantDetailTmp.clear();
            mgcProjWarrantDetailTmp.ProjId              = details.ProjId;
            mgcProjWarrantDetailTmp.WarrantDetailId     = details.WarrantDetailId;
            mgcProjWarrantDetailTmp.WarrantsWithPrice   = details.WarrantsWithPrice;

            mgcProjWarrantDetailTmp.insert();
        }
        // process warrants for the previous projId
        // 'hanging' details on the right side of the report
        void processRestOfWarrants()
        {
            // if there are still some warrants we found for the previous projId
            if (details)
            {
                // as we do not have enough rows from trans with the same previous projId,
                // let's add them with empty part from trans side
                next details;
                while(details)
                {
                    insertWarrants();
                    next details;
                }
            }
        }

        // Get the query from the runtime using a dynamic query.
        query = this.parmQuery();
        qr = new QueryRun(query);
        // we suppose that trans sorted by projId
        // each projId may have 0..N of warrants (details)
        while(qr.next())
        {          
            // get next transaction
            trans =  qr.get(tablenum(LedgerJournalTrans));  
            // projId changes 
            if(prevProjId != trans.mgcProjId)
            {
                // if there are still some warrants we found for the previous projId
                processRestOfWarrants();
                // get the first warrant for the new projId 
                select details
                    where details.ProjId == trans.mgcProjId;
                // keep the new as a previous for the next iteration
                prevProjId = trans.mgcProjId;
            }
            // if this is a trans with the same projId, then we can merge it with the next warrant
            else if (details)
            {
                next details;
            }
            // merge trans with warrants
            insertWithTrans();
        }
        // if there are still some warrants we found for the previous projId
        processRestOfWarrants();
    }

Raw outer joined data

Ascending ProjId

Descending ProjId