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.

Version 1.1

Released January 21, 2017.

Source code

Source SHA256: 6d7dae5aba3c7be630a5d7d6c41b8aba196ab2811d4492824fe433c356f0d539

Source PGP signature

Library

Library SHA256: 3d0852dacbd59397f049274f7c4c77795e707bc8c34d75154b8ae5ab81160405

Library PGP signature

Demo spreadsheet

Spreadsheet SHA256: 5b071dbb988fb8f9c16652c7ffc9c1c4799b076100b3fb1b981e6e596ac7669b

Spreadsheet PGP signature

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.

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

Filtered signal

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.

Spectrum tab

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.

Waveform tab

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

From Andrew Que (http://www.DrQue.net)

Middleton, WI

January 04, 2017 at 7:51 PM

Leave a comment if you find this library useful.

From Johannes (http://jolorenz.wordpress.com)

Munich, Bangkok

January 18, 2017 at 10:14 AM

Hi Andrew,

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

Yours Johannes

From Ed

Michigan, USA

January 27, 2017 at 7:19 PM

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

From Kristina

Orleans, France

April 25, 2017 at 5:54 AM

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

From Evaristo

Spain

July 10, 2017 at 4:15 PM

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

From Erwin

Amsterdam

January 18, 2018 at 4:58 AM

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.

From Jason (http://voat.co/u/fusir)

The Internet

April 03, 2018 at 10:07 AM

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.

From Thomas

Maine

August 07, 2018 at 5:03 PM

Has anyone tried Bluestein? after selecting columns of real and img it's asks for "additional parameters" what am I missing?

From Anonymous

California

September 07, 2018 at 5:49 PM

Better than the equivalent built into Excel because it is live

Well done

Thank you

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.