Excel 2010 and Web Queries

[jimbo45]

Hi all

Any decent Links to how to create Web Queries for EXCEL -- I'd like to get quotes for stocks from say YAHOO finance or MSN Money with the data being refreshed and the spread sheet re-calculated automatically every few minutes.

The old template MS had (MSN Money stock quote addin for doing this has now been removed from their web site. This was actually a useful free addin to EXCEL.

Cheers

jimbo

 

[Irene]

Some instructions here...
How to Use Web Query Files With Access | eHow.com

Oops, sorry these are for use with Access.

Maybe this will help - How to Import Online Data into Excel 2010 with a Web Query - For Dummies

 

[jimbo45]

Hi there
Thanks for the link but what I want to do is for example just give the name of Stock for example MSFT (Microsoft) and then get the data automatically.

The GET external data is fine but needs a lot of manual input -- you need to remember the URL each time and then select the data table and import.

I'd like to automate this as much as possible like the old MSN template did -- not sure why they removed it BTW it was very useful.

Cheers

jimbo

Added -- Found the file on rapidshare -- can't post the link but google on msnsq.zip

Works fine on EXCEL 2007 haven't got access to EXCEL 2010 until I get home

Can't understand why they removed this file.

All the data is PUBLIC DOMAN.

Cheers
jimbo

 

[Golden]

Hi Jimbo,

This quite tricky to accomplish for several reasons (once explained to me and my eyes glazed over). I trade regularly on the ASX (Australian Stock Exchange) and manage all my shares in Outlook2010. I had to do a few things to get the information I needed, but it works a charm now. The caveat is I can only get the close of trade for a day.

In essence, I had to:

1. Get the days trades in ASCII format (typically share code, volume, open price etc.) provided as a daily email by the bank through which I trade.
2. Get a colleague to write an automation Add-In that reads the code I am interested in from from my XLS, matches it to the same code in the ASCII file, and write the required info (e.g. close price) into a cell address in the XLS.

If you are interested in seeing it in action, I can provide:

1. An Excel spreadsheet example
2. The Add-in
3. The ASCII data file
4. Some instructions for installing the Add-In - its not as easy as it sounds, but the trick is turning off UAC during Add-In installation.

This would be from the ASX example, but if you could get a similar format from other exchanges, it might just work for you.

Let me know and I'll write it up and I can send you the files,
Golden

 

[jimbo45]

Hi there
I have a program HQUOTE which loads FREE End of Day data from YAHOO (OPEN, CLOSE, HIGH,, LOW and VOLUME) in ASCII and "Metastock" format -- I use Metastock for looking at "trends", historical activity and other "Technical Analysis"

I'm basically interested with OPTIONS trading on US markets - especially "In the Money" type trades a few days before expiry of the option. On the US markets the option expires normally on the 3rd Friday of the month.

I know this is a Windows Forum but what I want to do is have a decent calculator for workng with OPTIONS near expiry date.

For example say the current price of MSFT is 27.00 USD a share I could write a "PUT" option "in the money" at a strike price at say 28 USD a share for expiry on 17 Dec for which I would get paid say 60 cents a share.

So per contract (100 shares) I would get paid a premium of 60 USD for 100 shares -- you keep that whatever happens.

If the share price goes does NOT reach 28 cents the shares are "Put to you" in otherwords you HAVE to buy them at 28 USD whatever the market price so technically you have to BUY 100 shares at 2800 USD.

If the price goes to say 29 USD then the option won't be exercised as nobody will be mad enough to sell you shares at 28 USD when the Market price is 29 USD or more --so you still KEEP the premium and you've made money without even selling the stock.

If the share DOES NOT reach 28 USD a share the option expires you still KEEP the premium --this is why you want fairly stable and probably slightly rising shares although you have to BUY the shares at the Contract price say 28 USD.

So I have 100 at 2700 which I sell for 2800, 100 USD profit, Lose the 100 USD profit because I have to buy 100 shares at 28 USD but KEEP the 60 USD premium for the option PUT. (You need to add dealing costs as well but if you pick a non volatile share you can easily make around 2.75 % a month -- remember here I'm talking about a WORSE CASE SCENARIO of 60 USD profit on 2700 USD in 3 DAYS !!! not the dismal 2.5% a YEAR a Bank pays out.

If the option is NOT exercised I've made 60 USD and can write another OPTION without selling any of the stock.


Same works in reverse with CALLS but its slightly easier since you will only "Get Called" if the shares reach the "Strike price" which will be higher (normally) that the price at which you bought them so you win BOTH ways.

Ideally for CALLS you want to be "Called" so you get Premium and Profit of share value and for PUTS you really want the share to rise a bit so you don't to get the shares PUT to you.

Rolling over for a few months with stable shares can earn a nice profit WHATEVER the market does.

So I need to get the option premium / strike price into EXCEL to calculate the profit and loss.

YAHOO for example gives Option prices --screenshot enc.


Cheers
jimbo

 

[Golden]

Whooaa. OK, thats a bit more than what I first envisaged. Nice to see you using Metastock....damn good software.

 

[jimbo45]

Whooaa. OK, thats a bit more than what I first envisaged. Nice to see you using Metastock....damn good software.

Hi again -- OK this isn't a traders Forum but METASTOCK is brilliant --am using V11.

Technical analysis from this piece of software is the BEST I've ever used and for options trading it really helps you get rid of the rubbish.

Enc screenshot and Good Trading for 2011

Cheers
jimbo

 

[ignatzatsonic]

Jimbo:

Go to data/get external data/existing connections in Excel.

Do you see something like this?

If you do, you can set that up to query MSN Money Central for quotes and have them land wherever you want in an Excel sheet.

You can then use a custom macro to copy any of those values to anywhere else in the Excel file.

I use this daily in Excel 2007.

But maybe you are already wise to this or otherwise have the situation under control?