Create custom equity indices using Perl and Mysql.

2017-09-28

---

Yahoo APL to access stock information is no longer available to the public. Project details made available for legacy reasons.

Introduction

In the last procedure I demonstrate how to create your own equity tracking database. Now that you have this in place there are many things you can do. For example, you can create your own equity indices. The indices are weighted averages of the equities in your database. You can use your own calculated indices to compare to exchange benchmarks, and to describe the performance of your particular subset of equities.

First, you need to learn the basics, for this refer to wikipedia and other internet resources. For simplicities sake we are going to program a price-weighted index. The price of each component stock is the only consideration. This will cause the large capitalization stocks to have a governing influence on the index. However, to accommodate this you could create indices based market capitalization or you could use a different methodology such as capitalization-weighted.

For this procedure the script will not be broke down to clarify the basic math that is required to create price-weighted indices. Instead, the script is presented with copious commenting to assist you. For this project we are going to create several indices, one of the entire active list, and one for each equity sector.

Procedure

1. Make sure you completed procedure on "Financial Database with Mysql using Perl" this is the base that needs to be fully functional before moving on.

2. Upload financial data for the particular day that you are interested in calculating indices.

3. Now review the perl script and create your own script.

Click here to download the text file of perl script presented above.

4. Now find the text file with the indices data and open it. Immediately below is the a header table describing the output columns. Example of text output is provided.

Index Name	Date	Index Price per Share	Index EPS	Index PE	Index PS	Index PB	Index Dividend per Share	Index Volume	Index Capitalization

5. Now you can consume your indices data to better understand the relative pricing of equities.

Conclusion

You now have a script that will calculate indices for your list of equities of interest. At the least you can see if your equities are a mirror of the major indices such as S&P500. If you are a happy passive investor take apart the scripting and apply to other database projects. Enjoy!