FFT4loc is a Fast Fourier Transform macro for LibreOffice Calc. It has the ability to do a discrete Fourier transform (DFT), both forward and inverse, an a data set of arbitrary size. In addition there is a spectrum function that will produce magnitude and phase data for each frequency of a sample set.

This set of macros was written after a number of years unsuccessfully searching for a discrete Fourier transform function that be used in LibreOffice Calc. There are often times I have a set of data for which I would like to have a spectrum plot. For this a discrete Fourier transform is required.

This library will likely not be maintained because its uses are so limited.

Archives signed by Andrew Que.
Keys are generated yearly and can be found on the MIT PGP Public Key Server by doing a
search for *Andrew Que*. They can also be downloaded directly
from DrQue.net.

Released January 21, 2017.

- Added
*waveform*function. -
Changed
*Integer*to*Long*. Fix to allow transforms on data sets larger than 4096 data points.

Source SHA256: 6d7dae5aba3c7be630a5d7d6c41b8aba196ab2811d4492824fe433c356f0d539

Library SHA256: 3d0852dacbd59397f049274f7c4c77795e707bc8c34d75154b8ae5ab81160405

Spreadsheet SHA256: 5b071dbb988fb8f9c16652c7ffc9c1c4799b076100b3fb1b981e6e596ac7669b

Released January 7, 2017.

Source SHA256: 78ffb068302c990a9206677d5a7b16e8de8920e0912ec9c9d9b5d53446b68a84

Library SHA256: 414f2ed47427c30a77aa1b72a812efc6c9870a2a6ede85c3e6b88ce04faddc5b

Spreadsheet SHA256: edc1d67e2a3308f7965cb583478f6e2e2f2f520bf3c982747166cca6ffa5deda

Released January 4, 2017.

Source SHA256: 7d49cb4ca0bfdebb21e5317ba3740e38e558cb9fc23c62abcb46e67e66c972a4

Library SHA256: edc1d67e2a3308f7965cb583478f6e2e2f2f520bf3c982747166cca6ffa5deda

Spreadsheet SHA256: 2f7e0f6a6db8f8be20a4e5be863c1a482797d9099672d6be558a83b90c3d7cfd

First, the macro library must be imported into LibreOffice. Both the raw ooBasic file and the exported library are available. Below are the instructions for installing the library.

- Open LibreOffice Calc.
- Download library.
- Uncompress library.
- Navigate to "Organize Macros" by clicking on Tools-> Macros-> Organize Macros-> OpenOffice.org Basic.
- Click on "Organizer".
- Select the "Libraries" tab.
- Click "Import" button.
- Navigate to the location where the download file is extracted and select
*script.xlb*. - Accept the defaults and click "OK".
- Select "Modules" tab.
- Under My Macros -> FFT4loc there should be a module called "FFT".
- Drag the FFT module from FFT4loc into (or just below) Standard.
- Installation is complete. Close all open dialog boxes.

The functions of the library should now be available in Calc. You can

The macro functions provided by this library run like any other function in LibreOffice Calc. Use an equal sign followed by the function name, a parenthesis, and a range of data.

All functions return an array of data, so be sure to hit "Ctrl-Enter" after entering the formula or the results are not very useful. The data columns are always entered and return as real values in the first column, and imaginary values in the second.

The following functions are available:

- FFT - Fast Fourier transform on a range of cells.
- iFFT - Inverse fast Fourier transform on a range of cells.
- spectrum - Magnitude and phase for a range of cells.

The FFT and iFFT functions perform the forward and inverse fast Fourier transform respectively. They take a single parameter which is the range of cells containing the data. A forward transform can be done on real- only data by simply selecting a single column. Two columns implies complex data, with the first column being the real values, and the second column being the imaginary values. Inverse transforms require both real and imaginary data.

=fft( A1:B100 )

This line will do a forward fast Fourier transform on complex data (i.e. real and imaginary) over 100 cells starting at A1 and though B100. Note that the transform does not require that there be a power- of-two data size, although such data sizes will run faster.

=fft( A1:A100 )

This line will do a forward fast Fourier transform on real-only data because only a single column was selected.

The spectrum function will produce a list of frequency magnitudes and their phase. The row index is the frequency, starting at 0 (DC) and running to the Nyquist rate (one half the number of samples). Note that the DC values have no phase and the Nyquist no amplitude. Magnitudes are are normalized (i.e. divided by the number of total samples). Phase is given in degrees (default) or radians. Input may have a single column for real-only data, or two columns for complex data.

The spectrum is useful for making plots. One may consider enumerating the frequencies in the proceeding column as labels for the plot.

=spectrum( A1:A100 )

Produce a spectrum of 50 points over the real-only data in A1 through A100.

=spectrum( A1:A100, true )

Same as above, except the phase is given in radians rather then degrees.

=spectrum( A1:A100, true, 1e-8 )

This will request a spectrum where phase is given at 0 if the total magnitude is less than 1e-8 (0.00000001). There is always a small amount of rounding error when doing an FFT and this will prevent phase from being displayed for basically non-existent frequencies.

Included in the downloads section is a demo spreadsheet. It contains five sheets.

This sheet transforms a random signal into the frequency domain, and back again. The difference between the transform results against the original signal is taken and the largest discrepancy displayed. The error for a 512 byte signal is around 2.2e-16.

The filter sheet shows a noisy signal with three signals. A simple bandpass filter can be configured to extract just the desired waveforms with the results graphed.

This is an example of the graph. It shows the original signal in blue, the samples actually transformed in orange and the filtered signal in green.

The spectrum sheet displays the spectrum of a square wave. The Gibbs Phenomenon is clearly demonstrated with each odd frequency being 1/3 the magnitude of the previous.

The spectrum error sheet takes the spectrum of the same square wave but
over 1024 samples. The *Amplitude* column is the measured
amplitude, and the *Expected* the value that should occur due to
the phenomenon. Because the limited sample size there will be an error
in the accuracy magnitude. This error should be less than the inverse
of the sample size (1/1024).

The phase sheet uses the spectrum to extract magnitude and phase of a single sine wave with random frequency, amplitude, and phase. This is compared against the expected value and the error displayed. This error is typically very small (around 1e-12) and worst around the highest frequencies.

The waveform function will combine a list of sine waves together to form a signal. This can be useful for experimentation when trying to construct base signals.

=waveform( [range], [count] )

The range must be three columns and any number of rows. The first column specifies frequency, then amplitude, and phase (in degrees). The count is the number of data points in a single cycle. Generation actually produces count+1 data points which is nice for graphing. When taking an FFT, be sure not to include the last point because it is actually from the next period.

This shows the waveform function demonstrating the Gibbs
Phenomenon using 32 harmonics, a frequency of 2, amplitude of 3, and
phase of 45°. The top chart shows a plot of the data generated by
*waveform*. The bottom chart is the spectrum of that data.

LibreOffice Calc and ooBasic are not designed for the kind of computation needed by FFTs and are quite slow. On my development computer the following times were observed:

Points | Power | Seconds |
---|---|---|

4096 | 2^{12} |
7 |

16384 | 2^{14} |
31 |

65536 | 2^{16} |
140 |

4099 | Prime | 90 |

For smaller data sets this library is still usable, but better tools are needed for larger data sets.

Leave a comment if you find this library useful.

Hi Andrew,

thank you so much for your FFT library, a long missed feature of Open Office.

Yours Johannes

Thanks for this great contribution!!! Job well done!

Merci, thank you very much! It helps a lot.

Very appreciate work. Useful to keep stayin apart from Excel.

I need to press ctrl-shift-enter after entering the macro, e.g. after =ftt(A1:A100)

I just post it here, because it took me some tries to find this out.

I find that it only gives me one decimal value instead of an array. Pressing ctrl-enter or ctrl-shift-enter does nothing.

Has anyone else had this issue.

FFT4loc is is free, open-source software released under the MIT license.

FFT4loc is written and maintained by Andrew Que. To get in touch with Andrew Que, visit his contact page. The FFT algorithms were translated to ooBasic from the C library Free small FFT written by Project Nayuki.

(C) Copyright 2017 by Andrew Que.