PMT Forum
How do you want to make money today?

How make a tabled stock portfolio in excel

GIG · 12 · 4168

GIG

  • PMT Idol 1000
  • ****
    • Posts: 1,359
    • Likes Received: +7/-0
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.


rhipolito

  • Forum Master 300
  • ***
    • Posts: 387
    • Likes Received: +0/-0
Reply #1 on: Jan 25, 2010, 02:03 PM
Nice GIG. Thanks! this will help me track down my profit and loss ( i hope profit ) :rakenrol:


GIG

  • PMT Idol 1000
  • ****
    • Posts: 1,359
    • Likes Received: +7/-0
Reply #2 on: Jan 25, 2010, 02:15 PM
Did you buy something today rhipolito? the market was screaming Sale! Sale! Sale!


rhipolito

  • Forum Master 300
  • ***
    • Posts: 387
    • Likes Received: +0/-0
Reply #3 on: Jan 25, 2010, 02:48 PM
ipit ako GIG...hahahah..long term investor...no more funds to bottom fish


freefront

  • VIP 4500
  • *****
    • Posts: 4,888
    • Likes Received: +9/-0
Reply #4 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!


GoodSteward

  • VIP 3500
  • *****
    • Posts: 3,991
    • Likes Received: +4/-0
Reply #5 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.


GIG

  • PMT Idol 1000
  • ****
    • Posts: 1,359
    • Likes Received: +7/-0
Reply #6 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!


freefront

  • VIP 4500
  • *****
    • Posts: 4,888
    • Likes Received: +9/-0
Reply #7 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.


GIG

  • PMT Idol 1000
  • ****
    • Posts: 1,359
    • Likes Received: +7/-0
Reply #8 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.


GoodSteward

  • VIP 3500
  • *****
    • Posts: 3,991
    • Likes Received: +4/-0
Reply #9 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...


GIG

  • PMT Idol 1000
  • ****
    • Posts: 1,359
    • Likes Received: +7/-0
Reply #10 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.
                                     


vike

  • Newbie
  • *
    • Posts: 9
    • Likes Received: +0/-0
Reply #11 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


 


PMT Shoutbox

Refresh History
  • Post only questions or comments here. Advertisements NOT ALLOWED.
  • roselove: hello po sa inyo... newbie here
    Aug 06, 2019, 03:31 PM
  • Niel Jhacoubs: Sino palaging active dito?
    Jul 30, 2019, 04:15 PM
  • dagocjasonrey: Anong update dito?
    Jul 22, 2019, 08:49 PM
  • mark_Lee: anong bago dito
    Jun 28, 2019, 01:03 AM
  • mark_Lee: balita
    Jun 28, 2019, 01:03 AM
  • Lovelyprincess72: hi everyone , I'm newbie here
    Jun 27, 2019, 02:36 PM
  • Niel Jhacoubs: Musta?
    Jun 26, 2019, 01:43 PM
  • Forexanalysis: Hello
    Jun 25, 2019, 06:33 PM
  • Niel Jhacoubs: Hello sa mga online jan! :)
    Jun 15, 2019, 02:58 PM
  • oragon: :applause:
    May 20, 2019, 03:53 PM
  • FutureGizmo: Thank you for reporting those spam posts, oragon! Spam posts now deleted.
    May 20, 2019, 01:59 PM
  • oragon: Admin kindly monitor some peps! posting and sharing inappropriate nude photos and website links
    May 20, 2019, 01:52 PM
  • @culinary10: Hello I am new here! Regarding sa bdo life insurance for 8 years, pwede pa kaya ma refund yung binayad ko na 50k last year? Gusto ko sana icancel kasi lack of financial na, last may 15 2018 po un nag start ako ng life insurance, pinili ko annually kaso etong may 15 2019 wala nako ma ipay so gusto ko sana icash out, may alam ba kayo kung pano marerefund?
    May 17, 2019, 07:56 PM
  • FutureGizmo: Hi Sonny, the posting issue is now resolved. Thanks for informing us! We're currently testing a new, mobile-ready theme so let us us know kung may problema pa sa Forum. :)
    Apr 04, 2019, 10:40 AM
  • sonny.wapak: Nakaka receive din ba kayo nito pag gawa ng new post? "Please try again. If you come back to this error screen, report the error to an administrator."
    Apr 03, 2019, 10:56 PM
  • leonine_zafiro: what product nun?
    Apr 01, 2019, 10:25 PM
  • dhondiex27: hi
    Mar 30, 2019, 10:55 PM
  • lusart: jayrob i agree 200%. hulog ng langit organico. legit na legit
    Mar 29, 2019, 09:08 AM
  • palemelch: Hi
    Mar 28, 2019, 04:08 PM
  • O.C.W._AK: Magandang Araw sa Lahat! Blessings! (",)
    Mar 28, 2019, 08:42 AM
  • thor15: hello everyone, newbie here
    Mar 25, 2019, 01:47 AM
  • captjaylo: present newbie here
    Mar 22, 2019, 10:06 AM
  • balboagilbert26: hello Guys .. Newbie here
    Mar 21, 2019, 10:55 PM
  • businesswoman2019: @jefsanity nag inquire na po ako pero di sila nagdidisclose. Yung branch po kasi na target ko, currently building pa.
    Mar 21, 2019, 04:27 PM
  • jefsanity: @businesswoman2019, much better inquire ka na lang sa admin nila.
    Mar 21, 2019, 01:51 PM
  • businesswoman2019: Hello! Any idea how much ang stall sa waltermart. 2m x 2m
    Mar 21, 2019, 08:28 AM
  • John Neil: Looking for an accredited iron foundry
    Mar 17, 2019, 03:19 PM
  • jayrob: Mark_Lee ung organico legit po yan..  Member din AQ..
    Mar 17, 2019, 12:57 AM
  • perlymelad: Hello po! Need advice, why my new MLM leaders failing?
    Mar 14, 2019, 08:51 PM
  • perlymelad: Present
    Mar 12, 2019, 07:53 PM
  • kennethmauricio: Hi saan po dito pwede mag post ng for sale properties?
    Mar 11, 2019, 07:58 PM
  • jefsanity: @mark_lee isipin mo anu passion mo at skills mo...
    Mar 11, 2019, 06:52 PM
  • sniperaj09: BAGO LANG
    Mar 10, 2019, 11:16 AM
  • mark_Lee: magandang investnent ngayon sa 30k suggest naman keo mga boss
    Mar 10, 2019, 03:07 AM
  • perlymelad: E-BOOK PO SIR @JEFSANITY
    Mar 09, 2019, 05:40 PM
  • perlymelad: @jefsanity Good Morning Po
    Mar 09, 2019, 05:39 PM
  • perlymelad: Good Day everyone
    Mar 04, 2019, 06:24 PM
  • jefsanity: anu ang small business na yan?
    Feb 28, 2019, 07:40 PM
  • perlymelad: Good Day Everyone
    Feb 28, 2019, 03:31 PM
  • perlymelad: Hello everyone, CAN ANYONE HELP ME SUGGEST ANY SIDE TOPIC  / PROBLEM OF  STARTING SMALL BUSINESS?
    Feb 28, 2019, 06:58 AM
  • perlymelad: Present, 1st time po
    Feb 25, 2019, 06:17 PM
  • jd2281: newbie po
    Feb 25, 2019, 12:26 PM
  • ejllantino: present... 1st time
    Feb 24, 2019, 07:14 PM
  • rheiel: present. first time
    Feb 24, 2019, 11:04 AM
  • MELVIE: thanks po
    Feb 24, 2019, 12:50 AM
  • steph123: hello po, ask lang po, ilang months po ba ang hihintayin after mo ma process ang mga documents para maitama ang mali sa NSO?
    Feb 11, 2019, 03:13 PM
  • Deborah: newbie here.thanks
    Feb 08, 2019, 02:17 PM
  • marj0503: hello im newbie here
    Jan 31, 2019, 10:53 AM
  • yan24: any one here planning to start a tesda skill training center
    Jan 26, 2019, 12:07 PM
  • manzjon: Forex trading forum
    Jan 25, 2019, 02:16 PM