Getting FTSE100 and other indices

[Nocton]

I've started using the Getprice1 macro from stepone_portfolio-v3-1.xls to get prices into my portfolio spreadsheet. Is there a code to get the FTSE100 and other indexes at the same time as share prices? The Yahoo web site suggests ^FTSE, but that does not work. Currently I use a Web Query, but it would be simplest to use the same system for prices and indexes.

[kiloran]

The prices are obtained from Yahoo, and stock symbols for the LSE have a .L suffix. This suffix is automatically appended to the stock symbol in Column A when the macro retrieves prices.

However, Yahoo do not use the suffix for indices, so when you enter the index ^FTSE in Column A, the macro looks for ^FTSE.L, which does not exist.

The only solution is to modify the macro.

Using the Visual Basic editor, look for the following code in the macro getPrices1:

Code: Select all

URL_Middle = epic + ".L"

and replace this with:

Code: Select all

        If Left(epic, 1) = "^" Then
            URL_Middle = epic
        Else:
            URL_Middle = epic + ".L"
        End If

There is then another issue..... Yahoo uses a currency of GBP for indices, so the index value is multiplied by 100 to get it in GBp.

To fix this, look for the code:

Code: Select all

If curr = "GBP" And IsNumeric(Price) Then Price = Price * 100

and change it to

Code: Select all

If Not Left(epic, 1) = "^" And curr = "GBP" And IsNumeric(Price) Then Price = Price * 100

I haven't tested this thoroughly for other indices, but it works OK for ^FTSE

--kiloran

[Nocton]

Perfect, kiloran. It works with other indices too.
Thank you for your prompt reply and useful code.

[kiloran]

It strikes me that the changes you need would not affect any existing users, so I've made a note to update v3.1 and incorporate this code ( or maybe something a little smarter) at some time in the future

--kiloran

[Nocton]

You could also think of adding something to indicate when an EPIC code is not found. At present the value returned is the same as for the previous 'get'. I've added:

Code: Select all

If InStr(Data, "[]") <> 0 Then
    Price = -1
Else

before

Code: Select all

Str = Split(Data, "currency:")(1)

and that does the trick. But there may be a more sophisticated error trapping option.