Lazada Philippines
Lazada Philippines

Sunday, September 6, 2015

How to use Sample MS EXCEL spreadsheet? Philippine Stock Monitoring Tool for better result.


My goal is to show how to use ms excel sheet in monitoring stock prices. Ms Excel sheet is a widely used software in offices and other related data analysis. Having a tool in monitoring stock prices will allow you to develop certain rules. The following rules stated on the images is a sample strategy that you can adapt and you can improve as you go along the way.


This article will show to you how to think for yourself, ignore tha day to day noise and make profitable investment decision on your own analysis and set of rules.

Successful Monitoring is built on three core principle:


1. Knowing when to buy and sell.
2. Doing your homework.
3. Having a margin of safety.


UPDATED MONITORING LINK:
SEPTEMBER WEEK2

DOWNLOAD HERE:
https://drive.google.com/file/d/0B2FEBrxaHeHiSTNCMEdEaEVXbzg/view?usp=sharing

VISION: sharing ideas to develop and simplify  monitoring  of stocks using MS EXCEL SHEET.
To develop strategy that is effective in minimizing risk while maximizing stock profit.
To establish a trading plan before the start a market and not by simple "tsamba".

STEP 01: STRATEGY
SPREADSHEET 02: SUMMARY OF EQUITY
NOTES: this can be edited


STOCK PICK - based from your drop down filtered spreadsheet 01: fundamentals link  to each company name (stock pick 01-03(medium term), stock pick 04-05 (short term), stock pick 6 - trading) gray color

CODE/SYMBOL- based from your drop down filtered spreadsheet 01: fundamentals link  to each company SYMBOLS

TYPE - based from your drop down filtered spreadsheet 01: fundamentals link  to each company CATEGORY

juan dela cruz - you can put your name to customize your model portfolio.

ALLOCATION- percentage allocation of each stock is dependent on your preference.

20%- total amount of equity to be traded is dependent on your risk reward ration at that time for me I used 20% of my total portfolio in and out in the market due to global weakness. (you just change the percentage then the amount will be computed automatically)

STRATEGY INVESTIGATION- the real purpose of these is to set your own strategy plan based from an effective previous trade plan.

TIME MANAGEMENT -  allocation of your time specially to those who are not full time in trading ( I suggest trade plan is start, noon break and closing time.


STEP 02: DATA INPUTS 


SPREADSHEET  06 WATCH LIST DATA INPUT
NOTES: DATA INPUTS

A. Copy quotes watch list (from col financial) 
    - watch list is based from your 12 top picks on spreadsheet 01: fundamentals (yellow color)
    - use google chrome browser.

B. Paste special - VALUE on spreadsheet "current trading watch list"
    - watch list is based from your 12 top picks on spreadsheet 01: fundamentals (yellow color)
    - use this one to update prices.

C. Paste special - VALUE on spreadsheet "today"
    - watch list is based from your 12 top picks on spreadsheet 01: fundamentals (yellow color)
    - inputs used to update spreadsheet 07: monitoring stock prices
      (red fill-lower stock price previous, green fill- higher price previous) 
   - closing period

always update lower tables on spreadsheet 06 - copy paste special (yesterdays price list) 
closing period


STEP 03: DATA INPUT 02 - AVERAGE PRICE

COL FINANCIAL QUOTES - TRADED PRICE
SPREAD SHEET 03 TECHNICAL ANALYSIS

SPREADSHEET 03 : TECHNICAL ANALYSIS
NOTES: DATA INPUTS

A. Copy average price yesterday and paste special -  value a@ previous column

B. Data Input (from col financial quotes-traded price) 
    - average price traded for the day
    - after stock market closing

legend: (green fill -  higher average price, red fill - lower average price)

STEP 04: DATA INPUT 03 - OPENING PRICE 
   - purpose to determine the color of the candlestick and when there is a DOJI

COL FINANCIAL: QOUTES
  •  Input Opening price @ opening price table (from col financial)


STEP 05: DATA INPUT 04 - GRAPH DATA INPUT ANALYSIS

GRAPH 01

SPREADSHEET 03: TECHNICAL ANALYSIS

NOTES: DATA INPUTS

For MACD only
A. Copy MACD value and paste special -  value a@ MACD previous column 

B. Data Input (from graph provided by col financial - quotes-charting) 
    (use internet explorer in chart)
SUPPORT - A
RESISTANCE - B
5MA (5 day moving average) - C
MACD - D
Notes:
 Each trader have a different time frame and that makes every trader have different analysis. beware tips from other people. You might not know you have a different time frame.

STEP 06: TECHNICAL TABLE ANALYSIS


SPREADSHEET 06 & 04

ACTION TO BE TAKEN: 

    A. EXECUTING A BUY
  •  If you're analysis is to BUY ALI - Input BUY1 - means you want to buy ALI with a price order of 35.10(price is based from your analysis). Automatically your plan to buy ALI will be shown on spreadsheet 04 ALI 1 row ( see highlighted gray ) 
  • Then if it is executed just type the word "EXECUTED" and re input the value 35.10 (highlighted gray). 
  • If not executed just delete the word BUY1 on the table
  • Then on your next buy order, input BUY2  and so on until BUY4.
  • I divided into 4 transaction to minimize trading 
Notes: 
On the table provided, you can put your reason of buying that stock that will serve as your record.If you put a BUY1....4 on action taken table, you will see that following data will be automatically recorded (percentage gain/loss,total traded amount and percentage) and also on spreadsheet 08 cash flows.computations on fees, commissions are already included at the transaction)

SPREADSHEET 04 & 06
   B. EXECUTING A SELL
  • If you're analysis is to SELL ALI - Input SELL1 - means you want to sell ALI with a price order of 38(price is based from your analysis). Automatically your plan to sell ALI will be shown on spreadsheet 04 ALI 1 row ( see highlighted gray )
  •  then if it is executed copy the gain/loss value and paste special besides executed word and you can now delete "EXECUTED" on the table to record the transaction. 
  • If not executed just delete the word SELL1 on the table
  • Then on your next sell order, input SELL2  and so on until SELL4.
  • I divided into 4 transaction to minimize trading for stocks 01 to 3.
Notes:
On the table provided, you can put your reason of selling that stock that will serve as your record. If you put a SELL1....4 on action taken table, you will see that following data will be automatically recorded (percentage gain/loss,total traded amount and percentage) and also on spreadsheet 08 cash flows. Computations on fees, commissions are already included at the transaction).


STEP 06: CASH FLOWS

SPREADSHEET 08 - CASH FLOWS

Note: adjustment is in the part of equity trade to be equal on your portfolio. Capital input and additional capital will be placed on ADD-ON column every month. 

- The table shows how much you gain  or loss by daily, weekly, monthly. 
- IPO and dividends is also included here for the computation of equity value.


UPDATED TABULATION 09042015
TA09042015 - TECHNICAL ANALYSIS

TIPS:
Use the QUOTE - SIX PACKS by col financial to monitor the 6 STOCK PICKS



RECOMMENDED LINK TO NEWBIES : CLICK HERE
      
Free Seminars:

Free Webinars

How to invest in the Philippine Stock Market for beginners:


If you have questions with regards to the sample template being shared, Please don't hesitate to post a comment on this blog or you can contact me @ philippine.stocksdiary@gmail.com





73 comments:

  1. Sir, want to try your excel monitoring template but the download link is not working.

    ReplyDelete
    Replies
    1. Sir D' Trader, got it, many thanks. will study your template. Thanks again for sharing your ideas.

      Delete
  2. Sir, I tried your download link but unfortunately it doesn't work.

    ReplyDelete
  3. I even sign up for your newsletter as well as filling up the contact form but until now I did not receive your excel worksheet... Anyway, I am still hopeful that you will still share your excel sheet.

    ReplyDelete
  4. Thanks sir D'Trader. I got it already from your link. God bless you.

    ReplyDelete
  5. hi there sir. can u share also this file to my email.thnk you

    ReplyDelete
  6. kindly psend naman po s akin ng excel copy.. very much appreciated.. georgette.carpio@yahoo.com

    ReplyDelete
  7. Sir kindly send me a copy of the excel format.. Thanks and Advance Merry Xmas. jdowatancpa@gmail.com

    ReplyDelete
    Replies
    1. sir pwede mo syang idownload yung link po sa taas

      Delete
  8. Sir, the one posted above is protected and need some password.Can i please request a sample that is unprotected.That would be a great help.Thanks in advance.Merry Xmas, here is my email allanaragon316@gmail.com

    ReplyDelete
  9. Protected po yung excel sheet sa link, hindi maedit. Baka po pede i update yung excel file sa link to be unprotected. Thanks.

    ReplyDelete
    Replies
    1. punta sa data sir then unprotect password: JMCA2015

      Delete
  10. Sir, please send me a copy of excel format at Shem.mancera@gmail.com. Thanks for sharing! God bless!

    ReplyDelete
  11. Sir, OFW from UAE please send me a copy of excel format at jianviernes@yahoo.com Thanks!

    ReplyDelete
  12. OFW from Qatar po...please send me copy of your exel templates at mhytch_sean1015@yahoo.com salamat po....

    ReplyDelete
  13. This comment has been removed by the author.

    ReplyDelete
  14. Excel is a software program from Microsoft basic excel for beginners that is a part of Microsoft Office. Excel calculations is compiled for making and altering spreadsheets that are spared with a .xls expansion. It's general uses in corporate cell-based figuring, turn tables, and different diagramming devices. With an Excel spreadsheet, you could make a month to month spending plan, track costs of doing business, or sort and compose a lot of information calculations in spreadsheets of Excels.

    ReplyDelete
  15. sir pa send po ng copy sa email sir. pseudiom@gmail.com
    tsaka po PW nang spreadsheet for editing.
    thank you po.

    ReplyDelete
  16. pa send po sa akin mga sir danielmatangga@gmail.com

    ReplyDelete
  17. philip_ash26@yahoo.com
    me too

    ReplyDelete
  18. Hi sir,
    Ano po ung ginamit nyo sa charting? Ung interactive financial chart using java. San po pwd maka download nun?

    Please email me at gimdawis01@gmail.com
    thanks po.

    ReplyDelete
  19. please sent me a copy sir.

    Thank you for this.

    racmagboo@gmail.com

    ReplyDelete
  20. do you have an updated version of this excel file? i can't find BDO, ;(

    ReplyDelete
  21. Pashare po sir

    ronniebsme@gmail.com

    ReplyDelete
  22. Pa share po..eric@rae-engrs.com..thanks

    ReplyDelete
  23. Sir... pa share din po...esjtinoy@yahoo.com

    Thanks.

    ReplyDelete
  24. Sir pa send din po ng template mrching3281989@gmail.com malaking tulong ito sa akin para madagdagan ang aking ka alaman

    ReplyDelete
    Replies
    1. SIR JM D TRADER, PWEDE MAKAHINGI NG TEMPLATE KASI PASSWORD PROTECTED PO YUNG NAKA ATTACHED N LINK DITO S BLOG MO. ITO YUNG E MAIL KO APR_JCIYORK@YAHOO.COM
      THANKS

      Delete
  25. Sir pa send din po ng template mrching3281989@gmail.com malaking tulong ito sa akin para madagdagan ang aking ka alaman

    ReplyDelete
  26. sir pwede po ba makahingi ng copy? roygbivmenes@gmail.com yan po email ko salamat po!

    ReplyDelete
  27. Sir pwede pasend ng copy sa elhnerjimenez@gmail.com.ph
    Thank you in advance sir

    ReplyDelete
  28. Sir pasend naman poh ng copy sa todyabenir@gmail.com
    maraming salamat poh...

    ReplyDelete
    Replies
    1. Sir pwede pasend ng copy sa obet.stocks@gmail.com
      Thanks so much Sir.

      Delete
  29. Sir pwede pasend ng copy sa obet.stocks@gmail.com
    Thanks so much Sir.

    ReplyDelete
  30. Sir pwede pasend ng copy ng excell monitoring nyo sa re0103uz@gmail.com
    thank you po

    ReplyDelete
  31. Hi sir, can I also have a copy of the template? I've been looking for this for the longest time. Thank you so much! here is my email. n.nereen18@gmail.com ! :)

    ReplyDelete
  32. sir pwd pa send ng copy, mannyubi@gmail.com.. thanks po

    ReplyDelete
  33. Sir, pwede pahingi na rin ako ng updated copy? Thanks very much and God Bless!

    ReplyDelete
  34. I had used MarketXLS for this. It works for me great.
    https://marketxls.com/

    ReplyDelete
  35. Hi sir, may I have a copy of your monitoring?
    psptopacio@gmail.com

    Thank you and God bless. :)

    ReplyDelete
  36. Sir, pwede pahingi na rin ako ng updated copy?
    kindly send po to newbie_investor@mailinator.com

    Thanks very much and God Bless!

    ReplyDelete
  37. Intraday traders always face inherent risks that exist in the stock markets. Price volatility and daily volume are a couple of factors that play an important role in the stocks picked for daily trading. Traders must not risk over two per cent of their total trading capital on a single trade to ensure the right risk management. So here are a few tips shared to make profit in
    intraday trading.

    ReplyDelete
  38. pa share din po sir, please. thank you. nuicog@gmail.com sana mapansin po.

    ReplyDelete
  39. sir , really nice piece of work,
    can u pl guide how can i change data source as i am from India
    Thx in adv
    Deepak
    deepakjosh71@gmail.com

    ReplyDelete
  40. Sir, can you send this file to me at lemuelbustamante@hotmail.com. I am looking for a journal for my trades. Thanks and more power to you.

    ReplyDelete
  41. Hi sir.. Can u also send me a copy of ur files.. pobi_1_kenobi@yahoo.com..tnx

    ReplyDelete
  42. Hi Sir can you send me a copy of ur file at agapetrade1@gmail.com

    Thanks!

    ReplyDelete
  43. Sir pasend din..nakalock yung file.. mitchmike0401@gmail.com

    ReplyDelete
  44. Sir pasend din nang xls calculation mo. Thanks! marlonoyao1979@gmail.com

    ReplyDelete
  45. sir good eve pa send naman sir.. markpaduga@gmail.com

    ReplyDelete

Lazada Gadgets 2017

Lazada Philippines

D' trader

My photo
"To try your best to acquire an unflappable and supremely adaptable mind to enable you to see things as they actually are without clinging to preconceived notions" I love doing things that fascinate me. I love numbers. I really do. I found something that relates to my interest and its the stock market. I am a trader for over 5 years from now. Before that I try to invest in VUL which defines a combination of insurance and Equities and Bonds. Today my investments already matured and its worth it of the time when I struggle to pay for the premiums. Ive been challenge to work for this and I learned a lot about finance and investment. Since I am Civil Engineer in QS side, Managing Finance in the construction industry give me a broad knowledge in making expenditures and savings and we call that as VALUE ENGINEERING. I love writing about finance wherein I learn it from my own experience. I do love helping other people specially to my fellow OFW who want to manage their money properly. Come and join me to research more the world of INVESTING.
Lazada Philippines