Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Add-in = Fourier Analysis
I want to analyze 36 months of historical sales figures to see if I can
identify any time-series that will allow me to project them into the future. My rusty math memory tells me that I can use Fourier Analysis to find out the underlying frequencies and amplitudes. The Fourier Analysis add-in - plus what I have read on the web - tells me that the number of data points that I use must be a power of 2, so my choices are to use 2, 4, 8, 16, or 32 months of data (I use 32). I have also learned that the frequencies reported by the Add-in are listed in ascending multiples of 1/(32 months). From this I can calculate the value of each frequency returned by the Add-in. What I don't yet know how to do with the output from Fourier Analysis is: 1. How do I interpret the number reported for each frequency? I assume that each one is a measure of the amplitude of the reported frequency, but I do not know how to handle the 'real' and 'imaginary' components of the number. At first blush, I think I would ignore the imaginary, but ... 2. Once I have the frequencies and the amplitudes of each component waveform, I assume that I select to use only the more significant (higher amplitude) frequencies. Help on this selection process will be appreciated. 3. To make my projection, how do I make sure that I am applying the waveforms at the correct part of their cycle. For example, I could start all of the waveforms at t=0, but that is not necessarily where the cycle should start. Overall, it seems that I am looking for the equation for the full waveform that Fourier Analysis is calculating; so that if I insert the output of the FA into it, I will get the equation for the projected waveform. Thanks for your help. Mike |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Add-in = Fourier Analysis
On Sat, 17 Jan 2009 07:01:00 -0800, MichaelRobert
wrote: I want to analyze 36 months of historical sales figures to see if I can identify any time-series that will allow me to project them into the future. My rusty math memory tells me that I can use Fourier Analysis to find out the underlying frequencies and amplitudes. The Fourier Analysis add-in - plus what I have read on the web - tells me that the number of data points that I use must be a power of 2, so my choices are to use 2, 4, 8, 16, or 32 months of data (I use 32). I have also learned that the frequencies reported by the Add-in are listed in ascending multiples of 1/(32 months). From this I can calculate the value of each frequency returned by the Add-in. What I don't yet know how to do with the output from Fourier Analysis is: 1. How do I interpret the number reported for each frequency? I assume that each one is a measure of the amplitude of the reported frequency, but I do not know how to handle the 'real' and 'imaginary' components of the number. At first blush, I think I would ignore the imaginary, but ... 2. Once I have the frequencies and the amplitudes of each component waveform, I assume that I select to use only the more significant (higher amplitude) frequencies. Help on this selection process will be appreciated. 3. To make my projection, how do I make sure that I am applying the waveforms at the correct part of their cycle. For example, I could start all of the waveforms at t=0, but that is not necessarily where the cycle should start. Overall, it seems that I am looking for the equation for the full waveform that Fourier Analysis is calculating; so that if I insert the output of the FA into it, I will get the equation for the projected waveform. Thanks for your help. Mike For the amplitude you have to use both the real and the imaginary part. Use the IMABS function for this For the phase you also have to use both the real and the imaginary part. Use the IMARGUMENT function for this The more of the component that you use, the more accurate/detailed the projection will be. But do remember that future always bring in new facts, in addition to historical data, that will influence your sales. Hope this helps / Lars-Åke |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Add-in = Fourier Analysis
Hi,
Time series are often analysed using Box Jenkins, which Excel does not have. However, you might look at http://www.jaxworks.com/toc.htm or various other sites. -- If this helps, please click the Yes button Cheers, Shane Devenshire "MichaelRobert" wrote: I want to analyze 36 months of historical sales figures to see if I can identify any time-series that will allow me to project them into the future. My rusty math memory tells me that I can use Fourier Analysis to find out the underlying frequencies and amplitudes. The Fourier Analysis add-in - plus what I have read on the web - tells me that the number of data points that I use must be a power of 2, so my choices are to use 2, 4, 8, 16, or 32 months of data (I use 32). I have also learned that the frequencies reported by the Add-in are listed in ascending multiples of 1/(32 months). From this I can calculate the value of each frequency returned by the Add-in. What I don't yet know how to do with the output from Fourier Analysis is: 1. How do I interpret the number reported for each frequency? I assume that each one is a measure of the amplitude of the reported frequency, but I do not know how to handle the 'real' and 'imaginary' components of the number. At first blush, I think I would ignore the imaginary, but ... 2. Once I have the frequencies and the amplitudes of each component waveform, I assume that I select to use only the more significant (higher amplitude) frequencies. Help on this selection process will be appreciated. 3. To make my projection, how do I make sure that I am applying the waveforms at the correct part of their cycle. For example, I could start all of the waveforms at t=0, but that is not necessarily where the cycle should start. Overall, it seems that I am looking for the equation for the full waveform that Fourier Analysis is calculating; so that if I insert the output of the FA into it, I will get the equation for the projected waveform. Thanks for your help. Mike |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Add-in = Fourier Analysis
Lars-Ake:
Thanks for the ideas. How/where would I apply the IMABS and IMARGUMENT functions? Mike "Lars-Ã…ke Aspelin" wrote: On Sat, 17 Jan 2009 07:01:00 -0800, MichaelRobert wrote: I want to analyze 36 months of historical sales figures to see if I can identify any time-series that will allow me to project them into the future. My rusty math memory tells me that I can use Fourier Analysis to find out the underlying frequencies and amplitudes. The Fourier Analysis add-in - plus what I have read on the web - tells me that the number of data points that I use must be a power of 2, so my choices are to use 2, 4, 8, 16, or 32 months of data (I use 32). I have also learned that the frequencies reported by the Add-in are listed in ascending multiples of 1/(32 months). From this I can calculate the value of each frequency returned by the Add-in. What I don't yet know how to do with the output from Fourier Analysis is: 1. How do I interpret the number reported for each frequency? I assume that each one is a measure of the amplitude of the reported frequency, but I do not know how to handle the 'real' and 'imaginary' components of the number. At first blush, I think I would ignore the imaginary, but ... 2. Once I have the frequencies and the amplitudes of each component waveform, I assume that I select to use only the more significant (higher amplitude) frequencies. Help on this selection process will be appreciated. 3. To make my projection, how do I make sure that I am applying the waveforms at the correct part of their cycle. For example, I could start all of the waveforms at t=0, but that is not necessarily where the cycle should start. Overall, it seems that I am looking for the equation for the full waveform that Fourier Analysis is calculating; so that if I insert the output of the FA into it, I will get the equation for the projected waveform. Thanks for your help. Mike For the amplitude you have to use both the real and the imaginary part. Use the IMABS function for this For the phase you also have to use both the real and the imaginary part. Use the IMARGUMENT function for this The more of the component that you use, the more accurate/detailed the projection will be. But do remember that future always bring in new facts, in addition to historical data, that will influence your sales. Hope this helps / Lars-Ã…ke |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Add-in = Fourier Analysis
Lets say you have your input data in A1:A32 and your output data in
B1:B32 In cell C1 you put =IMABS(B1) In cell D1 you put =IMARGUMENT(B1) Copy C1:D1 down thru C32:D32 In cell E1 you put =1/32*SUMPRODUCT(C$1:C$32*COS((ROW()-1)*(ROW($1:$32)-1)/16*PI()+(D$1:D$32))) Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER Copy cell E1 down thru E32, and copy it further down ot get your projection of future sales. Here is an example with a "square wave" input. A1:A8 = 1, A9:A16 = 0, A17:A24 = 1, A25:A32 = 0 You will get the following Fourier components B1 = 16 B2 = 0 B3 = 2-10.05...i B4,B5.B6 = 0 B7 = 2-2.993...i B8, B9, B10 = 0 B11 = 2-1.336...i B12, B13, B14 = 0 B15 = 2-0.397...i B16 = 0 B17 = 0 B18-B32 are same as B16:B2 but with the opposite sign of the imaginary part (the complex conjugate) You will get the following amplitudes: C1 = 16 C3 = 10.25166 C7 = 3.599905 C11 = 2.40538 C15 = 2.039182 C2, C4, C5, C6, C8, C9, C10, C12, C13, C14, C16, C17 = 0 C18:C32 are same as C16:C2 You will get the following "phases": D3 = -1.37445 D7 = -0.98175 D11 = -0.58905 D15 = -0.19635 D1, D2, D4, D5, D6, D8, D9, D10, D12, D13, D14, D16, D17 = 0 D18:D32 are the same as D16:D2 In column E, the restored data, you will get the same as in column A except some minor rounding errors. If not, check the formulas. If you have all this setup and checked you can change your input data in A1:A32 and make a new Fourier transform to get the data in B1:B32. Hope this helps / Lars-Åke On Sat, 17 Jan 2009 14:06:01 -0800, MichaelRobert wrote: Lars-Ake: Thanks for the ideas. How/where would I apply the IMABS and IMARGUMENT functions? Mike "Lars-Åke Aspelin" wrote: On Sat, 17 Jan 2009 07:01:00 -0800, MichaelRobert wrote: I want to analyze 36 months of historical sales figures to see if I can identify any time-series that will allow me to project them into the future. My rusty math memory tells me that I can use Fourier Analysis to find out the underlying frequencies and amplitudes. The Fourier Analysis add-in - plus what I have read on the web - tells me that the number of data points that I use must be a power of 2, so my choices are to use 2, 4, 8, 16, or 32 months of data (I use 32). I have also learned that the frequencies reported by the Add-in are listed in ascending multiples of 1/(32 months). From this I can calculate the value of each frequency returned by the Add-in. What I don't yet know how to do with the output from Fourier Analysis is: 1. How do I interpret the number reported for each frequency? I assume that each one is a measure of the amplitude of the reported frequency, but I do not know how to handle the 'real' and 'imaginary' components of the number. At first blush, I think I would ignore the imaginary, but ... 2. Once I have the frequencies and the amplitudes of each component waveform, I assume that I select to use only the more significant (higher amplitude) frequencies. Help on this selection process will be appreciated. 3. To make my projection, how do I make sure that I am applying the waveforms at the correct part of their cycle. For example, I could start all of the waveforms at t=0, but that is not necessarily where the cycle should start. Overall, it seems that I am looking for the equation for the full waveform that Fourier Analysis is calculating; so that if I insert the output of the FA into it, I will get the equation for the projected waveform. Thanks for your help. Mike For the amplitude you have to use both the real and the imaginary part. Use the IMABS function for this For the phase you also have to use both the real and the imaginary part. Use the IMARGUMENT function for this The more of the component that you use, the more accurate/detailed the projection will be. But do remember that future always bring in new facts, in addition to historical data, that will influence your sales. Hope this helps / Lars-Åke |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Add-in = Fourier Analysis
Excel 2007
Using free online curve fitting. No Fourier Analysis required. Example fits to y=a*sin(x+b)+c http://www.mediafire.com/file/ojzioznn2fu/Cyclic.xlsx |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Add-in = Fourier Analysis
Lars-Ake:
Many thanks. I can see my way forward now! It will be a few days before I can get back into this project. I'll let you know how it works out. Thanks again. Mike "Lars-Ã…ke Aspelin" wrote: Lets say you have your input data in A1:A32 and your output data in B1:B32 In cell C1 you put =IMABS(B1) In cell D1 you put =IMARGUMENT(B1) Copy C1:D1 down thru C32:D32 In cell E1 you put =1/32*SUMPRODUCT(C$1:C$32*COS((ROW()-1)*(ROW($1:$32)-1)/16*PI()+(D$1:D$32))) Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER Copy cell E1 down thru E32, and copy it further down ot get your projection of future sales. Here is an example with a "square wave" input. A1:A8 = 1, A9:A16 = 0, A17:A24 = 1, A25:A32 = 0 You will get the following Fourier components B1 = 16 B2 = 0 B3 = 2-10.05...i B4,B5.B6 = 0 B7 = 2-2.993...i B8, B9, B10 = 0 B11 = 2-1.336...i B12, B13, B14 = 0 B15 = 2-0.397...i B16 = 0 B17 = 0 B18-B32 are same as B16:B2 but with the opposite sign of the imaginary part (the complex conjugate) You will get the following amplitudes: C1 = 16 C3 = 10.25166 C7 = 3.599905 C11 = 2.40538 C15 = 2.039182 C2, C4, C5, C6, C8, C9, C10, C12, C13, C14, C16, C17 = 0 C18:C32 are same as C16:C2 You will get the following "phases": D3 = -1.37445 D7 = -0.98175 D11 = -0.58905 D15 = -0.19635 D1, D2, D4, D5, D6, D8, D9, D10, D12, D13, D14, D16, D17 = 0 D18:D32 are the same as D16:D2 In column E, the restored data, you will get the same as in column A except some minor rounding errors. If not, check the formulas. If you have all this setup and checked you can change your input data in A1:A32 and make a new Fourier transform to get the data in B1:B32. Hope this helps / Lars-Ã…ke On Sat, 17 Jan 2009 14:06:01 -0800, MichaelRobert wrote: Lars-Ake: Thanks for the ideas. How/where would I apply the IMABS and IMARGUMENT functions? Mike "Lars-Ã…ke Aspelin" wrote: On Sat, 17 Jan 2009 07:01:00 -0800, MichaelRobert wrote: I want to analyze 36 months of historical sales figures to see if I can identify any time-series that will allow me to project them into the future. My rusty math memory tells me that I can use Fourier Analysis to find out the underlying frequencies and amplitudes. The Fourier Analysis add-in - plus what I have read on the web - tells me that the number of data points that I use must be a power of 2, so my choices are to use 2, 4, 8, 16, or 32 months of data (I use 32). I have also learned that the frequencies reported by the Add-in are listed in ascending multiples of 1/(32 months). From this I can calculate the value of each frequency returned by the Add-in. What I don't yet know how to do with the output from Fourier Analysis is: 1. How do I interpret the number reported for each frequency? I assume that each one is a measure of the amplitude of the reported frequency, but I do not know how to handle the 'real' and 'imaginary' components of the number. At first blush, I think I would ignore the imaginary, but ... 2. Once I have the frequencies and the amplitudes of each component waveform, I assume that I select to use only the more significant (higher amplitude) frequencies. Help on this selection process will be appreciated. 3. To make my projection, how do I make sure that I am applying the waveforms at the correct part of their cycle. For example, I could start all of the waveforms at t=0, but that is not necessarily where the cycle should start. Overall, it seems that I am looking for the equation for the full waveform that Fourier Analysis is calculating; so that if I insert the output of the FA into it, I will get the equation for the projected waveform. Thanks for your help. Mike For the amplitude you have to use both the real and the imaginary part. Use the IMABS function for this For the phase you also have to use both the real and the imaginary part. Use the IMARGUMENT function for this The more of the component that you use, the more accurate/detailed the projection will be. But do remember that future always bring in new facts, in addition to historical data, that will influence your sales. Hope this helps / Lars-Ã…ke |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Add-in = Fourier Analysis
Herbert:
Thanks for the link. And for adding the series equation. Mike "Herbert Seidenberg" wrote: Excel 2007 Using free online curve fitting. No Fourier Analysis required. Example fits to y=a*sin(x+b)+c http://www.mediafire.com/file/ojzioznn2fu/Cyclic.xlsx |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Add-in = Fourier Analysis
I want to analyze 36 months of historical sales figures to see if I can
identify any time-series that will allow me to project them into the future. Hi. Just some thoughts. Fourier Analysis assumes your data is periodic, so future projections would just be a repeat of your data. It will not be able to show growth, or something similar, into the future. The first output of Fourier is the zero frequency. The second line is frequency 1, third line is frequency 2, etc. The reason the first output is a real number is that that Sin( 0 ) is zero, and cancels out the imaginary part. Same for line 17 in the output, which is frequency 16. Sin( (2 Pi/32)*16*x), reduces to Sin(Pi x) and is always zero, hence line 17 is always real. On your part..."to see if I can identify any time-series.." Another option is to do a poor-mans version of a spectrum analyzer. =IMABS(B1) and copy down to Row 17. We do not need to look at the complex conjugates that are further down. Make the small adjustment here to divide by 32 in Row 1 and 17, and divide by 16 in rows 2-16. (2*x / 32 - x / 16) Then do something like a bar chart on this data. What this shows is the relative contribution of each frequency. Suppose we had 36 data points instead of 32 here to keep the math simple. Divide 36 by a few of our frequencies, say 1-12 {36., 18., 12., 9., 7.2, 6., 5.14, 4.5, 4., 3.6, 3.27, 3.} The first row in our output is just our average sales, and should have a value. If we had a large value in Row 2(freq 1) compared to the other values, then this would indicate a strong presence of a 36 month sales cycle. All other values close to 0 would be considered "noise" and would likely be dropped from the equation when reverted back into the time domain. Suppose row 4 was larger then others (freq 3), then this would indicate a strong presence of a 36/3 = 12 month sales cycle. Anyway, hope this helps a little. = = = HTH Dana DeLouis MichaelRobert wrote: I want to analyze 36 months of historical sales figures to see if I can identify any time-series that will allow me to project them into the future. My rusty math memory tells me that I can use Fourier Analysis to find out the underlying frequencies and amplitudes. The Fourier Analysis add-in - plus what I have read on the web - tells me that the number of data points that I use must be a power of 2, so my choices are to use 2, 4, 8, 16, or 32 months of data (I use 32). I have also learned that the frequencies reported by the Add-in are listed in ascending multiples of 1/(32 months). From this I can calculate the value of each frequency returned by the Add-in. What I don't yet know how to do with the output from Fourier Analysis is: 1. How do I interpret the number reported for each frequency? I assume that each one is a measure of the amplitude of the reported frequency, but I do not know how to handle the 'real' and 'imaginary' components of the number. At first blush, I think I would ignore the imaginary, but ... 2. Once I have the frequencies and the amplitudes of each component waveform, I assume that I select to use only the more significant (higher amplitude) frequencies. Help on this selection process will be appreciated. 3. To make my projection, how do I make sure that I am applying the waveforms at the correct part of their cycle. For example, I could start all of the waveforms at t=0, but that is not necessarily where the cycle should start. Overall, it seems that I am looking for the equation for the full waveform that Fourier Analysis is calculating; so that if I insert the output of the FA into it, I will get the equation for the projected waveform. Thanks for your help. Mike |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Add-in = Fourier Analysis
Just a side note if you were confused with the output.
There are a few different definitions of the Fourier equation depending on what one is working on. The equation Microsoft uses is more appropriate for analyzing signals and such, and I believe can cause a little confusion when listing it under "data analysis" (in my opinion). That is why we have to divide by the size of the data to convert Excel's output to a format to do "Data Analysis". I like it the way it is, but thought I would mention it. The program for Fourier is basically the same. It's just a matter of setting a flag to adjust the output during computations. = = = Dana DeLouis <snip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fourier Analysis in Excel | Excel Discussion (Misc queries) | |||
Fourier Analysis | Excel Worksheet Functions | |||
Fourier Analysis | Excel Worksheet Functions | |||
Fourier analysis. | Excel Worksheet Functions |