最近,在尝试使用matlab+excel+数据库做金融策略code,
为什么这样做,主要有两点:
1.使用者对excel比较熟悉,一般的金融从业者大多喜欢用excel。
2.matlab的强大数值计算功能可以提供后台计算。
我相信,两个完美结合会有不错的结果。可以使用的方法
有excelink,matlab excel build
我还做的一个excelink的视频教程,具体参见http://baoming.pinggu.org/Default.aspx?id=19
现在目标如何根据需求做的更参数化,自动化。
-----Ariszheng
matlab excel build Spectral Analysis Example
On this page… |
---|
Overview
This example illustrates the creation of a comprehensive Excel® add-in to perform spectral analysis. It requires knowledge of Visual Basic® forms and controls, as well as Excel workbook events. See the VBA documentation for a complete discussion of these topics.
The example creates an Excel add-in that performs a fast Fourier transform (FFT) on an input data set located in a designated worksheet range. The function returns the FFT results, an array of frequency points, and the power spectral density of the input data. It places these results into ranges you indicate in the current worksheet. You can also optionally plot the power spectral density.
You develop the function so that you can invoke it from the Excel Tools menu and can select input and output ranges through a GUI.
Creating the add-in requires four basic steps:
-
Implement the necessary VBA code to collect input and dispatch the calls to your component.
-
Create an Excel add-in and package all necessary components for application deployment.
Building the Component
Your component will have one class with two methods, computefft and plotfft. The computefft method computes the FFT and power spectral density of the input data and computes a vector of frequency points based on the length of the data entered and the sampling interval. The plotfft method performs the same operations as computefft, but also plots the input data and the power spectral density in a MATLAB Figure window. The MATLAB code for these two methods resides in two M-files, computefft.m and plotfft.m.
computefft.m: function [fftdata, freq, powerspect] = computefft(data, interval) if (isempty(data)) fftdata = []; freq = []; powerspect = []; return; end if (interval <= 0) error('Sampling interval must be greater then zero'); return; end fftdata = fft(data); freq = (0:length(fftdata)-1)/(length(fftdata)*interval); powerspect = abs(fftdata)/(sqrt(length(fftdata))); plotfft.m: function [fftdata, freq, powerspect] = plotfft(data, interval) [fftdata, freq, powerspect] = computefft(data, interval); len = length(fftdata); if (len <= 0) return; end t = 0:interval:(len-1)*interval; subplot(2,1,1), plot(t, data) xlabel('Time'), grid on title('Time domain signal') subplot(2,1,2), plot(freq(1:len/2), powerspect(1:len/2)) xlabel('Frequency (Hz)'), grid on title('Power spectral density')
To proceed with the actual building of the component, follow these steps:
-
If you have not already done so, execute the following command in the MATLAB product:
mbuild -setup
Be sure to choose a supported compiler. See Supported Compilers.
-
Create a project with the following settings:
Setting Value Component name Fourier Class name Fourier Project directory The name of your work directory followed by the component name. Show verbose output Selected -
Add the computefft.m and plotfft.m M-files to the project.
-
Save the project. Make note of the project directory because you will refer to it later when you save your add-in.
-
Build the component by clicking the Build button in the Deployment Tool toolbar.
Integrating the Component Using VBA
Having built your component, you can implement the necessary VBA code to integrate it into Excel.
Selecting the Libraries
Follow these steps to open Excel and select the libraries you need to develop the add-in:
-
Start Excel on your system.
-
From the Excel main menu, click Tools > Macro > Visual Basic Editor.
-
When the Visual Basic Editor starts, click Tools > References to open the Project References dialog box.
-
Select Fourier 1.0 Type Library and MWComUtil 7.5 Type Library from the list.
Creating the Main VB Code Module for the Application. The add-in requires some initialization code and some global variables to hold the application's state between function invocations. To achieve this, implement a Visual Basic code module to manage these tasks, as follows:
-
Right-click the VBAProject item in the project window and click Insert > Module.
A new module appears under Modules in the VBA Project.
-
In the module's property page, set the Name property to FourierMain. See the next figure.
-
Enter the following code in the FourierMain module:
' ' FourierMain - Main module stores global state of controls ' and provides initialization code ' Public theFourier As Fourier.Fourierclass 'Global instance of Fourier object Public theFFTData As MWComplex 'Global instance of MWComplex to accept FFT Public InputData As Range 'Input data range Public Interval As Double 'Sampling interval Public Frequency As Range 'Output frequency data range Public PowerSpect As Range 'Output power spectral density range Public bPlot As Boolean 'Holds the state of plot flag Public theUtil As MWUtil 'Global instance of MWUtil object Public bInitialized As Boolean 'Module-is-initialized flag Private Sub LoadFourier() 'Initializes globals and Loads the Spectral Analysis form Dim MainForm As frmFourier On Error GoTo Handle_Error Call InitApp Set MainForm = New frmFourier Call MainForm.Show Exit Sub Handle_Error: MsgBox (Err.Description) End Sub Private Sub InitApp() 'Initializes classes and libraries. Executes once 'for a given session of Excel If bInitialized Then Exit Sub On Error GoTo Handle_Error If theUtil Is Nothing Then Set theUtil = New MWUtil Call theUtil.MWInitApplication(Application) End If If theFourier Is Nothing Then Set theFourier = New Fourier.Fourierclass End If If theFFTData Is Nothing Then Set theFFTData = New MWComplex End If bInitialized = True Exit Sub Handle_Error: MsgBox (Err.Description) End Sub
Creating the Visual Basic® Form
The next step in the integration process develops a user interface for your add-in using the Visual Basic Editor. Follow these steps to create a new user form and populate it with the necessary controls:
-
Right-click VBAProject in the VBA project window and click Insert > UserForm.
A new form appears under Forms in the VBA project window.
-
In the form's property page, set the Name property to frmFourier and the Caption property to Spectral Analysis.
-
Add a series of controls to the blank form to complete the dialog box, as summarized in the following table:
Controls Needed for Spectral Analysis Example
Control Type Control Name Properties Purpose CheckBox
chkPlot
Caption = Plot time domain signal and power spectral density
Plots input data and power spectral density.
CommandButton
btnOK
Caption = OK
Default = True
Executes the function and dismisses the dialog box.
CommandButton
btnCancel
Caption = Cancel
Cancel = True
Dismisses the dialog box without executing the function.
Frame
Frame1
Caption = Input Data
Groups all input controls.
Frame
Frame2
Caption = Output Data
Groups all output controls.
Label
Label1
Caption = Input Data:
Labels the RefEdit for input data.
TextBox edtSample N/A N/A Label
Label2
Caption = Sampling Interval
Labels the TextBox for sampling interval.
Label
Label3
Caption = Frequency:
Labels the RefEdit for frequency output.
Label
Label4
Caption = FFT - Real Part:
Labels the RefEdit for real part of FFT.
Label
Label5
Caption = FFT - Imaginary Part:
Labels the RefEdit for imaginary part of FFT.
Label
Label6
Caption = Power Spectral Density
Labels the RefEdit for power spectral density.
RefEdit
refedtInput
N/A
Selects range for input data.
RefEdit
refedtFreq
N/A
Selects output range for frequency points.
RefEdit
refedtReal
N/A
Selects output range for real part of FFT of input data.
RefEdit
refedtImag
N/A
Selects output range for imaginary part of FFT of input data.
RefEdit
refedtPowSpect
N/A
Selects output range for power spectral density of input data.
The following figure shows the controls layout on the form:
-
When the form and controls are complete, right-click the form and click View Code.
The following code listing shows the code to implement. Notice that this code references the control and variable names listed in Controls Needed for Spectral Analysis Example. If you used different names for any of the controls or any global variable, change this code to reflect those differences.
' 'frmFourier Event handlers ' Private Sub UserForm_Activate() 'UserForm Activate event handler. This function gets called before 'showing the form, and initializes all controls with values stored 'in global variables. On Error GoTo Handle_Error If theFourier Is Nothing Or theFFTData Is Nothing Then Exit Sub 'Initialize controls with current state If Not InputData Is Nothing Then refedtInput.Text = InputData.Address End If edtSample.Text = Format(Interval) If Not Frequency Is Nothing Then refedtFreq.Text = Frequency.Address End If If Not IsEmpty (theFFTData.Real) Then If IsObject(theFFTData.Real) And TypeOf theFFTData.Real Is Range Then refedtReal.Text = theFFTData.Real.Address End If End If If Not IsEmpty (theFFTData.Imag) Then If IsObject(theFFTData.Imag) And TypeOf theFFTData.Imag Is Range Then refedtImag.Text = theFFTData.Imag.Address End If End If If Not PowerSpect Is Nothing Then refedtPowSpect.Text = PowerSpect.Address End If chkPlot.Value = bPlot Exit Sub Handle_Error: MsgBox (Err.Description) End Sub Private Sub btnCancel_Click() 'Cancel button click event handler. Exits form without computing fft 'or updating variables. Unload Me End Sub Private Sub btnOK_Click() 'OK button click event handler. Updates state of all variables from controls 'and executes the computefft or plotfft method. Dim R As Range If theFourier Is Nothing Or theFFTData Is Nothing Then GoTo Exit_Form On Error Resume Next 'Process inputs Set R = Range(refedtInput.Text) If Err <> 0 Then MsgBox ("Invalid range entered for Input Data") Exit Sub End If Set InputData = R Interval = CDbl(edtSample.Text) If Err <> 0 Or Interval <= 0 Then MsgBox ("Sampling interval must be greater than zero") Exit Sub End If 'Process Outputs Set R = Range(refedtFreq.Text) If Err = 0 Then Set Frequency = R End If Set R = Range(refedtReal.Text) If Err = 0 Then theFFTData.Real = R End If Set R = Range(refedtImag.Text) If Err = 0 Then theFFTData.Imag = R End If Set R = Range(refedtPowSpect.Text) If Err = 0 Then Set PowerSpect = R End If bPlot = chkPlot.Value 'Compute the fft and optionally plot power spectral density If bPlot Then Call theFourier.plotfft(3, theFFTData, Frequency, PowerSpect, _ InputData, Interval) Else Call theFourier.computefft(3, theFFTData, Frequency, PowerSpect, _ InputData, Interval) End If GoTo Exit_Form Handle_Error: MsgBox (Err.Description) Exit_Form: Unload Me End Sub
Adding the Spectral Analysis Menu Item to Excel®
The last step in the integration process adds a menu item to Excel so that you can open the tool from the Excel Tools menu. To do this you add event handlers for the workbook's AddinInstall and AddinUninstall events that install and uninstall menu items. The menu item calls the LoadFourier function in the FourierMain module.
Follow these steps to implement the menu item:
-
Right-click the ThisWorkbook item in the VBA project window and click View Code.
-
Place the following code into ThisWorkbook.
Private Sub Workbook_AddinInstall() 'Called when Addin is installed Call AddFourierMenuItem End Sub Private Sub Workbook_AddinUninstall() 'Called when Addin is uninstalled Call RemoveFourierMenuItem End Sub Private Sub AddFourierMenuItem() Dim ToolsMenu As CommandBarPopup Dim NewMenuItem As CommandBarButton 'Remove if already exists Call RemoveFourierMenuItem 'Find Tools menu Set ToolsMenu = Application.CommandBars(1).FindControl(ID:=30007) If ToolsMenu Is Nothing Then Exit Sub 'Add Spectral Analysis menu item Set NewMenuItem = ToolsMenu.Controls.Add(Type:=msoControlButton) NewMenuItem.Caption = "Spectral Analysis..." NewMenuItem.OnAction = "LoadFourier" End Sub Private Sub RemoveFourierMenuItem() Dim CmdBar As CommandBar Dim Ctrl As CommandBarControl On Error Resume Next 'Find tools menu and remove Spectral Analysis menu item Set CmdBar = Application.CommandBars(1) Set Ctrl = CmdBar.FindControl(ID:=30007) Call Ctrl.Controls("Spectral Analysis...").Delete End Sub
-
Save the add-in.
Now that the VBA coding is complete, you can save the add-in. Save this file into the <project-directory>/distrib directory that Deployment Tool created when building the project. Here, <project-directory> refers to the project directory that Deployment Tool used to save the Fourier project. Name the add-in Spectral Analysis.
-
From the Excel main menu, select File > Properties.
-
When the Workbook Properties dialog box appears, click the Summary tab, and enter Spectral Analysis as the workbook title.
-
-
From the Excel main menu, click File > Save As.
-
When the Save As dialog box appears, select Microsoft Excel Add-In (*.xla) as the file type, and browse to <project-directory>/distrib.
-
Enter Fourier.xla as the file name and click Save to save the add-in.
-
Testing the Add-In
Before distributing the add-in, test it with a sample problem.
Spectral analysis is commonly used to find the frequency components of a signal buried in a noisy time domain signal. In this example you will create a data representation of a signal containing two distinct components and add to it a random component. This data along with the output will be stored in columns of an Excel worksheet, and you will plot the time-domain signal along with the power spectral density.
Creating the Test Problem
Follow these steps to create the test problem:
-
Start a new session of Excel with a blank workbook.
-
From the main menu click Tools > Add-Ins.
-
Browse to the <project-directory>/distrib directory, select Fourier.xla, and click OK.
The Spectral Analysis add-in appears in the available Add-Ins list and is selected.
This add-in installs a menu item under the Excel Tools menu. You can display the Spectral Analysis GUI by selecting Tools>Spectral Analysis. Before invoking the add-in, create some data, in this case a signal with components at 15 and 40 Hz. Sample the signal for 10 seconds at a sampling rate of 0.01 s. Put the time points into column A and the signal points into column B.
Creating the Data
Follow these steps to create the data:
-
Click and hold the lower-right corner of cell A2 and drag the formula down the column to cell A1001. This procedure fills the range A1:A1001 with the interval 0 to 10 incremented by 0.01.
-
Click cell B1 and type the following formula
"= SIN(2*PI()*15*A1) + SIN(2*PI()*40*A1) + RAND()"
Repeat the drag procedure to copy this formula to all cells in the range B1:B1001.
Running the Test
Using the column of data (column B), test the add-in as follows:
-
Select the B1:B1001 range from the worksheet, or type this address into the Input Data field.
-
In the Sampling Interval field, type 0.01.
-
Select Plot time domain signal and power spectral density.
-
Enter C1:C1001 for frequency output, and likewise enter D1:D1001, E1:E1001, and F1:F1001 for the FFT real and imaginary parts, and spectral density.
The next figure shows the output.
The power spectral density reveals the two signals at 15 and 40 Hz.
Packaging and Distributing the Add-In
As a final step, package the add-in, the COM component, and all supporting libraries into a self-extracting executable. This package can be installed onto other computers that need to use the Spectral Analysis add-in.
To package and distribute the add-in, follow these steps:
-
Reopen project in the Deployment Tool, if it is not already open.
-
Select Include MCR in the project settings, if the MCR is required on the deployment machine.
-
Click the Package button in the toolbar.
The MATLAB® Builder™ EX product creates the Fourier_pkg.exe self-extracting executable.
-
To install this add-in onto another computer, copy the Fourier_pkg.exe package to that machine, run it from a command prompt, and follow the instructions in the readme.txt file that is automatically generated with your packaged output.