EXCEL's 'Get External Data'

Grumpy_Old_Duffer

I want to load the FTSE from http://finance.yahoo.com/q/bc?s=%5EFTSE&t=1d&l=on&z=l&q=l&c= into EXCEL, but I want this to be constantly updated.

a) How would I get the FTSE Index Value into EXCEL?
b) Does anyone know how I the Website can be constantly refreshed automatically, thereby updating the Index Value in EXCEL?

innovate

Import:
Data - Import External Data - New Web Query. Tick the yellow arrow above the information you want (so arrow turns into a green tick). Click IMPORT when you have selected all you want (you probably have to get more data than you want- but if you keep the imported data on a separate spreadsheet, you can refer to the cells of interest from other sheets)

Refresh:
Click on any cell of your imported data, then right-click, select Data Range Properties. Specify the refresh options you want.

That's it!

(you can also manually refresh by by right-clicking and "Refresh now")

Grumpy_Old_Duffer

innovate wrote:

Import:
Data - Import External Data - New Web Query. Tick the yellow arrow above the information you want (so arrow turns into a green tick). Click IMPORT when you have selected all you want (you probably have to get more data than you want- but if you keep the imported data on a separate spreadsheet, you can refer to the cells of interest from other sheets)

Refresh:
Click on any cell of your imported data, then right-click, select Data Range Properties. Specify the refresh options you want.

That's it!

(you can also manually refresh by by right-clicking and "Refresh now")

Don't have "Data - Import External Data - New Web Query", only "Data - Get External Data - New Web Query" ... if that makes any difference, and I can't see any yellow arrow!!!

I've used "Data - Get External Data - New Web Query", browsed to the website, selected 'Only the Tables' and formatting is set to 'None'; then OK.

The data is imported to EXCEL and, as you say, I can use 'Refresh' on the External Data toolbar. I can set this to automatically operate with a macro.

Do I need to do anything with 'Save Query ..' and/or 'Advanced..'?

BUT WHAT'S THIS YELLOW ARROW??????

Using EXCEL 2000.

tigermatt

It doesn't look like you need to "Save Query" - the connection info is stored in the workbook when you save it - you just have to refresh it when you open it again.

It doesn't matter that you have "Get External Data" not "Import External Data". They are both the same thing.

innovate

tigermat has already answered some of your questions. Sorry about different terminology - I have Excel 2003, don't know what MS changed between versions.

Ref the yellow arrows: they're actually black arrows with yellow background (at least in my version of Excel)

Even with Excel 2000, you should be able to specify automatic refresh, via dialogue window like this one

You should also be able to display a tool bar for your external data (View - Toolbars - External Data)

Grumpy_Old_Duffer

Thanks for this .... unfortunately I only have Excel 2000, version 2003 looks to do EXACTLY what I want, except for the refresh, which I want to be refreshed more often than 1 minute, which is the minimum EXCEL will allow ..... unless the Refresh could be invoked everytime data in a certain range (i.e. A1:E6) is changed.

albertross_2

Grumpy,

This isn't answering your question about Excel, so please ignore if irrelevant..

http://www.winstock.co.uk/ This software in combination with a winTV teletext card, can monitor and update portfolio's (in delayed realtime) if that is any use. Also, many financial (and share dealing) sites can monitor either a real or fictitous (paper) portfolio, for free, if that is what you are trying to achieve..

Chippy_Minton

Grumpy_Old_Duffer wrote:

Thanks for this .... unfortunately I only have Excel 2000, version 2003 looks to do EXACTLY what I want, except for the refresh, which I want to be refreshed more often than 1 minute, which is the minimum EXCEL will allow ..... unless the Refresh could be invoked everytime data in a certain range (i.e. A1:E6) is changed.

You can do this by using Excel's OnTime method or a Windows timer and http://www.cpearson.com/excel/ontime.htm shows how.

Press Alt+F11 to open the VB editor and paste the Excel timer code from that page into a module. In the The_Sub subroutine, put:

ThisWorkbook.RefreshAll

before the call to StartTimer. Test the refresh by running the StartTimer macro and stop it by running StopTimer. Once it's working correctly, call those subroutines in the Workbook_Open and Workbook_BeforeClose events respectively, as mentioned on that page.