Assessment Brief: Spike Detection Modelling in Finance S1

z(xt) = (xt − ¯ xt)
σt

1. 第一个是单个神经元的电生理记录。这是一个常见的

2、二是ASX200指数从1992年到1992年的历史日收盘价数据

1. 时间序列数据
2.滞后窗口（滚动平均值和标准偏差的数据点数）
3. 阈值 (n) – 每个观测值远离的移动标准差的数量

4. 一个重量参数——我会让你自己解决这个（它在某个地方

=（数据，滞后，阈值，权重）

Background
The algorithm in front of you is used to detect spikes from time-series data. This algorithm
uses moving averages and moving standard deviations to identify spikes. A spike is defined
when a data point, xt is greater than n×σt from ¯ xt, where n is an integer, ¯ xt and n×σt,
are respectively the moving average and moving standard deviation.
Recall from your basic statistics course that the Z score tells you how far a data point
is from the mean in units of standard deviation (in our case, the mean and standard
deviations are both moving, where the Z-score for xt is calculated as:
z(xt) = (xt − ¯ xt)
σt
If the raw data point, xt, is above (below) the mean then the Z-score is positive (negative).
If xt is equal to the mean then the Z-score is 0. For example, a spike can be identified
when the moving Z-score for xt is greater than 3, which is the same as saying when xt is
greater than 3 × moving standard deviation from the moving mean. Again, 3 is just an
arbitrary number and that number may not be ideal for all datasets.
The image below is an illustration of the algorithm (see Figure 1):

Figure 1: In the top panel, the blue trace is the raw time series data. The yellow race
is the moving average based on a defined lag window. The purple trace is the moving
standard deviation based on a defined lag window. In the bottom panel, the red lines
are the spikes detected when the above mentioned conditions are true. A value of one is
assigned when true and zero otherwise.In neuroscience, an action potential (spike) from the same neuron cannot occur consec-
utively due to the absolute refractory period of a neuron (the membrane potential must
reset before another action potential can fire again). In order to correct for the VALID
number of spikes, you must calculate the time between spikes (also known as the inter-
spike interval). For this assessment, it takes 50ms for the membrane potential to reset.
Therefore, if a spike occurs at x0, this same neuron cannot spike again until after 50ms
from x0. In the case that there are false positive spikes, you need to revert the signals
back to 0.
Finally, for the purpose of the presentation, you must calculate the average inter-spike
intervals starting from the very first spike from the list of valid spikes. You do not need
to report this figure in the spreadsheet as we’ll discuss this in the presentation. ALL
calculations but be completed in VBA.

Datasets
In this workbook, I’ve provided two sets of data:
1. The first is an electrophysiological recording of a single neuron. This is a common
trace in neuroscience where it illustrates the change in electrical activity (measured
in voltage) over time, with spikes denoting action potentials. Each data point
represents 10ms.
2. The second is historical daily closing price data of the ASX200 index from 1992 to
this week. In this case, spikes would denote abnormal daily returns.
Description of the function
The spike detection algorithm is called as a function and relies on 4 main inputs:
1. Time series data
2. Lag window (number of data points for the rolling average and standard deviation)
3. Threshold (n) – the number of moving standard deviations each observation is away
from the moving average
4. A weight parameter – I’ll let you work this one out yourself (it’s somewhere in the
code) and plays an important role in this algorithm.
The function MUST be called as follows:
=(Data, Lag, Threshold, Weight)

EasyDue™ 支持PayPal, AliPay, WechatPay, Taobao等各种付款方式!

E-mail: easydue@outlook.com  微信:easydue

EasyDue™是一个服务全球中国留学生的专业代写公司