Installation and Setup

1. Sign up for a Timewise Analytics account, to do this simply login with your Google Account here and make a note of your subscription key.

2. Install the Timewise Analytics Google Sheets™ Addon from Google Marketplace here

3. In Google Sheets™, click on:
Extensions->Timewise Analytics->Enter Subscription Key
in the menu and enter your subscription key

Installation and Setup demo

Handles

Timeseries Handles

A timeseries handle is a reference to timeseries data containing a list of dates and values. They look like this: 

{twHandle_22029477793476544593}

The advantage of timeseries handles is that timeseries data can be manipulated and controlled while referencing only a single cell in a spreadsheet.

For example n a very simple case, timeseries handles can be added, subtracted, multiplied together and in doing so a new timeseries handle will be created.

twDescribe will output the contents of the handle to the spreadsheet.

Timeseries handles can be charted using the menus under the Extensions menu in Google Sheets™, supported charts are:

  • Timeseries
  • Spread
  • Regression
Collapsed Timeseries Handles

A collapsed timeseries handle is a reference to a collapsed timeseries. They look like this:

{twCHandle_52029477793476554593}

Collapsed timeseries are created when the period of a timeseries is changed. For example, if you have a daily timeseries of sales data you may want to summarise this data monthly.

This can be done using the twCollapse function which will return a twCHandle containing the summarized data.

twDescribeCollapsedHandle will output the contents of the handle to the spreadsheet.

This handle can be used to chart a Box Plot displaying a summary of the median, min, max and inter quartile range.

Histogram Handles

A histogram handle is a reference to a histogram. They look like this:

{twHistHandle_7214105611774397050}

This handle is created using the twHistogram function.

twDescribeHistogram will output the contents of the handle to the spreadsheet.

This handle can be used to chart a Histogram showing the distribution of values in the timeseries.

PCA Handles

A PCA handle is a reference to a Principle Component Analysis. They look like this:

{twPCAHandle_7214105611774397050}

This handle is created using the twPCA function.

twDescribePCA will output the contents of the handle to the spreadsheet.

This handle can be used to chart the first two eigen vectors captured by the principle component analysis. 

Handles demo

Charting

One of the most useful applications of timeseries is to be able to quickly create, manipulate and then chart data using only single cells. Timewise Analytics provides a menu to allow many types of charts to be instantly generated by simply clicking on one or more timeseries handles and clicking on the appropriate chart function in the menu.

The following charts are available

  • Timeseries
  • Spread
  • Regression
  • Boxplot
  • Histogram
  • PCA
Charting demo

Timeseries

plots timeseries as a line chart

Timeseries demo

Spread

plots the difference between two timeseries as a line chart

Spread demo

Regression

plots the points of two timeseries as a scatter plot with the line of best fit and displays the correlation and R2

Regression demo

Box Plot

plots the min, max, median and inter-quantile range as a box plot chart based on the selected collapsed timeseries handle

Box Plot demo

Histogram

plots the bins of a histogram as a bar chart from the selected histogram handle

Histogram demo

PCA

plots the most significant 2 eigen vectors as a scatter chart based on the selected PCA handle

PCA demo

General Functions

General Functions demo

twCreate

creates a timeseries from the passed range of dates and values

Parameters
  • range- a spreadsheet range containing the dates and values of the timeseries to be created, this supports a single column of dates and one or more colums of values.
Returns
returns one or more timeseries handles 
twCreate demo

twDescribe

returns the time series data as a range

Parameters
  • handles- one or more timeseries handles
Returns
outputs the data from the handle(s) to the spreadsheet 
twDescribe demo

twDates

returns the dates from the timeseries

Parameters
  • handle- one timeseries handles
Returns
outputs the dates from the handle to the spreadsheet 
twDates demo

twValues

returns the values from the timeseries

Parameters
  • handles- timeseries handle(s)
Returns
outputs the values from the handle(s) to the spreadsheet 
twValues demo

twLast

returns the last value from the timeseries

Parameters
  • handles- timeseries handle(s)
Returns
outputs the last value from the handle(s) to the spreadsheet 
twLast demo

twFirst

returns the last value from the timeseries to the spreadsheet

Parameters
  • handles- timeseries handle(s)
Returns
outputs the first value from the handle to the spreadsheet 
twFirst demo

twValue

returns the value from the timeseries based on the passed index

Parameters
  • handles- timeseries handle(s)
  • index- the index of the value to return from the handle
Returns
outputs the value from the handle to the spreadsheet based on the index parameter 
twValue demo

twCount

returns the number of points in the timeseries

Parameters
  • handles- timeseries handle(s)
Returns
counts the number of entries in the handle(s) and outputs to the spreadsheet 
twCount demo

twLag

returns a lagged version of the timeseries

Parameters
  • handles- timeseries handle(s)
  • points- the number of points to lag the timeseries by
Returns
outputs a timeseries handle shifted backwward by the specified number of points

In time series analysis, a lag refers to the time between two observations in a time series. More specifically, the lag refers to the number of time intervals between two consecutive observations, usually measured in units of time such as hours, days, or months.

Lag is an important concept in time series analysis because it can affect the autocorrelation of the data, which is the degree to which the values in the time series are correlated with their own past values at different time lags.

For example, if we have a daily time series of stock prices, we can compute the autocorrelation between each day's price and the price on the previous day (lag 1), the price two days ago (lag 2), and so on. The autocorrelation at each lag can help us to identify any patterns or trends in the data.

Lags are also important in time series forecasting, as they can be used to model the relationships between the current value of the time series and its past values at different lags. Lagged values can be included as explanatory variables in regression models or used to create time series models, such as autoregressive integrated moving average (ARIMA) models. 

twLag demo

twLead

returns a leading version of the timeseries

Parameters
  • handles- timeseries handle(s)
  • points- the number of points to lead the timeseries by
Returns
outputs a timeseries handle shifted forwards by the specified number of points

In time series analysis, a lead refers to the time between two observations in a time series in the future direction. More specifically, the lead refers to the number of time intervals between two consecutive observations, usually measured in units of time such as hours, days, or months, in the future direction.

For example, if we have a monthly time series of sales data, we can compute the lead between each month's sales and the sales in the following month (lead 1), the sales two months ahead (lead 2), and so on. The leads can help us to analyze the future trends in the data and forecast future values of the time series.

Leads are important in time series forecasting, as they can be used to model the relationships between the current value of the time series and its future values at different leads. Lead values can be included as explanatory variables in regression models or used to create time series models, such as Vector Autoregressive (VAR) models.

In summary, lags refer to the past direction, and leads refer to the future direction in time series analysis. Both lags and leads can be used to model the temporal relationships between variables and forecast future values. 

twLead demo

twAlign

aligns the dates and points of multiple timeseries removing any points not present in all timeseries

Parameters
  • handles- timeseries handle(s)
Returns
outputs aligned timeseries handle(s) where the dates are aligned 
twAlign demo

twDespike

removes spikes from the timeseries using the passed window and standard deviations

Parameters
  • handles- timeseries handle(s)
  • k- the moving window to use for despiking
  • n- the number of standard deviations to use as a filter for despiking
Returns
outputs despiked timeseries handle(s)

In time series analysis, despiking refers to the process of removing outliers or spurious data points from a time series dataset that can distort the analysis or modeling of the data.

Outliers can arise due to measurement errors, data entry errors, or other anomalies that are not representative of the true underlying process. These outliers can lead to incorrect conclusions or predictions if they are not properly identified and removed. 

twDespike demo

twExpand

expands the points of a timeseries to align with the points of another using roll-forward

Parameters
  • handles- timeseries handle(s)
  • dateFrom- the date to begin the expanded timeseries
  • dateTo- the date to end the expanded timeseries
Returns
outputs aligned timeseries handle(s) where the dates are aligned 
twExpand demo

twChange

returns a timeseries containing the 1 step changes from the passed timeseries

Parameters
  • handles- timeseries handle(s)
Returns
timeseries of the difference between consecutive points 
twChange demo

Math

Math demo

twAdd

adds two timeseries or adds a constant to a timeseries

Parameters
  • handle1- the first timeseries handle(s)
  • handle2- the second timeseries handle(s) or constant(s) to add
Returns
a timeseries of the pointwise or constant addition to handle1 
twAdd demo

twSubtract

subtract two timeseries

Parameters
  • handle1- the first timeseries handle
  • handle2- the second timeseries handle
Returns
returns a timeseries of the point-wise subtraction of handle2 from handle1 
twSubtract demo

twMultiply

multiplies two timeseries or multplies a timeseries with a constant

Parameters
  • handle1- the first timeseries handle(s)
  • handle2- the second timeseries handle(s) or constant(s) to multiply
Returns
a timeseries of the pointwise or constant multiplication with handle1 
twMultiply demo

twDivide

divides two timeseries or divides a timeseries by a constant

Parameters
  • handle1- the first timeseries handle(s)
  • handle2- the second timeseries handle(s) or constant(s) to divide by
Returns
a timeseries of the pointwise or constant multiplication with handle1 
twDivide demo

twAbsolute

returns the absolute values of a timeseries

Parameters
  • handle1- timeseries handle(s)
Returns
a timseries containing the absolute values of the timeseries handle(s) passed in handle1 
twAbsolute demo

Stats

Stats demo

twMean

calculate the mean of a timeseries

Parameters
  • handle- the timeseries handle(s) for which to calculate the mean
Returns
returns a number representing the mean of the timeseries

In statistics, the mean is a measure of central tendency that represents the average value of a dataset. It is also known as the arithmetic mean or simply the average.

The mean is calculated by summing all the values in a dataset and then dividing the sum by the number of values in the dataset. Mathematically, the formula for the mean is:

The mean is a commonly used measure of central tendency because it takes into account all the values in the dataset. However, it can be affected by extreme values or outliers, which may not be representative of the typical values in the dataset. In such cases, other measures of central tendency such as the median or mode may be more appropriate. 

twMean demo

twMin

calculate the min of a timeseries

Parameters
  • handle- the timeseries handle(s) for which to calculate the minimum value in the timeseries
Returns
returns a number representing the minimum value in the timeseries 
twMin demo

twMax

calculate the max of a timeseries

Parameters
  • handle- the timeseries handle(s) for which to calculate the maximum value in the timeseries
Returns
returns a number representing the maximum value in the timeseries 
twMax demo

twMinIndex

returns the index of the minimum value in the timeseries

Parameters
  • handle- the timeseries handle(s) for which to find the index of the minumum value in the timeseries
Returns
returns an integer representing the index of the minumum value in the timeseries 
twMinIndex demo

twMaxIndex

returns the index of the maximum value in the timeseries

Parameters
  • handle- the timeseries handle(s) for which to find the index of the maximum value in the timeseries
Returns
returns an integer representing the index of the maximum value in the timeseries 
twMaxIndex demo

twMinDate

returns the date of the minimum value in the timeseries

Parameters
  • handle- the timeseries handle(s) for which to find the date of the minimum value in the timeseries
Returns
returns a date corresponding to the minimum value in the timeseries 
twMinDate demo

twMaxDate

returns the date of the maximum value in the timeseries

Parameters
  • handle- the timeseries handle(s) for which to find the date of the maximum value in the timeseries
Returns
returns a date corresponding to the maximum value in the timeseries 
twMaxDate demo

twSum

calculate the sum of a timeseries

Parameters
  • handle- the timeseries handle(s) for which to calculate the sum
Returns
returns a number representing the sum of the values in the timeseries 
twSum demo

twMedian

calculate the median of a timeseries

Parameters
  • handle- the timeseries handle(s) for which to calculate the median
Returns
returns a number representing the median values of the timeseries

In statistics, the median is a measure of central tendency that represents the value in a dataset that separates the lower half from the upper half of the values.

More precisely, the median is the middle value of a sorted dataset, or the average of the two middle values if the dataset has an even number of values.

For example, consider the following dataset of 7 values: {4, 5, 2, 8, 6, 1, 9}. To find the median, we first sort the values in ascending order: {1, 2, 4, 5, 6, 8, 9}. The median is the middle value, which in this case is 5.

The median is a useful measure of central tendency when the dataset contains extreme values or outliers that may skew the mean (average). In contrast to the mean, the median is not affected by extreme values and provides a more robust measure of central tendency in such cases.

The median is also commonly used in non-parametric statistical tests, such as the Mann-Whitney U test and the Wilcoxon signed-rank test, which do not assume a normal distribution of the data.

twMedian demo

twVariance

calculate the variance of a timeseries

Parameters
  • handle- the timeseries handle(s) for which to calculate the variance
Returns
returns a number representing the variance of the timeseries

In statistics, variance is a measure of how spread out a set of data is around its mean (average) value. Specifically, it measures the average of the squared differences of each data point from the mean.

The variance tells us how much the data is dispersed from the mean value. If the variance is large, it means that the data is more spread out, whereas if the variance is small, it means that the data is more tightly clustered around the mean.

Variance is an important concept in many statistical applications, including hypothesis testing, confidence intervals, and regression analysis. The square root of the variance is the standard deviation, which is another commonly used measure of variability.

twVariance demo

twStandardDeviation

calculate the standard deviation of a timeseries

Parameters
  • handle- the timeseries handle(s) for which to calculate the standard deviation
Returns
returns a number representing the standard deviation of the timeseries

In statistics, the standard deviation is a measure of the spread or dispersion of a set of data values from their mean (average) value. It is a widely used measure of variability or diversity in data.

The standard deviation is defined as the square root of the variance, which is the average of the squared differences between each data point and the mean. Mathematically, the standard deviation can be expressed as:

The standard deviation tells us how much the data is spread out from the mean value. If the standard deviation is large, it means that the data is more spread out, whereas if the standard deviation is small, it means that the data is more tightly clustered around the mean.

The standard deviation is commonly used in many statistical calculations, including the calculation of confidence intervals, hypothesis testing, and the computation of z-scores and t-scores in statistical inference.

twStandardDeviation demo

twSkew

calculate the skew of a timeseries

Parameters
  • handle- the timeseries handle(s) for which to calculate the skew
Returns
returns a number representing the skew of the timeseries

Skewness is a measure of the asymmetry of a probability distribution. It measures the degree to which the values in a distribution are skewed to one side or the other of the mean.

A distribution that is symmetric around the mean has zero skewness. Positive skewness occurs when the tail of the distribution is longer on the positive side than on the negative side, while negative skewness occurs when the tail is longer on the negative side than on the positive side.

Skewness is typically measured using the third central moment of a distribution, which quantifies how much the data deviate from the mean. The most commonly used measure of skewness is the sample skewness, which is a standardized measure that is based on the sample mean, sample standard deviation, and sample size. 

twSkew demo

twKurtosis

calculate the kurtosis of a timeseries

Parameters
  • handle- the timeseries handle(s) for which to calculate the kurtosis
Returns
returns a number representing the kurtosis of the timeseries

Kurtosis is a statistical measure that describes the shape of a probability distribution. Specifically, it measures the "peakedness" and "tail-heaviness" of a distribution relative to a normal distribution.

A distribution with positive kurtosis has a higher peak and heavier tails than a normal distribution, while a distribution with negative kurtosis has a lower peak and lighter tails. A distribution with zero kurtosis is said to be mesokurtic, meaning it has the same peak and tail behavior as a normal distribution.

The formula for kurtosis involves the fourth central moment of a distribution, which measures the spread of the data around the mean. The most commonly used measure of kurtosis is the excess kurtosis, which subtracts 3 from the kurtosis of a normal distribution so that a normal distribution has a kurtosis of zero.

twKurtosis demo

twAutocorrelation

calculate the autocorrelation of a timeseries with lag 1

Parameters
  • handle- the timeseries handle(s) for which to calculate the autocorrelation
Returns
returns a number representing the autocorrelation of the timeseries

Autocorrelation, also known as serial correlation, is a statistical measure that indicates the degree to which the values of a time series are correlated with their own past values at different time lags.

In other words, autocorrelation measures the similarity between a given value in a time series and the previous values in the series. A high degree of autocorrelation indicates that the time series is not random and that the values in the series are related to each other over time.

Autocorrelation is often measured using a correlogram or autocorrelation function (ACF) plot, which shows the correlation between a time series and its own lagged values. The ACF plot displays the autocorrelation coefficient (or correlation coefficient) on the vertical axis and the time lag on the horizontal axis.

Autocorrelation is important in time series analysis and forecasting because it can affect the accuracy of predictions. If a time series exhibits strong autocorrelation, it means that the past values of the series can be used to predict future values, and it may be necessary to include lagged values of the series in a forecasting model. Conversely, if a time series has low or no autocorrelation, it may be more difficult to predict future values based on past values.

twAutocorrelation demo

twCovariance

calculate the covariance of two timeseries

Parameters
  • handle- the timeseries handle(s) for which to calculate the covariance
Returns
returns a number representing the covariance of the timeseries

Covariance is a measure of how two variables are related to each other. Specifically, it measures the degree to which two variables move together, or co-vary, over time or across observations.

A positive covariance indicates that when one variable is above its mean value, the other variable tends to be above its mean value as well. A negative covariance indicates that when one variable is above its mean value, the other variable tends to be below its mean value. A covariance of zero indicates that the two variables are not related.

Covariance is often used in finance and portfolio management to measure the relationship between the returns of different assets. It is also used in regression analysis to estimate the coefficients of a linear regression model. However, covariance has some limitations, and its magnitude depends on the scales of the two variables being compared, which can make it difficult to interpret. Therefore, the standardized version of covariance, called correlation, is often preferred. 

twCovariance demo

twCorrelation

calculate the correlation of two timeseries

Parameters
  • handle- the timeseries handle(s) for which to calculate the correlation
Returns
returns a number representing the correlation of the timeseries

Correlation is a statistical measure that indicates the strength and direction of the relationship between two variables. Specifically, it measures how closely the values of two variables are related to each other.

Correlation is often measured using Pearson's correlation coefficient, which is a standardized measure that ranges from -1 to +1. A correlation coefficient of +1 indicates a perfect positive correlation, meaning that when one variable increases, the other variable increases in proportion. A correlation coefficient of -1 indicates a perfect negative correlation, meaning that when one variable increases, the other variable decreases in proportion. A correlation coefficient of 0 indicates no correlation, meaning that the variables are not related.

Correlation is widely used in many fields, including finance, social sciences, and engineering, to analyze the relationships between variables and to make predictions about future outcomes. However, it is important to note that correlation does not necessarily imply causation, and other factors may be influencing the relationship between the variables. 

twCorrelation demo

twRegression

calculate the best-fit linear regression coefficients of two timeseries

Parameters
  • handle- the timeseries handle(s) for which to calculate the regression coefficients
Returns
returns numbers representing the regression coefficients of the timeseries

Linear regression is a statistical technique used to model the relationship between a dependent variable (also known as the response variable) and one or more independent variables (also known as explanatory variables or predictors) using a straight line.

The goal of linear regression is to estimate the values of the coefficients β0, β1, β2, ..., βn that minimize the sum of the squared errors between the predicted values of the dependent variable and the actual values of the dependent variable.

Linear regression is commonly used in many fields, including finance, economics, marketing, and social sciences, to analyze the relationships between variables and to make predictions about future outcomes. 

twRegression demo

twPCA

perform principle component analysis on the range of timeseries

Parameters
  • handle- the timeseries handle(s) for which to perform the Principle Component Analysis
  • matrixType- valid values are covar and correl, defaults to correlation
Returns
returns a PCA handle containing the eigen values and eigen vectors from the Principle Component Analysis

Principal Component Analysis (PCA) is a statistical technique used to reduce the dimensionality of a dataset while retaining as much of the variability in the data as possible. It is a commonly used technique in data science, machine learning, and statistics.

PCA works by transforming a dataset into a new set of variables, known as principal components, that are uncorrelated with each other and capture the maximum amount of variation in the original dataset. The first principal component captures the most variation, the second principal component captures the second most variation, and so on.

PCA is particularly useful for datasets with a large number of variables, as it can help to identify the most important variables and reduce the complexity of the data. It is also useful for visualizing high-dimensional data by projecting it onto a lower-dimensional space. 

twPCA demo

twCollapse

collapses a timeseries to the passed frequency, eg. Daily data to monthly data, returns the mean, median, min, max and inter quantile range for the target frequency

Parameters
  • handle- the handle to collapse
  • period- the period to collapse the timeseries to. Valid values are month, day, week, minute and second
  • field- optional, if provided a timeseries will be returned containing only the passed field. Valid values are mean, median, sum, min, max, q25 and q75
Returns
either a timeseries handle or a collapsed timeseries handle depending on the value of the field parameter 
twCollapse demo

twHistogram

returns a handle to a histogram of a timeseries. The number of buckets are decided using the Freedman–Diaconis rule

Parameters
  • handle- a single timeseries for which to calculate the histogram
Returns
a histogram handle

A histogram is a graphical representation of the distribution of a dataset, typically used to show the frequency of values in a given range of values.

A histogram consists of a set of rectangular bins, each representing a range of values, and the height of each bin represents the number of data points in that range. The bins are usually arranged side by side, with no gaps between them, to emphasize the continuity of the data.

The Freedman-Diaconis rule is a method for determining the number and width of the bins in a histogram. It is based on the interquartile range (IQR), which is the difference between the 75th percentile (Q3) and the 25th percentile (Q1) of the dataset.

The rule suggests that the bin width should be set to:

bin width = 2 * IQR / (n^(1/3))

where n is the sample size. The number of bins can then be determined by dividing the range of the data by the bin width and rounding to the nearest integer.

The Freedman-Diaconis rule is a useful way to determine the appropriate bin size for a histogram, as it takes into account the spread of the data and is less sensitive to outliers than other methods, such as Sturges' rule. However, it is important to note that the rule is only a guideline and may not always be optimal for all datasets.

twHistogram demo

Moving Stats

Moving Stats demo

twMovingAverage

calculate a moving average of a timeseries

Parameters
  • handle- timeseries handle(s) to calculate the moving average on
  • window- the size of the window to use for the moving average
Returns
a timeseries handle containing the moving average of the timeseries handle(s) passed in the handle parameter

A moving average is a statistical technique used to smooth out fluctuations in a time series data by calculating the average of a rolling window of values. It is a commonly used method in finance, economics, and engineering.

The moving average is calculated by taking the average of a specified number of consecutive data points in a time series, and then moving the window by one data point and calculating the average again. This process is repeated for all data points in the time series.

For example, consider a time series of 12 monthly sales data: {10, 8, 12, 14, 16, 20, 22, 18, 16, 12, 10, 8}. To calculate a 3-month moving average, we would take the average of the first three data points (10, 8, 12), then the second three data points (8, 12, 14), and so on, until we have calculated the moving average for all 12 data points.

The moving average can help to smooth out the fluctuations in the data and highlight any trends or patterns that may be present. It can also be useful in forecasting future values of the time series by extrapolating the trend observed in the moving average. 

twMovingAverage demo

twMovingMin

calculate a moving min of a timeseries

Parameters
  • handle- timeseries handle(s) to calculate the moving minimum on
  • window- the size of the window to use for the moving minimum
Returns
a timeseries handle containing the moving minimum of the timeseries handle(s) passed in the handle parameter 
twMovingMin demo

twMovingMax

calculate a moving max of a timeseries

Parameters
  • handle- timeseries handle(s) to calculate the moving maximum on
  • window- the size of the window to use for the moving maximum
Returns
a timeseries handle containing the moving maximum of the timeseries handle(s) passed in the handle parameter 
twMovingMax demo

twMovingMedian

calculate a moving median of a timeseries

Parameters
  • handle- timeseries handle(s) to calculate the moving median on
  • window- the size of the window to use for the moving median
Returns
a timeseries handle containing the moving median of the timeseries handle(s) passed in the handle parameter 
twMovingMedian demo

twMovingVariance

calculate a moving variance of a timeseries

Parameters
  • handle- timeseries handle(s) to calculate the moving variance on
  • window- the size of the window to use for the moving variance
Returns
a timeseries handle containing the moving variance of the timeseries handle(s) passed in the handle parameter 
twMovingVariance demo

twMovingStandardDeviation

calculate a moving standard deviation of a timeseries

Parameters
  • handle- timeseries handle(s) to calculate the moving standard deviation on
  • window- the size of the window to use for the moving standard deviation
Returns
a timeseries handle containing the moving standard deviation of the timeseries handle(s) passed in the handle parameter 
twMovingStandardDeviation demo