For my advanced database course, the final project was to create an application that made use of Oracle 11g and PL/SQL. My groupmate and I decided to create a portfolio management application. I focused on developing the back-end database and writing the PL/SQL procedures while the other guy focused on the front-end.

The system supports multiple users each with multiple portfolios and a watch list. Each portfolio is made up of financial instruments and cash balances in various currencies which is controlled via transactions. Since the financial instruments are priced in their local currency, account values are calculated using conversion to the account's primary currency. A series of triggers, functions, and procedures were created to manage the database and get relevant information for users.

I used an old laptop as a server and used a dynamic DNS service to make the database accessible from anywhere even though my home internet doesn't have a static IP address. I would have hosted it on my VPS but Oracle 11g XE didn't meet some requirement. The laptop hosted Oracle 11g XE and the Java application I wrote to download CSV formatted data from Yahoo Finance, split it, and then insert new historical open high low close and volume data daily and update records for more current data every 30 minutes (I didn't want to get blocked by Yahoo for spamming requests). There are over 3000 ticker symbols that it gathers data on.

A scheduled task which executes a Jar file downloads Yahoo finance data via CSV, parses it, and updates the data of over 3000 ticker symbols

Since the guy working on the front-end worked at a .NET shop and liked doing everything using C# I wrote a small DLL to make it easy to get data from the database and to also act as a C# wrapper for calling the procedures and functions in PL/SQL. The Oracle DataAccess API wraps everything in so many layers but I wanted it to be easy to get basic C# primitives for this project. He used this to link back-end functionality to the .NET/Javascript/HTML front-end.

NOTE: If you happened to look carefully at the ER diagram at the top of the post, you will notice some of the fields used float values, which, while normally isn't a good idea for finance due to rounding errors of single or double precision floating point, is probably alright due to the fact that a) this was a school project and b) it has 126bit precision (roughly equal to 38 decimal precision)

comments powered by Disqus
the random curiosities of derek lomibao ©2014