第一个方法：
比如Google股价，代码是GOOGL，在 Excel里输入以下函数：
=WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s=GOOGL&f=l1")
（注：这函数只适合用于 Excel 2013版本，数据由Yahoo服务器提供。较旧的 Excel 可以用下面第二个方法）这函数输出的结果是Text格式，不能作数学运算。用=VALUE(...)函数将结果转换成数字值。有时Text里还包含一些不能打印的符号，解决方法是需要先用=CLEAN(…)来清除掉。
=VALUE(CLEAN(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s=GOOGL&f=l1")))
或者用=NUMBERVALUE(...)相同的功能。
=NUMBERVALUE(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s=GOOGL&f=l1"))或者用=NUMBERVALUE(...)相同的功能。
如果要查看马股也能，比如Maybank，代码是1155.KL
=NUMBERVALUE(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s=1155.KL&f=l1"))
（注：这函数支援大部分马股，但一些warrant不支援）
除了马股，新股，港股，同样能应用这函数。
example:
A1=GOOGL
B1=NUMBERVALUE(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&A1&"&f=l1"))
C1=WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&A1&"&f=n")
函数里的参数&f=l1代表last traded price，&f=n是公司名字。
第二个方法：
比较旧的 Excel 版本（2013以下），不支援=WEBSERVICE可以用以下 Marco 程序语言，不懂Marco 程序没关系。Ali 找到已经准备好了的 file ，供大家下载。（感谢Daniel Ferry of Excelhero.com 提供，原文Hhttp://chandoo.org/wp/2010/06/02/excel-stock-quotes/）。当启动时，可能需要 Marco enable ，才能使用。
下载
Stock Quote- activerange_yahoofinance_excelhero.com
(按连线时，可能有广告pop-up。这文件是安全使用。）
把股票代码输入进A区 然后按 Enter 键，股价就会陈列出来。B区可以自由选项，Last traded price, Day High, Day Low, Volume 等等。C区是AutoRefresh，如打勾每60秒将自动更新。
第三个方法：
另一个也是 Marco-ed file，其功能有列表出以往股价从某日期到某日期。（感谢Samir Khan提供，http://investexcel.net/multiple-stock-quote-downloader-for-excel/）
下载
Multiple-Stock-Quote-Downloader.zip
(按连线时，可能有广告pop-up。这文件是安全使用。）
填入Start date与End date日期，输入股票代码，按【Get Bulk Quotes】
下方会出现个股的Tab，如下
你也可以把资料输出成csv格式文件，只需要打勾”Write to CSV"就行了。
第四个方法：
这是由Yi Hu所篇写的marco-ed file，特点是已经有了图表，的确是懒人的好工具。
下载
Excel Clout - Stock_Quotes
（网址：http://www.excelclout.com/stock-quotes-in-excel/。注：按下载连线时，可能有广告pop-up。这文件是安全使用。）
以上四个方法，读者试试那个比较适合自己。
后语：
有人说成功金律之一【做好记录，成功一半】。问问大家有没有做交易的记录习惯？Ali把所有交易单据都收得好好地，打从一开始开CDS的customer's carbon copy，到现在有好几个文件夹了，大约能放满一箱A4纸盒。不过现在有用e-statement，减少用纸张，环保一些。
Ali习惯把每一项交易记录在Excel里，以便做投资组合分析和自我审查。不过现在已有很多免费/付费软件，网页，apps可以帮你做portfolio。例如i3investor.com, jstock, klse screener
以前股票是没有网上交易平台，我们是通过call remiser 下单。下单后，要知道实际付款数目，要等收到交易单据，单据收到时已经过了一两天，然后要到在柜台付现金/支票（以前没有 online payment），或到银行付支票后，再 call Remiser 说付款了。在T+3的制度，时间蛮吃紧的，迟了付费可能被罚款。所以 Ali 用 Excel 设定好 formula计算出该要付的数目，然后准备好支票，也可以核实一下。现在是online system，无需像以前那样，交易单据由email发送，online付款，一切简单了。
有了这些资料，可以做投资组合计算、分析。想要知道目前组合总资产值，需要输入每股价格，再总和。当时没有自动化，全是人手输入。如今Excel有自动更新股价，一切变成简单得多了。
如果你有兴趣，看更多小工具：
如何计算投资回酬，好用的XIRR函数介绍
I am a newbie, may i have your advice on the following pls:-
ReplyDelete(1) dividend. Say if declared dividend rm0.010, 1000 units, i thought the amount receivable is rm10? But the actual amount that i have received seem lower than this amount. Is there some sort of tax or agent's fee been deducted from the dividend?
(2) what is PAL? Say if rights issue price at 1.00, but the amount shown 1.50 (including PAL). How is the PAL be determined? Is PAL varied by all kind of factors, eg banks, or underlying value of the shares etc?
Thanks in advance.
hi vivi
Delete(1) most of company dividend are Single tier which no tax reduced. You may check the dividend voucher, what is the charge. (2) PAL, i dont know also, never come across this problem.
Thanks for the prompt response. I will further check my dividend voucher, i definitely did not receive the full amount. PAL make my shares average price increase. Is it normal to be charged / deducted for any bank charges / agent's fee (PAL) for dividend received and rights issue? Thanks for your advice.
Deletebased on my experience with several brokers, (HLebroking, itrade, public investment bank), I received full amount of dividend (single tier), there is no bank charge/fee. your case is not normal to me. Please clarify with your broker firm. why is it charged. you are welcome to share your case of experience
DeleteAli...my dividend voucher stated full amount, i.e. RM10, but i received only RM4.50 as my public bank investment account agent said it's service charge ranged from RM2.50 to RM5.50. OMG, it's just dividend received by cheque and no extra service from the agent, i was charged RM5.50...i am still reviewing my documents and i would need your advice, how can i private message you / chat privately? thanks
DeleteIt means 5.50 fee for issuing the dividend cheque?
DeleteI can be contacted by email boonlee77@gmail.com, or chat with google Hangouts.
Hi Ali, it's my remiser / bank charges. I am using public bank e-trading, nominee account. I have added you on google hangouts. Have a good weekend
Deletethanks for sharing!
ReplyDeleteyou are welcomed
DeleteHi Ali, thanks for sharing. You may try out below as well.
ReplyDeletehttp://www.investmentmoats.com/stock-market-commentary/portfolio-management/introducing-our-free-stock-portfolio-tracker-spreadsheet/
You can modify to your home currency and able to refer it online whenever internet available through google spreadsheet.
I am using it recently and its work pretty well for KLSE.
Will share it on my blog once I've managed to figure out some formula in excel.
- J丰 -
thank you for sharing. Google's spreadsheet is another option. It works in cloud environment, it can be opend everywhere, anytime
DeleteAli, 謝謝你教的這個 excel 方便小貼士，非常好用，已經植入我的檔案了。
ReplyDelete有一點可以補充的是，因為使用這個 webservice ，重開 excel 時它不會自動更新，所以大家可以 refresh 整個檔案 (Ctrl-Alt-F9)
对，用这个可以 refresh更新了。谢谢补充
Delete