Deprecated

Starting in LibreOffice version 6.3, calc has had the function FOURIER. This library is no longer necessary to get a Fourier transform.

About

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.

Download

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.

• Public key for 2017.

Version 1.1

Released January 21, 2017.

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

Archived versions

Click here to expand the archived version section.

Manual

Installation

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

Usage

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.

FFT/iFFT

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.

Spectrum

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.

Demo Spreadsheet

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

Accuracy

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.

Filter

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.

Spectrum/Spectrum Error

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).

Phase

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.

Waveform

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.

Performance

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	212	7
16384	214	31
65536	216	140
4099	Prime	90

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

User comments

Copyright

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

Author

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.