Frequently Asked Questions - NumXL 1.0

Remote Desktop™ is a remote support tool that enables instant, secure, and trouble-free connections between remote computers over the web and to any point on the globe.

Using state of art remote desktop software from techinline, our support team can quickly view, diagnose, and fix remote desktops without pre-installing software.

• Remotely control any desktop anywhere in the world
• Send and receive text chat messages

To get started, do the following:

1. Get a client ID: follow this link
2. A new broweser will open, Copy the Client ID into clipboard (Ctrl+C), and leave the browser window open.
3. Contact our support line through email (support@spiderxl.com) or Online Live Support, and send the Client ID
4. The representative will initiate a remote desktop session to your computer.

After installing NumXL, few users reported this error poping up when they open Excel.

The problem is directly related to the initialization of the user-interface portion of the Add-in and the virus scanner software on the user's machine. Nevertheless, the actual functions (e.g. ACFⁱ, WNTest, etc.) should be available are ready for use.

The problem occurs when the virus scanner on the client machine is interferring with the add-in startup code; We have seen this problem with Trend Micro Virus Scanner, AVG, but others may have similar issues.

To get around this problem, update your virus scanner software, and/or add an exception to allow Excel (and, NumXL) to access system registry.

If you continue to experience this error, please contact our support line.

Excel 2010, 2007:

If the installation was successful, you will see a new Ribbon tab, named "NumXL" that contain add-in icon.

Excel 2003-2000:

you will see a new menu and toolbar with the add-in icons in your Excel.

Start Menu

You can also find the installer program, help file and examples in the start menu.

Insert Function Dialog

The core-component of NumXL add-in registers tens of functions with Excel. To start using those functions, simply locate them in the Insert Function Dialog.

Spider Financial signs the executable binaries (i.e. programs) with its own digital-certificate issued by trusted certificate publisher.

If you see the security warning, this indicates you have the security settings for add-ins to be high; requiring all add-in to be signed by trusted publisher. Furthermore, the security warning also shows that Microsoft Excel recognizes our certificate as valid, but since this is your first add-in from Spider (i.e publisher (i.e. Spider Financial), you have not chosen to trust. Microsoft Excel program requires users to explicity accept the publisher's certificate before it can load the add-in.

To accept the certificate and permits Excel to load the add-in, see the instructions below:

Whatever your Excel or NumXL version, it is necessary that the Macros security level is set to High or Medium so that NumXL can run.

To examine the current security setting, use excel menu and find tools, then select Macros and click on Security..

The process is simple; Just download the installer . Then close Excel and run the file you have downloaded to start the installation process.

The installation will take few seconds. To find out how to run NumXL for the first time, click here to find tutorials.

We have implemented Nelder-Mead Simplex algorithm to support optimization required by several of our functions (e.g. model calibration, Optimal BoxCox Transform, ADF test, etc.). To account for constraints violation, we added a penalty term in the Utility function.

To calibrate an EGARCHⁱ or GARCH model, I’d suggest to use at least few hundred observations. There's a tradeoff between too few and too many; especially if you suspect that the time dynamic of series changes over time.

In practice, you want to conduct a back-testing for the model to ensure stability: to do so, calibrate the model using a N-observations rolling (overlapping or non-overlapping) windows of the input data, and then examine the model's parameters for signs of instability. 

There are a number of ways to calculate an estimate of the current volatility: (1) using parametric and non-parametric models. (2) intraday tick data, (3) Implied volatility in option markets, etc.

Using a GARCHⁱ model, the user computes the model-fitted volatility, and treat the last value as an estimate for current volatility.

An array function is a function that returns multiple values (i.e. arry of value). For instance, EWMAⁱ function returns the whole time series of the exponential weighted volatility time series.

An array function returns the value of the first element by default. To expand the values, please select all cells column-wise (starting from the cell with formula), hit F2 to view the formula and hit CTRL+ALT+ENTER together. If successfull, you will see the formula enclosed between italic braces (i.e. {}).

Using cross-correlation function (XCF) and LAG operatorⁱ, you can compute the different correlation factors.

Example: Let's assume we have two time series; Series A and Series B
Series A is stored in cells B1:B200
Series B is stored in cells C1:C200

To compute the cross-correlation matrix (R), we use the XCF and LAG functions as follow:
R(j,k) = XCF(LAG(B1:B200,1,j),1, LAG(C1:C200,1,k),1)

NumXL product is an Excel add-in, and is compiled for high speed. For this reason, it cannot be accessed directly from Visual Basic or macros. Nevertheless, you can use NumXL indirectly from Visual Basic for Applications within Excel only by using the "Run" command. For example:

Public Function GEDⁱ_XKURT(a)
GED_XKURT = Run("GED_XKURT", a)
End Function

Now GED_XKURT can be used as a spreadsheet function.

For developing Visual Basic applications, you can use NumSDK, our line of C/C++, Java and VB callable object libraries.

The financial time series of equities prices exhibits upward trend. Our goal is to show how de scriptive analysis can be used before a modeling approach.

YES, you can use the Solver add-in that ships with Microsoft Excel. If you don't have enabled, goto the Add-Ins manager and enable it.

1. describe the model you are trying to fit. This is done by guessing the initial coefficients of the model, and compute the log-likelihood (*_LL()) of the model.
   
2. Designate the cell that hold the log-likelihood as the Target cell, set equal to "Max", cells that hold model coefficients as "Changing-cells", and click on "Solve".
   
3. Using *_CHECK (GARCHⁱ_CHECK for GARCH model, ARMAⁱ_CHECK for ARMA model, etc.), examine the coefficients whether they yield a stable model. Add a constraint to the solver by designating this cell equals to one(1).
   
4. Now, click on "Solve" button on the Solver dialog box. The Solver will search for a set of optimal coefficients.
5. Upon completion, the solver will report an optimal solution is found. Click "OK".
   
6. In the rare occassions where a solution can't be found, change you initial guess of the coefficients, and try again.

In NumXL, we can test whether a time series is a white noise or not using WNTest function. WNTest examines the data series for evidence of any serial correlation using Ljung-Box statistical test and modified Q^*(m) statistics:

Where

The user can specify the upper lag order for the test, but we recommend using the value of .

Yes, you definitely can do so. To illustrate, Let's assume the price time series (i.e. \textrm{P_{t}}) are stored in A1:A300 range.

To compute logarithmic price

• In Excel, in cell B1, type +LN(A1:A300) and hit return. This will compute the log of first data cell.
• select cell B1:B300, hit F2 to edit the formula, and ctrl+Alt+Enter together. This will populate the B1:B300 and format the fomula as {=LOG(1:300)}

To compute logarithmic returns

• In Excel, in cell B1, type +DIFF(LN(1:300),1,1) and hit return. This will compute the log price and difference it next (i.e. log return).
• select cell B1:B300, hit F2 to edit the formula, and ctrl+Alt+Enter together. This will populate the B1:B300 and format the fomula as {=DIFF(LN(1:300),1,1)}

To compute the ACFⁱ(k=3) of a logarithmic return directly

• In Excel, in cell B1, type +ACF(DIFF(LN(1:300),1,1),1,3) and hit return. This will compute the log return time series and the ACF on the derived series.
• select cell B1:B300, hit F2 to edit the formula, and ctrl+Alt+Enter together. This will populate the B1:B300 and format the fomula as {=ACF(DIFF(LN(1:300),1,1),1,3)}

NumXL allows you to directly use its functions within your Excel sheet in the same way you would with built-in Excel functions.

In order to illustrate the use of a NumXL function within an Excel sheet, consider the following example:

• Open MS Excel, and create a series of 20 numbers.
• Using Excel toolbar, locate the "Insert function" field (i.e. labeled "fx") and click it with your mouse.
• An "Insert Function" Dialog pops up on the screen (see below)

• Using the dialog fields, fills in the function input by selecting cells from your worksheet (see below)

• For more information about this particular function, click on "Help on this function" hyperlink to bring up the helpfile.
• Once done, click on OK.

NumXL is constantly tested with all existing PC versions of Excel (e.g. 97, XP, 2003, 2007, etc.). .

Important:

Whatever your Excel or NumXL version, it is necessary that the Macros security level (see menu Tools/Macros/Security of Excel) is set to Medium.

Excel 97 SR-1 users: your Excel version must be updated to SR-2 to ensure reliable operation.

Excel 97 SR-2 users: your Excel version must be updated for optimal NumXL performance. Please, download the free patch available on Microsoft Office update center .

Excel 2000 users: we recommend that you download and install the SR3 patch, available from Microsoft Office update center .

Excel 2002 (Office XP) users: to run some of the NumXL functions, you need to activate the 'Trust access to Visual Basic Project' in the 'Trusted Sources' panel of the Tools/Macros/Security menu of Excel. Please, download and install the SR3 patch, available at Microsoft Office update center .

Excel 2003 users: we recommend that you download and install the latest patches, available from Microsoft Office update center

Yes, there is.

For NumXL, see the following article.

Yes. NumXL manual is included with the product and installed on the user machine as a help file. You can access the manual via the start menu ("Start"-->"Spider Financial" -->"NumXL"). Furthermore, we have the manual  online and can be viewed by registered users.

The manual provides an in-depth description of NumXL functions, each followed by a list of articles and books to the which the user might want to refer.

Yes. NumXL tutorial is included in the help file and it can be found online.

Furthermore, NumXL is shipped with numerous examples (workbooks) and case studies, and the help file includes wealth of information and examples.

For a list of the functionalities offered in NumXL, click here.