Portfolio Perfection

Did you know that Excel can link to online Share & Equity data? Or we can do it for you!

Following on from our June 2013 monthly tip “Get Off the Web!” here’s how to create an Excel spreadsheet that keeps track of your share portfolio in real time.SP32-080820131436pg

In a new workbook click on the Data menu tab, then Existing Connections. Standard links and ones that you have created (for another Tip of the Month in the future) will appear on the menu options. See the screen shot on above. For this exercise highlight the “MSN Money Central Investor Stock Quotes” option, and the Open button.  Here Excel asks where to put the data. Opt for Cell A1 in this example, and OK it.

SP32-080820131458pg

Excel then asks for a parameter value. The required value is a symbol that represents the Stock code in the MSN Money website. For how to find the stock code for your particular share see the EOXL Quick Tip below, but just for now enter “GB:RR/”. Check the “Use this value..” box and press OK.

In a few moments a large amount of data about Rolls Royce Holdings PLC (GB:RR/) fills the spreadsheet from the MSN Money website. Lots of formatting and hyperlink creation has taken place, but if you opted for cell A1 earlier, the latest share price for Rolls Royce Holdings, according to MSN Money is shown in cell D4.

SP32-080820131555pg

EOXL Quick Tip:To exclude all the formatting and hyperlinks being added when Excel asks where to put the data click on the “Properties..” button and check the “Preserve cell formatting” box. See screen shot below

Properties Box

Properties Box

To finish off select Connections on the Data tab and click on the Properties button. Check all the  boxes under Refresh Control and change the refresh interval to 1 minute. Now watch cell D4 change every minute as the stock value changes.

But if you cannot wait hit the refresh button.

SP32-090820131658pg

EOXL Quick Tip: To get the Stock Symbols visit the MSN Money website and enter your search in the “GET QUOTE” box. See the screen shot below. For UK stocks type in ” GB: ” before the intials of the company.  An alternative is to search Google for your company and add “ticker symbol” to the search box.  BTW The  UK FTSE 100 symbol is GB: UKX.

An excellent example spreadsheet with multiple stock quotes is available as a free download on our sister site accountancytemplates.com

SP32-07082013113500

We can now create your Portfolio on an Excel spreadsheet and send it for you to monitor.

For your stocks and equities on the London, New York, Paris and German exchanges – just use our Contact Us form with a list your companies for a quick quotation.

About Derek