1.- REVISTAS PRINCIPALES

Portfolio Tracker is software which is freely available on internet by different financial sites. I have made an effort to create similar software which can be used to keep a track of portfolio and which will also tell the user about any arbitrage opportunity which is available due to price variation at NSE (National Stock Exchange) and BSE (Bombay Stock Exchange) at different point of time during a single day.

INTRODUCTION

Portfolio Tracker is software that can help in calculating the gain or loss on the stocks of a portfolio. This software pulls the current prices of the shares from NSE and BSE sites at a definite interval. It compares the current price with the purchase price and hence calculates the profit or loss on the stock. This software is made using the functions of Microsoft Excel 2007.

GOAL AND OBJECTIVE

1) To calculate the profit or loss of an investor on a given por tfolio based on the current market situation.

2) To find out the arbitrage opportunity based on price difference at NSE and BSE market.

3) As every investor does not have time to regularly keep a track of their portfolio at a single point of time, this software helps them to watch and monitor their portfolio as soon as they open this excel sheet along with the internet connection.

STATEMENT OF SCOPE

The software takes name of the share, total number of shares bought and the average purchase price as input and fetches the current market value of those shares. The software calculates the total profit or loss and percentage of the same. We can also extend the scope according to the use of investor or personalize the working of the program according to the needs of investor.

Portfolio Tracker also compares the NSE and BSE prices every minute and recommends the arbitrage strategy to the user. We can also extend the scope of same for currency hedging. The Software is password protected; hence the user can prevent mishand ling of his personal portfolio and can keep the data confidential.

84 MAJOR CONSTRAINT

1) The software updates the prices at particular interval; hence the change in prices for less than a minute cannot be accommodated in it. This might become a shortcoming while deciding the arbitrage strategy as the stock prices changes at every fraction of seconds.

2) The software requires internet connection.

3) It takes average purchase price as an input instead of taking different purchase quantity at different purchase prices.

USER PROFILE

A common man dealing in share market can use the software for personal portfolio tracking.

FUNCTIONAL MODEL AND DESCRIPTION

1) Profit and Loss Calculation: First function of the software is used to calculate the profit and loss for the entered portfolio of the user. This function requires following inputs from the user:

a. Script code for BSE and script name for NSE b. Buy Date

c. Total number of shares bought d. Average Buy Price

Once these inputs are available to the software, it takes the name of the share and matches it which the data available from the NSE site using the VLOOKUP function.

Buy price is compared which the current market price and If (Current Price > Buy Price)

85 Amount of Gain (Loss) per share is calculated by following formula:

–

Total Gain (Loss) is calculated by multiplying the Gain (Loss) per share with the total number of shares bought.

Percentage of Gain (Loss):

Annualized:

Absolute:

VLOOKUP Function: The V in VLOOKUP stands for vertical. It searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.

Syntax:

VLOOKUP (lookup_value, table_array, col_index_num, range_lookup) Where,

Lookup_value is the value to search in the first column of the table array.

Table_array is the array from where the value is to be matched. The first column of table_array is searched for lookup_value.

Col_index_num is the column number of table_array from where the value is to be fetched.

86 Range_lookup can have any of the two values (False or True). For exact match false is used and true is used for approximate match, where the next largest value that is less than lookup_value is returned.

2) Recommendation of Arbitrage Strategy: An arbitrage is the simultaneous purchase and sale of an asset in order to profit from a difference in the price. It is a trade that profits by exploiting price differences of identical or similar financial instruments, on different markets or in different forms. Arbitrage exists as a result of market inefficiencies; it provides a mechanism to ensure prices do not deviate substantially from fair value for long periods of time.

The function of the software recommends the users an arbitrage opportunity for his portfolio. It compares the NSE and BSE stock prices for shares of the portfolio and based on those prices it calculates the Arbitrage opportunity.

Arbitrage Strategy: Now, following function is used for calculating the recommendation for arbitrage strategy:

If (Arbitrage Opportunity == YES) {

If (NSE Price > BSE Price) {

Arbitrage Strategy = BUY BSE, SELL NSE;

} Else {

Arbitrage Strategy = BUY NSE, SELL BSE;

} Else {

Arbitrage Strategy = “-“;

}

87 Arbitrage Gain/Loss: The software also calculates the Gain (Loss) per share because of the arbitrage. Following formula is used for the same:

ROAD AHEAD

The software is currently launched with basic features of portfolio tracker. It can be further enhanced by making changes. Few of the enhancements suggested are:

1) Instead of the taking the hard coded value for the purchase price, a function can be developed which takes the purchase price and number of shares purchased as input and thereby calculate the Average Price.

This will help the user the update his current purchase and hence automatically calculate his new Average Purchase Price.

2) This can be extended to monitor currency prices and take necessary actions at desired point of time to make profits.

3) Through this technique, we can not only calculate or monitor the stock prices but can also use it to retrieve data that can automatically be refreshed and takes no time to monitor the changes taking place.

88