NumXL

X13ARIMA-SEATS Wizard Issue With Open Files.

Osama Alarjeh — Fri, 31 Jan 2025 19:27:58 +0000

This issue has arisen in our support desk: a customer using the X13ARIMA-SEATS model reported that the wizard failed to capture the changes after he pressed the “Apply” button, and the X13AS(.) function returned #VALUE!

Background

When a user selects a cell containing an X13ARIMA model in their workbook and launches the X-13ARIMA Wizard, the X13ARIMA-SEATS wizard loads the existing model settings and creates a separate sandbox environment (subfolder) in the user’s data temp directory.

The sandbox environment allows users to experiment with different model settings, verify their consistency, conduct trial runs, and examine raw output files.

By default, NumXL maintains the X13 sandbox model folder and its output files in the user profile folder, specifically at: C:\users\\AppData\Local\NumXL\x13as_tempscenario:

The X-13ARIMA wizard, upon the user’s request, loads any of several files used or generated by the X13 program (e.g., output file, error file, input specification file, etc.) for review using the system editor program (e.g., Notepad).

Once the user completes his experimentation in the X-13ARIMA wizard and wishes to commit those changes to the model referenced in his workbook, he presses the “Apply” button. The Wizard deletes the current sandbox folder and writes the final settings in the model specification cell on your worksheet.

Analysis

Upon further investigation, we found that the user replaced the default system editor (e.g., Notepad) with another program. Furthermore, the user left one or more output files open in that editor when he pressed the “Apply” button.

Unlike the Notepad program, the new editor program locks the opened file for editing, and attempting to clean up the sandbox temporary folder or files triggered an error, interrupting the completion of the X13ARIMA-SEATS wizard.

As a result, the X13ARIMA-SEATS wizard Window closed, but the temporary/sandbox folder was left intact. The specification text was not written into the worksheet, and the X13AS(.) function returned #VALUE!

Resolution

During the X13-ARIMA Wizard session, close all opened output files before you press the “Apply” button.

The post X13ARIMA-SEATS Wizard Issue With Open Files. appeared first on NumXL.

Trusted Publisher in Microsoft Excel

Osama Alarjeh — Mon, 04 Nov 2024 21:20:49 +0000

This blog stemmed from a support case involving an enterprise customer who had difficulties running NumXL add-ins after the MIS/IT department implemented new security policies that restricted application add-ins and macros in Microsoft Excel via the Microsoft Trust Center:

Under the “Add-ins” section, Check or select the” Require Application Add-in to be signed by Trusted Publisher.” Option.

Under the “Macro Settings” section, Select or check the “Disable VBA macros except digitally signed macros” option.

Although Spider Financial adheres to the best security practices and digitally codesigned the NumXL application and its associated macros, the end-user “Trusted Publishers” list did not include the public certificate of our codesigning key. Consequently, Microsoft Excel deactivated the NumXL add-ins and displayed a security warning message regarding the disabled active content.

Resolution I:

The Warning message above includes a button to enable the add-in only for the current session but to make it permanent, you’d need to click on the link in the warning message to take you to “File->Info”:

Now, press on the “Enable Content” button, then select “Advanced Options”:

Now, you can add Spider Financial as a “Trusted Publisher,” and the security warning will not reappear.

The advantages of this approach are that it does not require heightened security permissions, meaning there is no need to contact the system administrator to add Spider Financial as a trusted publisher in the end-user list, unlike the local machine.

IMPORTANT: This approach may not work if the MIS/IT department enforces additional policies that prevent end-users from adding to the trusted publisher’s list or instructs Microsoft Office to disregard the end-user’s “Trusted Publishers” list. In that case, let’s consider the second approach.

Resolution II:

Starting with version 1.69.5, the NumXL installer, using its elevated permission, attempts to add the public certificate to the local machine’s “Trusted Publisher” list. For older NumXL versions or if the installer fails to add the certificate, you should manually add it, as described below.

To add or remove a public certificate from the “Trusted Publishers” list, launch the Microsoft Management Console (MMC) application by typing “mmc.exe” in a command window. The MMC requires elevated security permission, so you must call your administrator to complete this method.

Next, use the menu to press “File” and select “Add/Remove Snap-in.”

Select “certificates” from the list of Snap-ins and press the “Add >” button (between the two panels). Then, select the “Computer Account” for your “local machine.”

Press the OK to complete.

Now, select “Trusted Publishers” in the tree view in the left panel, and with your mouse, right-click in the right-hand panel and select “All Tasks->Import” from the popup menu.

The certificate import wizard appears and asks for the certificate file. The NumXL public certificate file for the code-signing key can be found in the NumXL installation folder (e.g., C:\Program Files\NumXL).

That’s all. The “Trusted Publishers” list should include Spider Financial.

Restart Microsoft Excel.

The post Trusted Publisher in Microsoft Excel appeared first on NumXL.

NumXL V1.69 WOLF is Here!

Osama Alarjeh — Fri, 20 Sep 2024 16:56:06 +0000

We are excited to announce the release of NumXL version 1.69 (WOLF). This new version includes a new licensing mode, numerous bug fixes, offline examples, and PDF documentation.

In this version, the installer copies a complete set of workbook examples and support documentation in PDF format, such as a user’s guide, reference manual, getting started guide, etc.

Furthermore, the installer creates a few new shortcuts to the start menu for program troubleshooting and resetting the NumXL registration with Microsoft Excel.

For enterprise users, the NumXL installer now supports a quiet or silent mode. This mode is recommended when deploying desktop software or program updates to end-users using an automated IT tool or script without generating error or restart messages.

Finally, NumXL introduced a new licensing mode: Free or Lite mode. This mode is for users with expired, invalid, or missing license keys. In this mode, NumXL keeps most of NumXL functions but imposes a limitation on the dataset size of 25.

Please refer to the 1.69 (WOLF) release notes on our help center for a complete list of changes.

The post NumXL V1.69 WOLF is Here! appeared first on NumXL.

The NumXL installer isn’t a Microsoft-verified app

Osama Alarjeh — Wed, 06 Mar 2024 18:33:23 +0000

Have you encountered the error “the app you’re trying to install isn’t a Microsoft-verified app” while installing NumXL?

Why does the error appear?

Windows throws this error if you have configured your system only to install apps from the Microsoft Store. You could have set this permission on purpose, or it might have been set by default.

NumXL software is not yet listed in the Microsoft store, and we are working on making it verified. So, if your Windows apps and features settings restrict the installation of apps from the Windows Store, this error will occur.

How Do I Fix this error?

To fix this error, you need to follow the steps mentioned below:

For Windows 10

Open settings and go to Apps & features.
Change the icon shown with a yellow arrow to “anywhere” instead of “Microsoft only”.

For Windows 11

From the start menu, launch the Windows settings, and select “Apps”. Now, follow those steps:

On the right-hand side panel, locate and select the advanced app settings option.

In the Advanced App settings, change the setting for “choose where to get apps” to “anywhere” instead of “Microsoft store only” as shown in the image below.

The post The NumXL installer isn’t a Microsoft-verified app appeared first on NumXL.

NumXL Code Signing Certificate Expiry

Zaid Marridi — Fri, 11 Aug 2023 15:00:00 +0000

NumXL Code Signing Certificate Expiry

The NumXL Code Signing certificate expired on August 4th, 2023. However, because NumXL timestamps its program and executable signatures, they will remain valid even after the certificate’s expiration. As a result, Windows and Microsoft Office will continue to trust the code indefinitely.

Why should I Care?

You shouldn’t. There is no required change or adverse effect on our end-users for the current software.

What is Code signing?

Code Signing Certificates are used by software developers (e.g., NumXL) to digitally sign applications, drivers, executables, and software programs as a way for end-users to verify that the code they receive has not been altered or compromised by a third party.

What happens next?

NumXL is procuring a new code signing certificate. However, we are experiencing delays due to the latest changes in industry standards that focus on added protection and safeguarding of sensitive private keys during the signing process.

NumXL will use the new code signing certificate for future software releases.

Stay Updated with NumXL

For the latest news, articles, and updates from NumXL, connect with us on:

Thank you for being a valued part of our community!

The post NumXL Code Signing Certificate Expiry appeared first on NumXL.

Single Euro Payments Area (SEPA)

Zaid Marridi — Fri, 04 Aug 2023 15:00:00 +0000

Single Euro Payments Area (SEPA)

Great News! Customers based in seventeen (17) European Union countries may now purchase the NumXL product or a NumXL subscription with the Single Euro Payments Area (SEPA) as a payment method.

What is SEPA? The Single Euro Payments Area is an EU initiative to create a single market for euro-denominated payments.

What is SEPA Direct Debit Payment? SEPA Direct Debit is a payment transfer that is initiated by the service provider (e.g., NumXL) to allow automated payments from the customer’s bank account.

The SEPA Direct Debit payment is commonly used for recurring payments such as automated payments for a monthly subscription charge (can also be used for one-time payments) and requires a pre-authorization (mandate) from the customer.

How long does SEPA Direct Debit Payment take? SEPA Direct Debit transactions usually take 3 business days before the customer is charged. Once the customer has been charged, it will take an additional 3 business days to complete the order and generate a license key.

How about subscription renewals? The SEPA Direct Debit supports automatic billing for subscriptions. NumXL – through our reseller (FastSpring) – will bill customers again when their subscription is due for renewal. To cancel the subscription, customers should decline the automatic billing.

Currently, NumXL supports SEPA payments for customers in the following countries:

Austria (AT).
Belgium (BE).
Cyprus (CY).
Denmark (DK).
Estonia (EE).
Finland (FI).
France (FR).
Germany (DE).
Greece (GR).
Ireland (IE).
Italy (IT).
Netherlands (NL).
Poland (PL).
Portugal (PT).
Spain (ES).
Sweden (SE).
Switzerland (CH).

For more information on SEPA, please visit:

The post Single Euro Payments Area (SEPA) appeared first on NumXL.

Error: NumXLUI.xlam is not a valid add-in!

Shams Al-Bulbul — Fri, 09 Jun 2023 12:51:28 +0000

Error: NumXLUI.xlam is not a valid add-in!

Many customers reported that a strange error window popped up when they tried to activate the NumXL Toolbar (NumXLUI.xlam). The Error states: “NumXLUI.xlam is not a valid add-in” or “NumXLUI.xlam file format or file extension is not valid…”.

The error message is triggered by Windows Defender when the “Block Win32 API calls from Office macros” rule is set.

During a recent update to the Windows Security and Microsoft Defender for Endpoint service, user devices experienced a series of false positive detections for the Attack Surface Reduction (ASR) rule “Block Win32 API calls from Office macro” after updating to security intelligence build 1.381.2140.0.

This new rule blocks VBA macros trying to call Win32 APIs and prevents the NumXL toolbar from loading. In a few cases, users reported that their Antivirus (AV) programs popped warning messages on their screen of a “potentially malicious behavior” and blocked the add-in from loading.

Diagnosis:

There is no malware in our product. We developed NumXL entirely by ourselves and did not outsource any part.

Furthermore, all executable files (i.e., DLL, EXE, and XLAM) from NumXL are signed with a Code Signing certificate, ensuring that the files are from our company, Spider Financial Corp, and have not been tampered with. You can verify this information on the certificate.

As an extra protection layer In Excel, you can choose to disable all macros except digitally signed macros or require application add-ins to be signed by a trusted publisher.

Why do VBA macros call Win32 API?

NumXL UI uses a few Windows API calls for its core functionality, such as reading certain parts of the registry (for example, to determine whether the user is utilizing the 32-bit or 64-bit edition of Excel), storing items in memory, reading the user’s language settings for Windows and Excel, and accessing the user’s application data folder to store settings, among other functions.

Solution

If your organization has the “Block Win32 API Calls from Office Macros” rule applied in combination with “block mode,” you can still use NumXL by adding an exception for our files.

Exclude our installation folder:
- For 64-bit Installation, exclude the “%Program Files%\NumXL” folder.
- For 32-bit Installation, exclude the “%Program Files(x86)%\NumXL” folder.
Exclude our Toolbar add-in file (NumXLUI.xlam) in the installation folder.

To learn more, please check out the following guidelines (Microsoft):

Conclusion

The “NumXLUI.xlam” error message is due to a new rule in the latest Windows security update. It is not indicative of malware in our product, NumXL. You can resolve this issue by adding an exception for our files, following the guidelines provided by Microsoft. We appreciate your understanding and cooperation and assure you of our continued commitment to the security and functionality of our software.

The post Error: NumXLUI.xlam is not a valid add-in! appeared first on NumXL.

Migration from X12ARIMA to X-13ARIMA-SEATS

Rana Hussein — Fri, 16 Dec 2022 16:00:00 +0000

Migration From X12ARIMA to X-13ARIMA-SEATS

This tutorial outlines the steps to migrate existing X12ARIMA models to the latest
X-13ARIMA-SEATS model.

1. Select the X12ARIMA Model

In your workbook, select the cell where the X12ARIMA model is stored in your workbook. The cell will have a value beginning with “x12a_” and a formula that starts with an X12ARIMA(.) function.

For this tutorial, I chose the following model:

2. Launch X-13ARIMA-SEATS Wizard

To start the model migration, locate the ARMA icon, click on it, and select X-13ARIMA-SEATS
from the drop-down menu.

3. Examine the X13ARIMA Settings

The X-13ARIMA-SEATS Wizard processes the X12ARIMA model and maps its settings
(e.g., Input Data, Date Start, Transform, X11, ARIMA) into the equivalent settings
in X13AS. First, we need to select an empty cell to store the SPC settings.

Next, examine the X13ARIMA settings in the Input tab: verify the Input Data cell-range, Date Start, and Frequency. Press the Validate button to examine the model settings consistency and ensure that no error pops up.

Transform

Select or switch to the Transform tab. The transform function (i.e., log) should match the settings in the X13AS model.

Outliers

Select or switch to the Outliers tab and examine its settings. In our X12ARIMA model example, none of the Types are selected.

Seasonal Adjustment

Now, switch to or select the Seasonal Adj. tab. X12ARIMA supports only one Seasonal Adjustment Method: X11.

ARIMA

Now, select the ARIMA tab and examine its settings. The X12ARIMA support either the Auto(X11ARIMA/88) model selection or Manual.

Regression

Finally, select the Regression tab and examine the built-in variables:

Change the inner tab to Holidays.

4. Execute the X13AS Program with the Model

Press the Run X-13AS command button to launch the U.S. Census X13AS program with the new model and generate the different output files.

Error File

Press the Error File control to launch the notepad application with the X13AS error file loaded. Examine the content for any warnings or notes.

Output File

Press the Output File command button to launch the notepad application with the X13AS output file loaded. The output is a large file, so scroll down and familiarize yourself with its different sections.

5. Finalize or Commit the Migration

When you are comfortable with the imported settings, click the Apply command button. The X13AS Wizard will re-write the formula and generate the SPC string in the designated cells in your workbook.

Upon completion, the model’s cell will display a text value starting with “x13as_”.

IMPORTNANT

NumXL does not write any changes into your workbook until you click the Apply command button. So, if you click Cancel, all your changes will be lost.
In the X13AS Wizard, if you wish to restore the model to its initial state (when you first launched it) and undo your changes, Click the Reset button.
Once you export/migrate the model into X13AS(.), we can’t restore or undo the changes, so you may wish to keep a backup of your workbook.

6. Update Formulas of Dependent Cells

First, let’s locate all cells in your workbook that reference the model’s cell in its formula. To do so, let’s use Excel’s Trace Dependents feature:

Select the X13AS model’s cell
Select or switch to the Formulas tab in the toolbar
Locate and click on the Trace Dependents icon.

Now, edit the formula of the cell (at the end of the arrow), and replace X12ACOMP(.) with X13ASCOMP(.) and X12AFORE with X13ASFORE(.).

Furthermore, the X13ASCOMP and X13ASFORE use a date argument to reference the target period, so you need to replace the offset calculation in the older X12ACOMP and X12AFORE with actual serial date number values.

Notice

The U.S. Census X-13ARIMA-SEATS software is only available on 64-bit Windows machines. For more information, please consult this article: X-13ARIMA-SEATS Compatibility with 32-bit Windows.

The post Migration from X12ARIMA to X-13ARIMA-SEATS appeared first on NumXL.

Interpolation 101

Zaid Sabbah — Fri, 23 Sep 2022 12:46:31 +0000

Interpolation 101

Many years ago, NumXL supported an interpolation function as a utility or helper method to meet our users’ modeling needs. To our surprise, this functionality received significant attention, and we decided to compile some of the most frequently asked questions about it here.

What is Interpolation?

Interpolation is a numerical method for estimating the value of a new datapoint based on the range of a discrete number of known data points.

To illustrate, let’s assume the value of a function $y=f(x)$ is given as a set of five (5) distinct x-values, as shown in the plot below.

Now, we wish to estimate (i.e., interpolate) the value of the function at $x=5$, using the given five (5) data points.

For interpolation purposes, the desired target x value must be within the range of the original data set (e.g., $2\le x\le 12$).

Before we go any further, let’s answer a few foundational questions we often hear:

What about extrapolation? How is it different? Extrapolation is the process of estimating the function value of an x-value that falls outside the range of a given dataset.
Does NumXL support extrapolation? Yes, the same function NxINTRPL(.) can be used for extrapolation. Make sure to pass “true” or 1 to the “extrapolate” argument.
Is curve fitting (e.g., regression) and interpolation the same? Curve fitting and interpolation are often used interchangeably, but there is a subtle difference between them: interpolation must fit the data points exactly, whereas curve fitting must get as near as possible.
Should I use interpolation or curve fitting? It depends. Interpolation assumes that data points are known perfectly (or at least to very high precision), while curve fitting assumes some degree of noise in the data points.

Data Preparation

As we mentioned earlier, all interpolation methods accept a discrete set of known data points. To handle real-world data sets, the NumXL interpolation function (NxINTRPL) pre-processes the data to:

Sort the data points in ascending order (based on X).
Silently drop data points with missing values.
Replace multiple readings for the same X value with their average.

Q: Should the data points be uniformly separated?

A: No, the interval length between data points doesn’t need to be uniform, but we recommend that datapoints are spread out over the x-range to improve the interpolation accuracy and minimize the impact of outliers.

Interpolation Methods

Step Interpolation 1: Forward Flat Interpolation.
The step interpolation methods model the unknown function flat/constant between known datapoints, using the last known value.

Step Interpolation 2: Backward Flat Interpolation.
In the case of the backward flat method, the unknown function is also modeled as flat/constant between the known data points, but it uses the level of the immediate next data point.

Step Interpolation 3: Nearest Neighbor Interpolation.
With the nearest neighbor method, the unknown function is still modeled flat/constant, but its level is determined by the level of the nearest data point.

Linear Interpolation:
With linear interpolation, we connect each data point to its neighbors using straight lines.

Note that the values of the function between two data points is solely determined by those two data points.

Cubic Spline: Natural Spline
Using the cubic spline method, we construct a piece-wise cubic polynomial on each interval with matching first and second derivatives at the supplied data points. The second derivative is chosen to be zero at the first point and the last point.

The resultant curve is smooth, but it is not “local,’ which means if the functions make a large change (e.g., x=4), the curve around the surrounding points is “wobbly” (aka., it overshoots at intermediate points).

Cubic Spline: AKIMA Spline
To avoid the wobbly curve of the natural cubic spline, we relax the continuity of the second derivative around the data points (knots), resulting in non-rounded corners.

As shown in the figure above, the curve around the data points is continuous but not smooth.

Cubic Spline: Steffen Spline
Steffen’s spline adds a new constraint: the cubic spline segment is monotonic between data points. There is no wobbling, period.

The resultant curve is smooth around the data points and does not exhibit any wobbling between them. Therefore, minima and maxima can only occur exactly at the data points.

Steffen’s spline and its first derivative are guaranteed to be continuous, but the second derivative may be discontinuous.

Cubic Spline: Constrained (Kruger) Spline
Kruger proposed a constrained cubic spline to prevent overshooting by sacrificing smoothness, so we no longer require a continuous second derivative at every point and calculate the first derivative value numerically using surrounding points.

Using the constrained (Kruger) spline, we avoided the overshoot wobbly effect of the cubic spline and did not need to add monotonicity constraints.

There are other types of cubic splines, but we only introduced the ones currently supported in NumXL version 1.68.

Q: Which type of cubic spline should I use?

A: It depends on the nature of your data, and the importance of a smooth curve and/or overshoot side-effect. In many cases, we suggest choosing the constrained (Kruger) cubic spline as a safe bet.

Q: I see two interpolation functions in NumXL: Interpolate(.) and NxINTRPL(.). Which one should I use?

A: The Interpolate(.) function was our early implementation function, but it is now deprecated. All the interpolation methods mentioned above are available only in NxINTRPL(.), so you should only use this function.

Q: The NxINTRPL(.) function accepts both a single value and an array for the target argument. Why?

A: The NxINTRPL(.) runs faster when you interpolate for multiple values at once, rather than calling separately for each value. We recommend passing all x-values in a few function calls.

Files Examples

Please click the button below to download one dimensional interpolation example.

Interpolation-1D 0.00 KB

This file contains example for one dimensional interpolation. ...

Download

The post Interpolation 101 appeared first on NumXL.

Using Quarterly Data in X-13ARIMA-SEATS

Zaid Sabbah — Fri, 02 Sep 2022 16:01:40 +0000

Using Quarterly Data in X-13ARIMA-SEATS

The latest U.S. Census seasonal adjustment program, the X-13ARIMA-SEATS, supports monthly, quarterly, and annual datasets. Although monthly and annual data samplings are definite, quarterly reporting can be more ambiguous. Which quarterly cycle are we talking about? Is it a Mar-Jun-Sep-Dec cycle, or something else?

This article was inspired by an inquiry from one of our customers: specifically, an Australian analyst using quarterly data following a Feb-May-Aug-Nov cycle. Is this a problem?

The U.S. Census X-13ARIMA-SEATS documentation assumes that quarterly data follows the Mar-Jun-Sep-Dec cycle. So, what can we do to adjust to the analyst’s situation?

If we were to ignore the reporting month discrepancy and use the data as is, then the calendar (trading days, leap year) and holidays adjustment would be incorrect for many periods. This would lead to errors.

If you don’t care for the prior data adjustment or wish to include a regression component in the forecast SARIMA model, then you could simply shift the date component in your dataset by one month so that it matches the Mar-Jun-Sep-Dec Cycle.

What is the alternative? We could resample, as follows:

For stock-type data, we interpolate the levels on the Mar-Jun-Sep-Dec months.
For flow-type data: (1) convert (i.e., aggregate) it to form a stock-type time series, (2) interpolate the stock-levels on the Mar-Jun-Sep-Dec months, and finally, (3) difference the interpolated values time series to reconstruct a flow-type.

IMPORTANT: NumXL comes with a simple, yet powerful, interpolation function – NxINTRPL(.), which allows you to interpolate the whole time series in one call. See the attached examples.

But what about the outputs: seasonally adjusted and forecast values?

X-13ARIMA generates all its outputs using the Mar-Jun-Sep-Dec cycle, so if you wish to take them back to a raw data quarter-cycle, you’ll need to carry on an interpolation for the new months.

Files Examples

Please click the button below to download Quarterly Data In X-13ARIMA-SEATS example.

X13 Quarterly Data Example 0.00 KB

This file contains example for X13 Quarterly Data example. ...

Download

The post Using Quarterly Data in X-13ARIMA-SEATS appeared first on NumXL.