PMT Forum
Do you want to make money today? :)

How make a tabled stock portfolio in excel

GIG · 12 · 4382

GIG

  • PMT Idol 1000
  • ****
    • Posts: 1,359
    • Likes Received: +7/-0
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
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
Did you buy something today rhipolito? the market was screaming Sale! Sale! Sale!


rhipolito

  • Forum Master 300
  • ***
    • Posts: 387
    • Likes Received: +0/-0
ipit ako GIG...hahahah..long term investor...no more funds to bottom fish


freefront

  • VIP 4500
  • *****
    • Posts: 4,885
    • Likes Received: +9/-0
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,985
    • Likes Received: +4/-0
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
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,885
    • Likes Received: +9/-0
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
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,985
    • Likes Received: +4/-0
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
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
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.
  • Doni_megaworld: Happy Saturday! who's online?
    Today at 08:48 AM
  • Doni_megaworld: Good Morning! who's online?
    Yesterday at 07:34 AM
  • Doni_megaworld: Hi Guys, who's online?
    May 29, 2020, 03:08 PM
  • Doni_megaworld: good morning! who's online?
    May 28, 2020, 08:47 AM
  • Doni_megaworld: good morning guys!
    May 27, 2020, 09:47 AM
  • Doni_megaworld: work work
    May 26, 2020, 02:47 PM
  • Niel Jhacoubs: What's good in this afternoon?
    May 26, 2020, 02:01 PM
  • Doni_megaworld: agreed
    May 25, 2020, 03:30 PM
  • Niel Jhacoubs: @lyndrey depende siguro sa seller, so kung mag order kayo online much better na kilala niyo at trusted
    May 25, 2020, 02:29 PM
  • Doni_megaworld: kumusta? sino mga online?
    May 24, 2020, 03:24 PM
  • lyndrey: Do not buy PPE online. Either expensive overpriced or totally damaged or cannot be used
    May 24, 2020, 01:37 PM
  • lyndrey: Online Scammers/online shopping cheats are active with stay at home regimen.
    May 24, 2020, 01:34 PM
  • Niel Jhacoubs: @razee22 Parang di na po nacoconvert into money ang load po.
    May 12, 2020, 04:07 PM
  • razee22: any advised or recommendation?
    May 12, 2020, 11:16 AM
  • razee22: Paano po ba iconvert ang load into money like Paypal, Paymaya or Gcash?
    May 12, 2020, 11:15 AM
  • razee22: paano po ba iconvert ang load into money? Paypal or Gcash
    May 12, 2020, 11:14 AM
  • Niel Jhacoubs: Hellooooo, okay lang ba kayo dyannnn?
    May 11, 2020, 11:40 AM
  • theimprobableone: hi
    May 05, 2020, 11:37 AM
  • freelance_treasure_hunter: hello all...
    Apr 28, 2020, 04:26 PM
  • Niel Jhacoubs: Can someone save me?
    Mar 24, 2020, 01:39 PM
  • Niel Jhacoubs: I'm tired.
    Mar 24, 2020, 01:39 PM
  • Victorvargas: Hello sa lahat po new lang po dito. Hope na marami matutunan ako about business.
    Mar 21, 2020, 09:43 PM
  • Niel Jhacoubs: Yoh, its been a while.
    Mar 17, 2020, 10:33 AM
  • demi07: Hello everyone! New in pinoymoneytalk :)
    Mar 14, 2020, 01:24 PM
  • theappswitch: Hi!
    Feb 25, 2020, 03:14 PM
  • mike2020: hi po sa inyo lahat
    Feb 23, 2020, 11:52 PM
  • Jayjayqwe: hi po
    Feb 19, 2020, 11:27 PM
  • tirs d pip: hello
    Feb 18, 2020, 02:32 PM
  • Ace1105: Hello sa lahat...  :hello:
    Feb 07, 2020, 09:46 AM
  • FREYAHYARA: Anyone knows where Ig shops like styledom.ph, thriftme, instashoppeavenu and the likes get their items for selling. Would like to venture in the same business but would like to know where and who the suppliers are
    Jan 28, 2020, 09:38 PM
  • Niel Jhacoubs: To bpc85 kung nakalagay ang inyong names sa beneficiary list ng iyong ama kay may makukuha po kayo.
    Nov 28, 2019, 02:18 PM
  • Niel Jhacoubs: Hellooooo, its been a while. :)
    Nov 28, 2019, 02:16 PM
  • miakashidou31: hi , meron po ba ditong nagta transfer sa paypal?
    Nov 05, 2019, 01:46 PM
  • whennedy: good noon po
    Oct 18, 2019, 12:04 PM
  • edzlpdac: goood day po sa lahat!
    Oct 12, 2019, 03:37 PM
  • bpc85: ask lng sana ako kung may makukuha ba kami ng Mama ko at ako or mga kapatid ko sa benepisyo ng aming Ama bilang membro ng AFPSLAI. thank you po.
    Sep 27, 2019, 12:52 PM
  • bpc85: Hi guys, im new here,.
    Sep 27, 2019, 12:52 PM
  • Anaperdi: Hi just want to know if meron dito who is into boundary hulog ng motorcycle? For mga riders ng courier. Pwede ba magpashare how it works? Naisip ko lang baka pwede ko ibusiness, meron na pala mga posts online pero di pa clear ang system sa akin. thank  you sa makakapansin
    Sep 21, 2019, 11:32 AM
  • Anaperdi: Hi newbie here..
    Sep 21, 2019, 11:19 AM
  • jprios: May contact ba kayo kung sakali?
    Sep 18, 2019, 06:11 PM
  • jprios: Sino pwedeng mahiraman dito?
    Sep 18, 2019, 06:09 PM
  • kfg52: Present!
    Sep 15, 2019, 07:26 PM
  • juju31: san po ba may nag papahiram dito
    Sep 14, 2019, 07:18 PM
  • juju31: hello
    Sep 14, 2019, 07:18 PM
  • FutureGizmo: Thanks julz! Medyo ganun nga trabaho namin ngayon, to monitor spam posts kasi ang daming spam bots. But thanks for reporting. Auto-ban agad ang spammers sa amin haha
    Sep 07, 2019, 12:17 PM
  • julz: @FutureGizmo si @nikkikv18 just posted more than 10 posts about porn sites. hay, spam.
    Sep 03, 2019, 01:05 PM
  • Cid_brent: sino po dyan pwede magbigay send ng paypal funds and I will pay you using gcash?
    Aug 25, 2019, 08:58 PM
  • Meghan91811: Sino po may alam about Rice Farming...
    Aug 25, 2019, 07:44 AM
  • roselove: hello po sa inyo... newbie here
    Aug 06, 2019, 03:31 PM