Getting FTSE100 and other indices
-
- Lemon Slice
- Posts: 435
- Joined: November 6th, 2016, 11:25 am
Getting FTSE100 and other indices
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.
-
- Lemon Quarter
- Posts: 3865
- Joined: November 4th, 2016, 9:24 am
Re: Getting FTSE100 and other indices
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:
and replace this with:
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:
and change it to
I haven't tested this thoroughly for other indices, but it works OK for ^FTSE
--kiloran
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"
Code: Select all
If Left(epic, 1) = "^" Then
URL_Middle = epic
Else:
URL_Middle = epic + ".L"
End If
To fix this, look for the code:
Code: Select all
If curr = "GBP" And IsNumeric(Price) Then Price = Price * 100
Code: Select all
If Not Left(epic, 1) = "^" And curr = "GBP" And IsNumeric(Price) Then Price = Price * 100
--kiloran
-
- Lemon Slice
- Posts: 435
- Joined: November 6th, 2016, 11:25 am
Re: Getting FTSE100 and other indices
Perfect, kiloran. It works with other indices too.
Thank you for your prompt reply and useful code.
Thank you for your prompt reply and useful code.
-
- Lemon Quarter
- Posts: 3865
- Joined: November 4th, 2016, 9:24 am
Re: Getting FTSE100 and other indices
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
--kiloran
-
- Lemon Slice
- Posts: 435
- Joined: November 6th, 2016, 11:25 am
Re: Getting FTSE100 and other indices
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:
before
and that does the trick. But there may be a more sophisticated error trapping option.
Code: Select all
If InStr(Data, "[]") <> 0 Then
Price = -1
Else
Code: Select all
Str = Split(Data, "currency:")(1)