Calculate the Value of the FTSE

[Mackie]

Can someone please tell me how to calculate te value of the FTSE100.

or.. Is there anywhere I can get a spreadsheet that can do the calculation for me. I should be able to make a DDE link to the spreadsheet with the share prices.

What I am trying to do is establish to what extent a move in the FSTE 100 is affected by;

Stocks Going Ex-Dividend
The movement of individual sectors E.g. The Miners Up, The Oil Co's down
The movement of more heavily weighted stocks. E.g. BP, VOD, HSBA

This way it can be a bit easier to detect broad reversals, false rallies, sell-offs, etc.

Many thanks
VB Rgds

 

[Rognvald]

You may find some of what you want here if you look hard enough,have the time to spare, understand the mathematics and can program Excel well enough.

Good luck - I am awed at the task you are proposing to set yourself. Do you really think it is worthwhile?

http://www.ftse.com/indices_marketdata/ground_rules/uk-series-ground-rules.pdf

Otherwise you may like to consider a look at Sharescope EODor RT where a lot of this work is done for you and can probably be manipulated in Excel (can't quite remember - its a long time since I used it). If your precise requirement is not automaticaly calculated in sharescope there is a facility for carrying out many of these filtering type exercises quite easily.

You can probably do enough of what you want in the free trial period to get a result and if you want to carry on - well - its up to you

As you refer to connecting by dde It seems that you realise you will need a datafeed. I see, after a search of your posts, that you seem to use Esignal. The Sharescope combination of data and software is probably the best and cheapest (if not the only) commercial option available to enable relatively easy calculation of what I think you have in mind

 

[Mackie]

Hi Rognvald, Many thanks
I am hoping somebody will have a spreadsheet already!

 

[Rognvald]

Sorry to sound callous, but dream on

 

[Mackie]

I already have a live feed for UK Equities and the FTSE with Updata which works extremely well.
I can create a DDE link from the Updata package.!

Have faith! It's Sunday!

 

[Captain Haddock]

Hi Mackie I have done this already with a DDE link to an excel spreadsheet. Unfortunately you need the index divsor number which FTSE.com won't release unless you are a member. However you can calculate it yourself at the end of each day and use it for the following day.

I used for a while on the binarys as it gives you instant FTSE cash figure as opposed to every 12(?) seconds.

I will try and dig out the formula for you

EDIT: Have to add that I used Sharescope as my data feed for the DDE and I just remembered a problem I had with it. Sharescope reported all trade types including O and AT, these cannot be filtered out so If you had an O trade printed thats what the spreadsheet would use to calculate the FTSE cash price. And as you are probably aware the share prices are only changed by AT trades, so It would sometimes give a slightly innaccurate figure. Depends how accurate you want it I guess.

 

[jklondon]

capitain haddcock - how did it work with binaries - cant imagine it presented a signficant edge since with the right input its relativley easy to calculate the fair value of the ftse no?

 

[Captain Haddock]

capitain haddcock - how did it work with binaries - cant imagine it presented a signficant edge since with the right input its relativley easy to calculate the fair value of the ftse no?

JK

As it gave an instant cash figure, it was helpful just as the hourly binarys were about to settle that were close to the mark (lunchtime ftse normally)

I used it on betfair mostly where you are trading against other traders prices as opposed to a brokers price. The edge was having a more up to date ftse cash figure than most others trading on the exchange.

 

[Captain Haddock]

Hi Mackie I have done this already with a DDE link to an excel spreadsheet. Unfortunately you need the index divsor number which FTSE.com won't release unless you are a member. However you can calculate it yourself at the end of each day and use it for the following day.

I used for a while on the binarys as it gives you instant FTSE cash figure as opposed to every 12(?) seconds.

I will try and dig out the formula for you

EDIT: Have to add that I used Sharescope as my data feed for the DDE and I just remembered a problem I had with it. Sharescope reported all trade types including O and AT, these cannot be filtered out so If you had an O trade printed thats what the spreadsheet would use to calculate the FTSE cash price. And as you are probably aware the share prices are only changed by AT trades, so It would sometimes give a slightly innaccurate figure. Depends how accurate you want it I guess.

FTSE cash is calculated by the sum of the market caps divided by the divisor

Market cap is obviously the number of shares in issue multiplied by the current share price. (can be calculated using your DDE link to current price)

The divisor you will need to work out yourself from the night before unless you become a member at ftse.com.

Do this by:

Use the closing prices and the shares in issue to get the FTSE closing market cap.

Divide that by the FTSE close price and you've got your divisor for the next day.


Current cash price = Current market cap / Index Divisor

Round it off to 2 decimal places

Hope that makes some sense. Its not going to be 100% accurate but I found it good to 2 dec places 90% of the time.

It is however interesting to see how much the large caps do move the FTSE and its interesting to see how just a switch in price from bid to offer on the big 4 can move the cash

 

[Rognvald]

Well, if you really want to spend your time on all this - fine!

Then there is the question Mackie raised about the xd stocks

Then the sectoral calculations.

Then of course the FTSE constituents and weightings change periodically in accordance with the rules set by FTSE.

Then you find your machine doesn't have enough memory or processing power

Then you find that you have made a mistake in the spreadsheet,

Then your dde link doesn't function quickly enough and misses prices.

I would not consider spending my time with all of this, but hey - chacun a son gout.

 

[Mackie]

Captain Haddock,

Many Many thanks
I have downloaded the FTSE data from

http://www.ftse.com/objects/csv_to_table.jsp?infoCode=100a&theseColumns=MiwxLDQsMTQsMTIsOa=

I will add a DDE link and try and do some fiddling with it this coming weekend.


Rognvald
Often the FTSE moves by XX points. It is good if you know what has caused it. Or if one knows of news that may affect say BP, the FTSE will also be affected.

Many thanks for you input.

Kind Regards

Rob

 

[SOCRATES]

Well, if you really want to spend your time on all this - fine!

Then there is the question Mackie raised about the xd stocks

Then the sectoral calculations.

Then of course the FTSE constituents and weightings change periodically in accordance with the rules set by FTSE.

Then you find your machine doesn't have enough memory or processing power

Then you find that you have made a mistake in the spreadsheet,

Then your dde link doesn't function quickly enough and misses prices.

I would not consider spending my time with all of this, but hey - chacun a son gout.

And you have forgotten to mentiopn the surprise antics of the Index Committee, who insert and remove constituents periodically and all hell is played with the weightings as they also have to be adjusted to take account of changes.

 

[Captain Haddock]

You are welcome Mackie, good luck with it.

 

[Captain Haddock]

I think some of you may be missing the point concerning weightings.

The larger the market cap, the greater the impact on the ftse due to it making up a larger chunk of the cumulative market cap. Hence they are automatically adjusted when using Shares in issue x price formula

There are no mysterious weighting adjustments..........

 

[SOCRATES]

I think some of you may be missing the point concerning weightings.

The larger the market cap, the greater the impact on the ftse due to it making up a larger chunk of the cumulative market cap. Hence they are automatically adjusted when using Shares in issue x price formula

There are no mysterious weighting adjustments..........

No ? Go on then ~ give us an explanation in detail, because I can tell you that no end of approaches to the exchanges elicited a sensible workable formulation at how it is that the value of the index is arrived at on a proper, structured actuarial basis and how adjustments are made to account for insertions and extractions, and how the multiplier is arrived at, all of this with any credible degree of accuracy that can be verified. This goes for all exchanges by the way.

Go on, enlighten us then, but please do it properly if you are going to do it at all, no throwaway statements this time please, however intended, Captain Haddock.

Your detailed response is awaited with interest.

 

[Captain Haddock]

Sorry Socrates, whilst I am more than happy to help someone if I can, you won't find me spending my time composing a lengthy and detailed post purely to enter into an arguement.

I prefer to put my efforts to better use.

I have however taken the time to fish out this link from my favourites for you to browse entitled; "Ground Rules for the Management of The UK
Series of the FTSE Actuaries Share Indices"............should you feel so inclined.

http://www.ftse.com/indices_marketdata/uk_series/uk-series-ground-rules.pdf

 

[SOCRATES]

Thank you for your reply Captain Haddock. It is exactly the reply that I expected, and I am not disappointed but to the contrary. This is a riddle several of us have been strugglilng with for years, and finally have more or less given up and just accept the indexs' existences as sort of rough yardsticks.

You see, if you take the prices of all the stocks contained in the index at a precise point in time together with the value of the index simultaneously, you would argue that you have a set of figures on which to base a calculation taking into account the weightings would you not ?

OK, well you follow this route and you arrive at a conclusion that there is a coefficent or multiplier underpinning the calculation.

But, say, a little while later you repeat the excercise.

The results this time are altogether different, bearing in mind that nothing has been added or subtracted in terms of underlying shares, and there has been no alteration to the weightings either....

Therefore one can only arrive at the conclusion that the multiplier itself has to be manipulated to force fit given situations for reasons best known to the providers of such yardsticks.

So you see it is not so easy and I can understand your reluctance to step in where angels fear to tread. Very wise.

And with reference to my post 15 above, kindly subsitute "elicited" for "failed to elicit".
Thank you.

Any further comments ? Anyone ?

 

[Rognvald]

"I have however taken the time to fish out this link from my favourites for you to browse entitled; "Ground Rules for the Management of The UK
Series of the FTSE Actuaries Share Indices"............should you feel so inclined."

Captain Haddock
I have no wish to enter into an argument and I am sure Socrates hasn't either. What you have "fished out", is the link I provided in the first instance in post #2 in response to Mackie's question.

If you refer to the detail in this (and it seems from your most recent post (#16) that you are not prepared to do so), you will find that the position is exactly as Socrates and I have described it. There is no easy way to achieve what Mackie originally stated he wished to do without a very complex spreadsheet demanding frequent, constant and detailed adjustment. Even if there were I believe that the value of what would be obtained is questionable

If it was easy and if the results were so useful, every one and his auntie and aunties' dog would be using it to take money from the market. It seems though, that for some reason, they are not. Construe - as my Latin master used to say

 

[Captain Haddock]

"I have however taken the time to fish out this link from my favourites for you to browse entitled; "Ground Rules for the Management of The UK
Series of the FTSE Actuaries Share Indices"............should you feel so inclined."

Captain Haddock
I have no wish to enter into an argument and I am sure Socrates hasn't either. What you have "fished out", is the link I provided in the first instance in post #2 in response to Mackie's question.

Ah, yes indeed it is I do apologise.

 

[Captain Haddock]

Socrates and Rognvald

Why overcomplicate matters?

Sometimes a bit of thinking outside the box and a belief that just maybe all those that tell you it's impossible are wrong can yield some great results for your needs.

I had a variety of similar responses to yours when I initially attempted this.

Fortuntely I do not listen to people telling me can't be done and went ahead and did exactly what I set out to do. Which was create a spreadsheet that gives me an instant value of the ftse....... It wasn't complicated and yes it had its limitations as I mentioned in an earlier post.........but you know what.........it worked and hopefully Mackie's will too.

So you see, I can't help but sit here and smile when I see comments like this;

"There is no easy way to achieve what Mackie originally stated he wished to do without a very complex spreadsheet demanding frequent, constant and detailed adjustment"

One would assume you've attempted to create such a spreadsheet with such a firm statement but we all know you have not........................

Rognvald"I would not consider spending my time with all of this."

Perhaps one should spend a bit less time quoting useless latin phrases???