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 |
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 |
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 |
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
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
Sir, want to try your excel monitoring template but the download link is not working.
ReplyDeletesent to your e-mail sir
DeleteSir D' Trader, got it, many thanks. will study your template. Thanks again for sharing your ideas.
DeleteLink is not working Sir.
ReplyDeletesir sent nah. please check your e-mail
DeleteSir, I tried your download link but unfortunately it doesn't work.
ReplyDeleteI 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.
ReplyDeletesir sent nah. please check your e-mail
DeleteLink already fixed
ReplyDeleteThanks sir D'Trader. I got it already from your link. God bless you.
ReplyDeletehi there sir. can u share also this file to my email.thnk you
ReplyDeleteplease click above link
Deletekindly psend naman po s akin ng excel copy.. very much appreciated.. georgette.carpio@yahoo.com
ReplyDeleteSENT PO
DeleteSir kindly send me a copy of the excel format.. Thanks and Advance Merry Xmas. jdowatancpa@gmail.com
ReplyDeletesir pwede mo syang idownload yung link po sa taas
DeleteSir, 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
ReplyDeleteProtected po yung excel sheet sa link, hindi maedit. Baka po pede i update yung excel file sa link to be unprotected. Thanks.
ReplyDeletepunta sa data sir then unprotect password: JMCA2015
Deletepassword: JMCA2015
ReplyDeleteSir, please send me a copy of excel format at Shem.mancera@gmail.com. Thanks for sharing! God bless!
ReplyDeletesent already sir
DeleteSir, OFW from UAE please send me a copy of excel format at jianviernes@yahoo.com Thanks!
ReplyDeletesent sir
DeleteNarecieve ko na po maraming salamat
DeleteOFW from Qatar po...please send me copy of your exel templates at mhytch_sean1015@yahoo.com salamat po....
ReplyDeletesent
DeleteThis comment has been removed by the author.
ReplyDeleteExcel 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.
ReplyDeletesir pa send po ng copy sa email sir. pseudiom@gmail.com
ReplyDeletetsaka po PW nang spreadsheet for editing.
thank you po.
pa send po sa akin mga sir danielmatangga@gmail.com
ReplyDeletesent
Deletephilip_ash26@yahoo.com
ReplyDeleteme too
sent
DeleteHi sir,
ReplyDeleteAno 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.
please sent me a copy sir.
ReplyDeleteThank you for this.
racmagboo@gmail.com
sent already to your e-mail sir
DeleteSir pashare naman po. :)
Deletegrldbb219@gmail.com
sent na sir
Deletedo you have an updated version of this excel file? i can't find BDO, ;(
ReplyDeletesent sir
DeletePashare po sir
ReplyDeleteronniebsme@gmail.com
sent sir
DeletePa share po..eric@rae-engrs.com..thanks
ReplyDeleteSir... pa share din po...esjtinoy@yahoo.com
ReplyDeleteThanks.
sent
DeleteSir pa send din po ng template mrching3281989@gmail.com malaking tulong ito sa akin para madagdagan ang aking ka alaman
ReplyDeletesent
DeleteSIR 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
DeleteTHANKS
Sir pa send din po ng template mrching3281989@gmail.com malaking tulong ito sa akin para madagdagan ang aking ka alaman
ReplyDeletesent sir
Deletesir pwede po ba makahingi ng copy? roygbivmenes@gmail.com yan po email ko salamat po!
ReplyDeletesent
DeleteSir pwede pasend ng copy sa elhnerjimenez@gmail.com.ph
ReplyDeleteThank you in advance sir
Sir pasend naman poh ng copy sa todyabenir@gmail.com
ReplyDeletemaraming salamat poh...
Sir pwede pasend ng copy sa obet.stocks@gmail.com
DeleteThanks so much Sir.
Sir pwede pasend ng copy sa obet.stocks@gmail.com
ReplyDeleteThanks so much Sir.
Sir pwede pasend ng copy ng excell monitoring nyo sa re0103uz@gmail.com
ReplyDeletethank you po
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 ! :)
ReplyDeletesir pwd pa send ng copy, mannyubi@gmail.com.. thanks po
ReplyDeleteSir, pwede pahingi na rin ako ng updated copy? Thanks very much and God Bless!
ReplyDeleteI had used MarketXLS for this. It works for me great.
ReplyDeletehttps://marketxls.com/
Hi sir, may I have a copy of your monitoring?
ReplyDeletepsptopacio@gmail.com
Thank you and God bless. :)
Sir, pwede pahingi na rin ako ng updated copy?
ReplyDeletekindly send po to newbie_investor@mailinator.com
Thanks very much and God Bless!
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
ReplyDeleteintraday trading.
pa share din po sir, please. thank you. nuicog@gmail.com sana mapansin po.
ReplyDeletesir , really nice piece of work,
ReplyDeletecan u pl guide how can i change data source as i am from India
Thx in adv
Deepak
deepakjosh71@gmail.com
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.
ReplyDeleteHi sir.. Can u also send me a copy of ur files.. pobi_1_kenobi@yahoo.com..tnx
ReplyDeleteHi Sir can you send me a copy of ur file at agapetrade1@gmail.com
ReplyDeleteThanks!
Sir pasend din..nakalock yung file.. mitchmike0401@gmail.com
ReplyDeleteSir pasend din nang xls calculation mo. Thanks! marlonoyao1979@gmail.com
ReplyDeletesir good eve pa send naman sir.. markpaduga@gmail.com
ReplyDelete