Code




programming experiments and work




PL/SQL PORTFOLIO APP WITH YAHOO FINANCE FEED




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

GOLEM JOURNAL OF RELIGION AND MONSTERS





Site: golemjournal.org(UPDATE: unfortunately, GOLEM Journal is no longer in operation, they couldn't get enough people to write and submit new content Link to site used for testing hosted on my server)

I was approached by the senior editor of GOLEM journal and asked to help them redesign their website. The editorial board had limited experience with HTML and needed an easy way to manage their journal without needing to be a coding expert. Their old website had stagnated due to not having a reliable way to add and modify content. view the original website (as I mentioned above, GOLEM has shut down)

In response to their needs, I build a custom content management system from the ground up using PHP and MySQL. This system allows the editorial board to manage their site without the need to have any knowledge in website design while at the same time retaining control to customize the site easily to fit changing needs over time.

Major front-end features:

  • New sleek design
  • Full-text and keyword search of articles
  • News posts that can be sent to a mailing list
  • Customizable pages

Major back-end features:

  • Multiple user permission levels
  • Secure password management using hashed and salted authentication along with secure password reset
  • Article review section for editorial board commenting before live publication
  • What you see is what you get (WYSIWYG) forms to edit content without knowing HTML/CSS
  • Text based and PDF article support

Here is a view of the back-end interface.(click to enlarge)

comments

SIMULATING GRAVITY EFFICIENTLY WITH QUAD-TREES




The n-body problem is a classic physics problem in which a number of objects interact with each other using gravity in space. The net force acting on a particular body is determined by the sum of gravitational force between that body and every other body in the system

Calculating gravitational attraction is an expensive process. Every unique body within a system exerts an equal and opposite force on one another. This results in a time complexity of O(N^2). The force between two individual points of mass is strongest when the two points are very close to each other and weak when far away from each other. This property is used in the Barnes-Hut algorithm to reduce the time complexity of the problem to O(N log N). Because far away points have little impact on the net force acting on a gravitational body, the Barnes-Hut algorithm groups points sufficiently far away from the one in question and only calculates the force between the point and the group's center of mass.

Groups of bodies are determined recursively using a quad tree. This quad tree is a tree data structure with 4 children. Each child represents 1/4 of the spacial region based on x and y coordinates.


Spacial visualization of the quad-tree
Leaf nodes contain a single body. Each node contains a count of bodies in the subtree,a list of the bodies in the subtree, the spacial region it covers (xmin,xmax,ymin,ymax),the location of the center of mass,the total mass of all of the subtree, and pointers to the child nodes. The structs that I used to construct the tree are shown below.

//the structs used for the quad-tree
typedef struct{
    double x,y,vx,vy,mass,fx,fy;
    unsigned char r,g,b;
} Body;
struct Llnode{
    struct Llnode *next;
    Body  bod;
};
typedef struct Bhnode{
    struct Bhnode *next[4];
	struct Llnode *list;
    long int listsize;
    double cmassx,cmassy,mass;
    double xmin,xmax,ymin,ymax;
} Bhnode;

In order to determine if a group of bodies are sufficiently far away from each other, a value theta is set by the user that acts as a threshold for the ratio between the width of the region (s) and the distance between the body in question and the current node's center of mass (d). If the threshold is met, s/d < theta, then the node is sufficiently far away to consider the whole subtree as a single mass.


Video of Barnes-Hut in action

comments



Scroll down for more.(or click here)

the random curiosities of derek lomibao ©2014