On the Securities and Investment Design of experimental content course

[Paper Keywords] portfolio content design experiments

[Abstract] <<Securities and Investment>> course experiment carried out to mobilize the enthusiasm of students to deepen the understanding of theoretical knowledge, but the existing theoretical teaching process, how to use the limited experiments to achieve high efficiency? Experimental teaching content of the specific arrangements have played a key role in the article on the <<Securities and Investment>> course teaching content are discussed.
<<Securities and Investment>> is a combination of strong theoretical and practical courses, the theory of teaching content and practice of portfolio investment together, can enhance the students the basic concepts, basic understanding of the principles to enhance students' interest in learning , operational and perceptual knowledge, stimulate student initiative and innovation, to expand the depth and breadth of learning to enhance students analyze and solve problems and the practical ability to improve the quality of training finance professionals.

First, the <<Securities Investment 'course content of the experimental design

Currently offers <<Securities and Investment>> course experiment many institutions, but most of the teaching content is relatively fragmented, difficult to achieve good results. Based finance professional full program of experimental teaching system thinking, in teaching <<Securities Investment Science>> courses, the content of the teaching practice and theoretical knowledge of learning together, <<Securities Investment>> course experiments aimed at highly specialized courses, ranging unit less, to carry out targeted professional experiments, the related basic skills training in individual and consolidate classroom teaching of theoretical knowledge, but with a continued emphasis on follow-up course content convergence, avoid teaching content of the cross and overlap.

Second, the <<Securities Investment>> course content design theory based on experimental

Theoretical knowledge is the formation of practical ability, ability to apply foundation the ability to master certain knowledge based on training and practice through training in order to form, so students must first lay a solid theoretical foundation practice course for the future course of practice to prepare Therefore, the curriculum design must adhere to the practice of teaching content and teaching the theory of the principle of compatibility To the limited hours, the reasonable arrangement of teaching the theory and practice of teaching time, so while maintaining the integrity of imparting theoretical knowledge, allowing students to be more fully practical course of training.

Domestic Securities Investment of the basic theoretical framework is generally divided into four parts: the basic theory of portfolio investment, operation theory, decision theory and control theory and policy because of financial professionals <<Securities and Investment 'in the early course <<Financial Marketing> >, has a more detailed introduction to the basic theory of portfolio securities in the investment vehicles of stocks, bonds, funds, warrants, futures and options, and the stock market investment banks in the run theory has also been highlighted, these two parts can no longer repeated introduction, during the <<Securities and Investment>> the content of teaching can be focused on portfolio investment decision-making theory and control theory and policy. Topics include: securities investment portfolio analysis, fundamental analysis, technical analysis, stock market regulation and management due to financial professionals <<Securities and Investment>> follow-up courses <<Securities Investment Technical Analysis>> Therefore, in the 'Securities Investment>> Courses taught in the technical analysis of the content is just a brief introduction.

Third, the <<Securities Investment>> experimental design course content

As the <<Securities and Investment>> course experiment hours are limited (12 hours, so the content is a key test portfolio theory, securities characteristic line to verify, through this part of the teaching experiment, so that students master the initial investment securities portfolio analysis validation, drawing risk-free securities portfolio with a risk of the feasible set, the optimal combination of a variety of securities analysis steps are as follows:

1 Draw risk-free securities portfolio with a risk of the feasible set

Mainly need to calculate a security's expected return and standard deviation.
(1 data acquisition. First great wisdom on the software selected cycle data, making the stock price of monthly data, then the right price for re-processing (right through the complex process causing the share price reflects not only the capital gains, but also reflect the dividend yield, the final export to excel, get stock data. If you have a database, you can also get stock data from the database.

(2 calculate stock's annual returns. Use the excel function can be drawn automatically calculate annual stock return data
(3 calculate the stock's expected return and standard deviation in cell D3 enter the formula excel comes AVERAGE (C3: C18) will be output Founder of the expected return, enter the STDEVP (C3: C18) can output standard deviation of the stock .

(4 to calculate the risk-free assets and risky assets in the absence of short selling when the portfolio returns and standard deviation.
① the known data input form excel, risk-free asset in this case, select the bank deposits, earnings of 4.14%.

② Enter the table the proportion of investment risk-free asset, and gradually decreasing due to availability of short-selling restrictions, so the proportion of investment in risky assets in ascending order, both sum to 1 in the portfolio expected return on investment rate cells enter the formula , in this case, A8 * 0.0414 + B8 * 0.152. Similarly, a combination of the standard deviation obtained when the risk-free asset portfolio risk when combined with the standard deviation formula σp = | θσ |, in this case B8 * 0.3662.

③ draw capital allocation line in the excel menu, click 'Insert', 'Chart', select XY (Scatter), a smooth line plot. Click Next, in the Chart Source Data dialog box, modify the data area, X-axis selection standard deviation of the data D8: D28, Y-axis to select the desired rate of return data C8: C28. Click Next, select the location to save the chart to get the capital allocation line.

(2) the optimal combination of various portfolio

If only two risk portfolio together, the combination of the expected rate of return and standard deviation formula can be derived, and the corresponding feasible set curve, however, when more than two securities portfolio, it must be more sophisticated computational tools This experiment selected one of them, take Solver tool to achieve the purpose of the experiment.

(1-based data collection experiment in trying to calculate a variety of stocks together when the feasible set, so it is according to the method of Experiment 1 received four annual stock returns, expected rate of return and standard deviation. Select four stocks, in addition to calculate each stock's expected rate of return and standard deviation, but also to calculate the covariance between them, where the use of COVAR this function to calculate the Founder and Handan Iron & Steel covariance can be entered in a cell COVAR ( C3: C12, F3: F12), calculate the other covariance Similarly, you can get four shares of the covariance matrix.

(2 calculation of the optimal combination of four kinds of stock.
① install Solver in excel menu, click 'Tools', 'add', Add-Ins dialog box appears, select the Solver dialog box, and then 'OK', then Solver has been successfully installed. Links to free download http://eng.hi138.com
② known in the excel spreadsheet to enter data.
③ establish operations area. The expected rate of return to the relevant data into the table, in a cell reserved for the optimal proportion of investment, portfolio yield, portfolio variance, standard deviation. Default optimal investment proportion of 1,0 , 0,0, which is the total investment in Handan Iron & Steel stock on the use of matrix operations to calculate the portfolio variance method. and portfolio variance prescribing.

So we set up a computing area, the establishment of the cell data the relationship between changes in a cell will cause the data to make corresponding changes in other data.

④ optimal solution obtained by Solver in excel in the establishment of regional constraints, the corresponding constraints are listed Solver principle is that the computer automatically eligible to filter the solution, the optimal solution, therefore, must accurately set the filter in the constraint region, the proportion of investment should be equal to the sum of 1, in the appropriate cell type = SUM (). If the situation is no short selling, the investment proportion of each stock is> = 0 When people set a target rate of return, the computer will automatically calculate the minimum qualifying standard deviation of the solution, which is by looking for the optimal solution. constantly changing the target rate of return has been a lot of groups on the optimal solution is find the efficient frontier.

Click the Tools menu, you will find the Solver in which this option, open the Solver dialog box, click in the dialog box, set constraints, the optimal solution will automatically output to the appropriate computing area. Suppose set a target cell Select 'minimum.' constraints in the absence of short selling should have three, one is the proportion of investment should be> = 0, the proportion of investment and should be equal to 1, then enter 0.2, that the first default target income 20% target items , variable items and constraints are finished you can start to enter, click on 'solve' the computer will automatically calculate the results, click Save, you will find in the original calculation of zone data has changed.

In this calculation results in together to get four kinds of stocks, target return is 20%, the combined standard deviation of the smallest solution, the solution this time is four to get the proportion of investment, the investment proportion of 0.36,0.63,0.1, 0, which is to find the optimal combination.

⑤ establish data area. The front just to get the optimal combination of the efficient frontier of a point, to get the other point of the efficient frontier, we must constantly changing target rate of return, get a different optimal solution, and ultimately draw the efficient frontier. In order to get This series of data, to establish a data area to hold the results obtained continuously calculated. the portfolio returns of 0.2, standard deviation 0.33 is written to the data area. Next to continue to use the Solver tool, the constraints of the target rate of return 20 % to other data, such as 25%, another solution would be the optimal solution, followed by changing the cell, it will be some combination of need, the same results of the calculation is that we finally get a complete data
(3 given target rate of return solving the optimal proportion of investment and if you want to invest in four stocks, the required return on investment rate of 28%, then how should the minimum allocation of risk it? Front Solver can actually solve this problem As long as the constraints added in 0.28, that is, when asked 28% yield, the optimal proportion of investment should be 0,0.79,0.21,0 there such a short time is calculated, and ultimately get results.


[1] Zvi Bodie. Investments (6th edition [M]. Machinery Industry Press.

[2] Sun Jiayu. Securities Investment experimental teaching reform of [J]. Industry and Technology Forum, 2008 (6).

[3] Yingchun. Securities investment firm offer courses teaching the case of exploration [J]. Education and occupation, 2007 (6) Links to free download http://eng.hi138.com

Securities Investment Papers