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

Go to Link: https://www.blogger.com/blogger.g?blogID=1574269398077451413#editor/target=post;postID=7711473229695250172

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 Stock Market 101 Seminar

Free Webinars

   PSE Academy free Webinars

How to invest in the Philippine Stock Market for beginners:

  Smart Pinoy Investor by Omeng Tawid

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