PMS maintains the portfolio of a person ( user of the system) who is involved in stock trading. The main objective of PMS is to maintain the list of stocks a person has bought. The system will also allow a person to trade in the stock exchange. The proposed PMS will have the functionalities outlined in the following subsections. Maintaining Stock Information The PMS will work on a virtual stock exchange, which will have 20 stocks listed on it. The system will display stock prices for these 20 stocks, which will be updated at a frequent interval. Whenever a user logs into the system, it will display all 20 stocks with the current price as well as highest and the lowest price for the day. The information provided will be used while trading the stocks. Maintaining Customer Information Customers and administrators will both use the system. Normal users, the customers, would be interested in trading on the stock exchange through the proposed system. They would also be able to view the status of their orders for buying and selling. The status can be approved, rejected, or cancelled. Administrators have the right to create users (customers) in the system. Administrators will be responsible for approving or cancelling stock trading orders placed by the customers. They can also view all the approved, cancelled, and pending orders. The system will allow only registered users to trade on the stock exchange. So before starting the trading, the users will have to register on the system by providing following details to the administrator: -
Customer ID -
Customer password -
Customer name -
Customer phone -
Customer email -
Customer address -
Current balance -
Customer margin The administrator will then add the customer to the system. Generally most of the stock-trading firms offer traditional, margin, flexible, discounter, and upper or low quantity limit accounts, among others. For each of them, the trading firm specifies different business rules that regulate the specific forms of trading that they support. To simplify the complexity, PMS will offer only a marginal account concept, in which the system will force the users to maintain a minimum balance while trading on the stock exchange. The administrator will set this balance via the CustomerMargin field. When registering, the user will have to deposit minimum amount in the stock exchange. The administrator will set this amount via the CurrentBalance field. Once the registration has been completed, each user will have a unique username and password. The users will have to supply their username and password before accessing the system. During the login stage, users would have an option to change their password. Because it is not mandatory to change the password, the system will not force a user to do so. Stock Selling and Purchasing Upon logging into the system, a user can start trading on the stock exchange. As mentioned earlier, the system will display current prices for all the stocks along with the highest and lowest price in a day. Users can select a particular stock and enter the desired quantity for buying that stock. Here the total amount of the transaction will be checked against the CustomerMargin and CurrentBalance fields for that customer. If the difference between the CurrentBalance and transaction value is greater than the CustomerMargin, the order will be generated by the system. If it is less, the customer will not be allowed to purchase the stock and the transaction will not be committed. Once the order has been generated, the transaction value will be deducted from the CurrentMargin field. Finally an administrator can approve or cancel the order. If the administrator approves the order, the customer portfolio will be updated accordingly. If the administrator cancels the order, the customer's margin amount will be updated accordingly . Similarly, the customer can sell stocks that have been purchased earlier. The customer can select the stock from the portfolio and sell them on the stock exchange. Upon selling, the order will be generated, which can be approved or cancelled by the administrator. Once the order has been approved, the transaction amount will be added into CurrentMargin field for that customer. Maintaining Customer Portfolio For each customer the system will maintain the list of stocks traded (bought as well as sold) by the customer. This will be stored in the portfolio, which at any given point will display stocks held by the customer along with the quantity of each stock and current price. Stock quantity and current price will be used to determine the current value of a customer's portfolio. Assumptions Because the objective of the system is to demonstrate the concepts of migration, the following assumptions have been made to simplify the functional complexity of the system: -
There will be a virtual stock exchange running behind the system. -
The stock exchange will allow trading based on a marginal account. -
The virtual stock exchange will list 20 stocks, which can be traded by the user. -
The prices for these stocks will get changed every minute. -
Whenever a user wishes to buy a particular stock, the required quantity for that stock will be available in the stock exchange. -
There will be an administrator in the system who will act as frontend person of the stock exchange. -
The way that a customer sends information to the administrator is out of the scope of the system. -
The administrator will have right to approve and cancel the stock trading order placed by the user. System Flow and Architecture Figure 12-1 depicts the complete flow of the system, and Figure 12-2 shows the architecture of Visual Basic application. Customers access the application through the TraderWorkStation project and the administrator accesses it through the SurveillanceMonitor project. These projects act as front ends for the system and interact with the COM components through Common Gateway Interface (CGI). The CGI project is responsible for invoking the methods of the COM components , which have the logic for interfacing with the database. Figure 12-1. Flow of Portfolio Management System. Figure 12-2. PMS Visual Basic 6.0 architecture. Database in the System The entity-relationship (ER) diagram for PMS is shown in Figure 12-3. Tables 12-1 and 12-2 have been designed and created under the database Stock_System in MS-SQL Server 7.0 for the proposed system. Figure 12-3. Entity relationship diagram for PMS. Table 12-1. Tables in PMS Table Name | Table Description | Admin | Stores basic information for administrator. | Customer | Stores basic information for customer. This table will store customer name, customer address, customer margin limit, and so on. | Portfolio | Stores list of stocks owned by a particular customer by stock name and quantity of that stock. | Stock_Daily_Values | Stores opening and closing price of all stocks in the stock exchange. | Stock_Order | Stores orders placed by customer. Orders stored in this table have to be approved by the administrator. | Stock_Price | Stores current price of all stocks in the stock exchange along with highest and lowest price. | Stocks_Master | Stores list of all stocks available for trading in the stock exchange | Table 12-2. Stored Procedure in PMS Stored Procedure Name | Stored Procedure Description | Sp_Admin | Adds an administrator to the system. | Sp_Approve_Order | Allows administrator to approve orders. Customer portfolio and margin are updated accordingly. | Sp_Cancel_Order | Allows administrator to cancel orders. Customer portfolio and margin are updated accordingly. | Sp_Change_Admin_Password | Administrator password can be changed provided old passwords match. | Sp_Change_Password | Changes customer password at the time of login. | Sp_Customer | Adds a new customer to the system. | Sp_Customer_Portfolio | Returns the current portfolio of stocks held by the customer. | Sp_Get_All_Stocks | Returns all stock ID and names maintained in the system. | Sp_Get_Approved_Orders | Returns all approved orders for a particular customer. | Sp_Get_Cancelled_Orders | Returns all cancelled orders for a particular customer. | Sp_Get_Current_Stock_Prices | Returns the current stock prices data to be shown to customers to do trading. | Sp_Get_Customer_Name | Returns all registered customers in the system. | Sp_Get_Margin_Amount | Returns the margin amount for a specified customer. | Sp_Get_Prices_For_Week | Returns closing prices of stocks for past week to be displayed as a report in MS Chart. | Sp_Get_Unapproved_Orders | Returns all unapproved orders for a particular customer. | Sp_Insert_Stocks_Master | Adds 20 stock values in the system to be used for the stock trading. | Sp_Logout | Logs out the user from a trading session. | Sp_Order | Places buy and sell orders for the stocks in the system. The margin values of the customers are changed accordingly. | Sp_Update_Customer | Updates the customer details. | Sp_Update_Daily_Stock_Values | Updates opening and closing price of stock in a day. This SQL script is scheduled as a job to execute in SQL server every minute. | Sp_Update_Stock_Prices | Updates stock prices at regular intervals of one minute. This SQL script is scheduled as a job to execute in SQL server every minute. | Sp_Validate_Admin_Login | Validates administrator login. | Sp_Validate_Login | Validates customer login. | Sp_View_Customer_Details | Returns all customer details, which can then be updated. | Setting Up PMS in Visual Basic 6.0 To set up PMS, the following steps need to be followed: -
Create the database called Stock_System in the SQL Server. -
Use the sp_Insert_Stocks_Master procedure to insert the stocks into the system. -
The database scripts have been kept in the DBScripts folder for this chapter. Running the Database.sql script from this folder should allow you to set up the database. -
Configure the sp_Daily_stock_Values and sp_Update_Stock_Prices in SQL Server scheduler to execute every minute so that we can simulate a real stock exchange-like scenario where the stock prices gets updated very quickly. -
All the COM components (discussed in detail later in this chapter) will have to be compiled and registered on the system. -
All the COM components refer to the stocksystem.ini file, which has to be kept in the WINNT directory. -
The database parameters are kept in the stocksystem.ini file and have to be changed to point to your database server. The server on which the CGI code executes is also stored in the stocksystem.ini file. -
Customers use the executable TraderWorkStation to execute functionality. -
Administrators use the project SurveillanceMonitor to execute functionality. -
The CGI project has to be compiled and the resulting exe file has to be kept in Scripts subdirectory of Inetpub directory. This CGI project should have references to all the COM components. Technical Features Used in the System PMS has been developed using the following technical features: -
COM components for completing the stock trading operation -
Graphics for displaying stock performance over one week for a selected stock -
ADO for performing database operations in the COM components -
MDI forms for GUI design -
CGI scripts for invoking methods of COM components |