## Sunday, October 19, 2014

### The problem

How to post nice pieces of code to a blog? The desired properties are:
1. Beauty.
2. Language awareness.
3. Easy to copy to the clipboard.
4. Easy to integrate in Blogger.
5. Easy to maintain

### One possible solution

Use Gist.You must have an account on GitHub. Gists are versioned text files, so you can alter them and fix any problems on the Gist page without having to deal with your blog page.

### How to integrate it into Blogger

After creating your Gist, copy the "Embed URL" to the clipboard:
The link is something like this:
<script src="https://gist.github.com/mrjimenez/bceea54fdf66a9aec7b2.js"></script>
Reserve a space to integrate the Gist in your blog post. Then choose "HTML" in the Compose/HTML radio button. Insert that code in the proper place in your page, and your're done:

Take a look at the snapshot:

## Saturday, October 18, 2014

### The problem

Uploading photos to Facebook seems to be unresponsive, I get to this screen and when I press the "Choose File" button, nothing happens:

### The solution

Go to a file manager (e.g, konqueror) drag the file with the mouse and drop it over one of the buttons. The file will then be accepted:

If you want to unselect a photo, click the corresponding "Choose File" button. Apparently, its intended function is not to choose a file, it is to reset the file to "No file choosen":

After that, click "Upload Photos" and wait, because it takes some time.

## Thursday, October 16, 2014

### NVidia Drivers

Make sure you have the official NVidia drivers installed in your system:
• Run Yast.
• Click in "Software Repositories"
• Choose "Specify URL", then "Next"
• Confirm
• Go back to Yast
• Click in "Software Management"
• Search for "nvidia"
1. x11-video-nvidiaG03-340.46-30.1.x86_64
2. nvidia-gfxG03-kmp-desktop-340.46_k3.7.10_1.1-30.1.x86_64
3. nvidia-computeG03-340.46-30.1.x86_64
4. nvidia-settings-325.15-1.3.x86_64
5. nvidia-glG03-340.46-30.1.x86_64
6. nvidia-texture-tools-2.0.6-36.2.x86_64
7. nvidia-uvm-gfxG03-kmp-desktop-340.46_k3.7.10_1.1-30.1.x86_64
Notice that the above assumes your board is supported by the G03 kernel driver and that you are using the "kernel-desktop". Make sure you choose the proper driver for your board and the kernel driver corresponding to your kernel.

### CUDA Installation

Install the CUDA repository. Although the repository is for OpenSuSE 13.1, it will work perfectly with 12.3.
• Choose "Specify URL", then "Next"
• Confirm
• Go back to Yast
• Click in "Software Management"
• Search for "cuda"
• Add the following packages (some of them will be automatically added):
1. cuda-documentation-6-5-6.5-14.x86_64
2. cuda-cudart-6-5-6.5-14.x86_64
3. cuda-cufft-dev-6-5-6.5-14.x86_64
4. cuda-repo-opensuse131-6.5-14.x86_64
5. cuda-visual-tools-6-5-6.5-14.x86_64
6. cuda-cufft-6-5-6.5-14.x86_64
7. cuda-npp-dev-6-5-6.5-14.x86_64
8. cuda-curand-dev-6-5-6.5-14.x86_64
10. cuda-runtime-6-5-6.5-14.x86_64
12. cuda-samples-6-5-6.5-14.x86_64
13. cuda-curand-6-5-6.5-14.x86_64
14. cuda-toolkit-6-5-6.5-14.x86_64
15. cuda-cublas-6-5-6.5-14.x86_64
16. cuda-cusparse-dev-6-5-6.5-14.x86_64
17. cuda-drivers-340.29-0.x86_64
18. cuda-cudart-dev-6-5-6.5-14.x86_64
19. cuda-npp-6-5-6.5-14.x86_64
20. cuda-command-line-tools-6-5-6.5-14.x86_64
21. cuda-cusparse-6-5-6.5-14.x86_64
22. cuda-6.5-14.x86_64
23. cuda-core-6-5-6.5-14.x86_64
24. cuda-cublas-dev-6-5-6.5-14.x86_64
25. cuda-driver-dev-6-5-6.5-14.x86_64
26. cuda-6-5-6.5-14.x86_64
• Click "Accept".

## Abstract

A moving average (MA) process, also known as weighted moving average (WMA) is a type of signal filtering that consists in performing a weighted average over a finite sequence of past samples of the original signal. Implementing such scheme in a worksheet is not always straightforward due to the handling of missing values. This article proposes an implementation of such filters on Google Sheets using Google App Scripts, a Javascript like language. Exponential Moving Average (EMA) filters are an important special case of WMA, so they have been implemented on top of WMA.

## Keywords

Moving Average, MA, Weighted Moving Average, WMA, Exponential Moving Average, EMA, Noise Removal, Filtering, Google Sheets, Spreadsheets, Google App Scripts, Javascript.

## Introduction

In previous articles [1][2], implementations of WMA and EMA have been proposed using the normal spreadsheet function infrastructure. These implementations try to be compatible with existing spreadsheet standard functions so that they can be easily ported to other spreadsheet e.g., OpenOffice.

The inconvenient in this solution is that the formulas are large and as a consequence, hard to read and maintain, making it easy to slip subtle errors. Also, the formula must be called once for each line to be calculated, and this process has a big overhead. The ideal solution should make a single call to a function that would return an array of processed data. The following implementation addresses both issues.

## Results

The resulting spreadsheet shows a comparison of the previously posted methods with this scripted based one.

## Conclusion

Two javascript functions have been developed to implement the missing data weighted moving averages of previous articles [1][2]. The results have been shown to be identical. The javascript based method has the advantage of being much cleaner to maintain, typically requiring a single cell on the spreadsheet.

## Abstract

A moving average (MA) process, also called weighted moving average (WMA) is a type of signal filtering that consists in performing a weighted average over a finite past sequence of samples of the original signal. Implementing such scheme in a worksheet is not always straightforward due to the handling of missing values. This article proposes a convenient way to implement such filters on Google Sheets.

## Introduction

The moving average signal is defined by:
$y(j) = \sum_{i=0}^n w(i) \cdot x(j-n+i)$
The spreadsheet function $$AVERAGE()$$ performs this computation in the particular case where all $$w(i)$$ are equal to one. In this case, it is very easy to deal with missing values, as the only thing you need to do is count the number of non-missing values and divide the sum by this count.

When the $$w(i)$$'s are different numbers, it is not enough to count the number of non-missing values, one needs to perform the final normalization using the sum of the correspondent weight coefficients.

## Proposed Solution

The proposed solution is to use the function $$SUMPRODUCT()$$ twice, first to get the weighted sum, and second to get the sum of the coefficients that have multiplied non-missing data.

Assume that:
• The spreadsheet has a page called "Filter1".
• The averaging coefficients are on column Filter1!A.
• Cell Filter1!B1 has the formula "COUNT(A:A)", which will count the number of averaging coefficients.
• The current cell is B22.
Then the claim is that the following formula will calculate the correct value of WMA:
$\begin{array}{l} =\\ SUMPRODUCT( \\ \qquad OFFSET(B22, -Filter1!B1 + 1, 0, Filter1!B1, 1), \\ \qquad OFFSET(Filter1!A1, 0, 0, Filter1!B1, 1)) \\ / \\ SUMPRODUCT(\\ \qquad ARRAYFORMULA(\\ \qquad \qquad N(ISNUMBER(\\ \qquad \qquad \qquad OFFSET(B22, -Filter1!B1 + 1, 0, Filter1!B1, 1)))), \\ \qquad OFFSET(Filter1!A1, 0, 0, Filter1!B1, 1)) \end {array}$
The details of this expression are as follows:
• $$Filter1!B1$$ is $$n$$.
• $$OFFSET(current\_cell,−n+1,0,n,1)$$ is used to produce a range of n cells, of which the current cell is the last one.
• $$OFFSET(Filter1!A1, 0, 0, Filter1!B1, 1)$$ are the weighting coefficients.
• $$ARRAYFORMULA(N(ISNUMBER(OFFSET(\cdots))))$$ applies the function $$N()$$ to the boolean result of $$ISNUMBER()$$ for each cell in the current range, which will produce an array of zeroes where there is missing data and ones where there is data.

## Results

The formula has been tested against some weighting data. The resulting spreadsheet has a plot of the original data, along with the $$AVERAGE()$$ data and WMA data for comparison.

## Conclusion

A spreadsheet formula for the correct calculation of a weighted moving average has been derived and successfully tested on Google Sheets. The proposed formula deals with missing values in a way similar to the $$AVERAGE()$$ function, avoiding the distortions that would be caused either by using zero in place of the missing values or by packing the original series.

## Abstract

Exponential moving averages (EMA) is a way to remove noise from data series. Unfortunately, useful straightforward spreadsheet support for EMA is absent. This article examines the problems involved and proposes a one line formula solution to add EMA to a spreadsheet.

## Introduction

Traditionally, implementing an exponential moving average has been done in spreadsheets using a recursive formula, i.e., an auto-regressive (AR) process or an infinite impulse response (IIR) filter. The formula is the following:
$y(n) = \alpha \cdot x(n) + (1 - \alpha) \cdot y(n-1)$
There are some problems with this approach:
1. Strictly speaking, this is not a moving average. Moving average assumes a finite length sliding window under which data is being weighted. There is a growing window, not a sliding window.
2. The last item implies that the average takes into account every single data sample. The previous formula actually implements an IIR filter. This kind of averaging never forgets a value, although old values certainly get irrelevant over time. It would be nice to have control over the window where the exponential average takes place, especially when one intends to use a small window.
3. It does not deal properly with missing values. In a spreadsheet, it is common to have missing data values and the previous approach does not allow them. Converting missing values to zero would unacceptably distort the average value. If one insists in applying this formula to the series by packing the original series, the weights applied to the values in the averaging process will not reflect the actual distance in time that these samples might have.
The function AVERAGE(range) is able to deal with this problem quite simply because it uses the same average on every sample, so it is just a matter of dividing the sum by the number of non-blank entries. In a non-uniform averaging like EMA, we need to keep track of which weights were really applied or not due to missing values, and fix the normalizing factor accordingly.

## Proposed solution

The function SERIESSUM(a, n, m, x) is defined as
$SERIESSUM(a, n, m, x) = \sum_{i=0}^n x_i a^{n+m i}.$
The proposed solution is to use this function twice, first to calculate the weighted sum, and then a second time to calculate the sum of the weights where data is not missing.
Assume that:
• Cell F1 contains the geometric progression ratio $$(\alpha)$$;
• Cell F2 contains the window size $$(n)$$;
• Column B contains the raw data;
• The current cell is B22.
Then the claim is that the following formula will calculate the correct value of EMA:
$\begin{array}{l} =\\ SERIESSUM(F1, F2, -1, \\ \qquad ARRAYFORMULA(N(OFFSET(B22, -F2 + 1, 0, F2, 1))))\\ / \\ SERIESSUM(F1, F2, -1, \\ \qquad ARRAYFORMULA(\\ \qquad \qquad N(ISNUMBER(OFFSET(B23, -F2 + 1, 0, F2, 1))))) \end{array}$
The details of this expression are as follows:
• $$OFFSET(current\_cell, -n+1, 0, n, 1)$$ is used to produce a range of $$n$$ cells, of which the current cell is the last one.
• $$ARRAYFORMULA(N(OFFSET(\dotsc)))$$ will apply the $$N()$$ function to each element of the argument range to generate a new range with zero values in the missing data cells. Without this trick, $$SERIESSUM()$$ would use non-missing values as if they were contiguous.
• $$ARRAYFORMULA(N(ISNUMBER(OFFSET(\dotsc))))$$ will generate a range composed of ones where data is not missing and zeros where data is missing.

## Results

The formula has been tested against some weighting data. The resulting spreadsheet has a plot of the original data, along with the $$AVERAGE()$$ data and EMA data for comparison.

## Conclusion

A spreadsheet formula for the correct calculation of an exponential moving average has been derived and successfully tested on Google Sheets. The proposed formula deals with missing values in a way similar to the $$AVERAGE()$$ function, avoiding the distortions that would be caused either by using zero in place of the missing values or by packing the original series.