PMT Forum

PMT Forum's MOST POPULAR Discussion Boards => Stocks and Stock Trading => Topic started by: GIG on Jan 25, 2010, 12:46 PM

Title: How make a tabled stock portfolio in excel
Post by: GIG on Jan 25, 2010, 12:46 PM
This really very rudimentary but a lot of peeps asks for this. This is done in microsoft excel.

Here goes,

First row is for the stock name
2nd row is for the date of acquisition
3rd : number of shares
4th: purchase price
5th: Initial value that is purchase price times the number of shares
6th: closed value that is closed value times number of shares. Example, your 10 shares closde at 200 apiece. Closed value then is 2000.

7th: Buying charges. Brokers charge a percentage of all trades based on the peso amount of a trade. For me, I  used my abacus trading acct as benchmark of 0.0113. Multiply this to your total purchase value. Example, if you bought 10 shares at 200, then to get the buying charge, multiply 2000(10x200) by 0.0113.

8th: selling charges. Similar as buying charge but multiply this with 0.0163. Example you sell 10 shares at 300 apiece. Selling charge equals 3000(10x300) multiply by 0.0163.

9th: last traded value or closing value of a stock
10th: Total Gain/Loss. This is arrived by deducting the following from the closed value. Initial value,buying charges and the selling charges. This is your net profit or loss.

Try to organize your stocks this way. You can see that brokers fees pile up easily. We cant totally escape from them but its good to know the exact expenses incurred from your trading. Good luck and start trading.
Title: Re: How make a tabled stock portfolio in excel
Post by: rhipolito on Jan 25, 2010, 02:03 PM
Nice GIG. Thanks! this will help me track down my profit and loss ( i hope profit ) :rakenrol:
Title: Re: How make a tabled stock portfolio in excel
Post by: GIG on Jan 25, 2010, 02:15 PM
Did you buy something today rhipolito? the market was screaming Sale! Sale! Sale!
Title: Re: How make a tabled stock portfolio in excel
Post by: rhipolito on Jan 25, 2010, 02:48 PM
ipit ako GIG...hahahah..long term investor...no more funds to bottom fish
Title: Re: How make a tabled stock portfolio in excel
Post by: freefront on Jan 25, 2010, 03:06 PM
First, I need to get back my laptop. She's like a bad girl that won't come home. Dang beautiful nieces who think whatever they do in their lives are more important than me earning money for their pizza!
Title: Re: How make a tabled stock portfolio in excel
Post by: GoodSteward on Jan 25, 2010, 11:19 PM
Boss GIG,
the multiplying factor that you use for buing () and selling expenses, whats the brokers fee that you took into consideration? Is .025% per transaction or .05% per transaction?
Posted on: Jan 25, 2010, 11:16 PM
Guys, medyo OT how long will this plunging continue? One week, 2 weeks? Or freefall to last years bottom? ANy speculations?

Thanks pala GIG , thats a good way to monitor my transaction. Sa citiseconline portfolio kasi they consolidate all the same stocks u have at hand and give you the average purchase price.
Title: Re: How make a tabled stock portfolio in excel
Post by: GIG on Jan 26, 2010, 09:16 AM
Inipon ko na lahat yung brokers fee,fees,taxes etc to arrived at my number of 0.0113 for buy.
Here is the breakdown:

For Buy:
broker commission      1%
vat                          0.12%
SCCP fee                  0.01%

Total                        1.13% or 0.0113

For selling:
broker comm              1%
vat                          0.12%
sales tax                   0.5%
SCCP fee                   0.01%

Total                        1.63% or 0.0163

They charge 10% tax for cash dividends received.
 
BPI trade has smaller buying and selliing charges about 0.303% or .00303
with minimum commission of P20
and PDTC monthly fee of 0.00000833 x market value of total month end holdings.

Posted on: Jan 26, 2010, 09:10 AM
Regarding the PSE,

Again Im no nostradamos, but I think this is a knee jerk reaction to wallstreet. I do not see any major problems in the US that we do not know of. Im buying right now.
Posted on: Jan 26, 2010, 09:14 AM
To freefront

I know the feeling man! blame it on facebook hehe!
Title: Re: How make a tabled stock portfolio in excel
Post by: freefront on Jan 26, 2010, 11:24 AM
Gig, how about we just copy the charges before we send the order. Firstmetro calculates the charges before we hit the send button anyways.

Thanks for the format. I was doing it by hand rill now.
Title: Re: How make a tabled stock portfolio in excel
Post by: GIG on Jan 26, 2010, 11:45 AM
That would be fine freefront.

I did this excel thing when I was day trading. I play with the numbers inputing the buying stock price and projecting the sell price, in an instant I could see the net profit/net loss. This would enable me to decide how much stock I would buy to get say a 10T profit on a trade. Unfotunately, I don't day trade anymore and I strongly discourage the practice.
Title: Re: How make a tabled stock portfolio in excel
Post by: GoodSteward on Jan 26, 2010, 02:08 PM
Thanks GIG! with regard to the brokerage fee, im using citiseconline and its 20pesos or .25% per transaction whichever is higher. Just wanna share...
Title: Re: How make a tabled stock portfolio in excel
Post by: GIG on Jan 29, 2010, 10:45 AM
Here is also how I keep track of my profit and losses since the beginning of my trading life. This I got from my Dad. He always believe that he has better luck than the average guy in mahjong. He keeps track of his winnings and losses since day 1.

This is also done in excel

1st Row: Buy date
2nd Row: Sell date
3rd: company name
4th: no of shares
5th: purchase price
6th: Initial Value
7th: Buy charges
8th: Selling Price
9th: Sell gross value
10th: Sell charges
11th: Days held
12th: Gross Profit
13th: Net Profit
14th: Net Dividend This is where you keep tabs on your cash dividend less of taxes.
15th: Captial gained : This is equal to the net profit divided by the sum of (Initial value + buying charges)
16th: % Monthly gain: this is equal to capital gained divided by (days held/ 30days)

             %Monthly Gain = capital gained / (days held/30 days)

     This would give you the percentage per month of your trade. I monitored this when I was day trading. I aimed for above 6% per month before I sell my positions. I did fine till the crisis hit.

Hope this helps.
                                     
Title: Re: How make a tabled stock portfolio in excel
Post by: vike on Feb 04, 2012, 12:16 AM
paano po kinukuha ung Net profit po, Capital Gained at % Monthly gained? Ang lumalabas po kc sa akin ay decimal number po sa Capital Gain kaya baka po may mali po..

ginagawa ko po ito para po sa school works po