BVC:LSE

[R7914T]

I am getting a strange result from =GETREALTIME("BVC:LSE",22,"ft") It results in 30 being the bid price. which is wrong.

Last price =GETREALTIME("bvc:lse",21,"ft") is 95.6

The problem is that the bid price should be an error code, if it is not available, so I can choose the market price in the absence of a valid bid price. (In working out current profits to date, I prefer to work with bid prices rather than mkt prices. )

The reason why the bid price is wrong is presumably connected with there being no bid price quote for it on the FT site, at the time of the pull.

The asking price #25 is also wrong at 137

I wonder where it is getting these strange prices from on the FT page.

Batm Advanced Communications Ltd BVC:LSE Actions TechnologyTechnology Hardware & Equipment

Price (GBX)95.60Today's Change0.600 / 0.63%Shares traded232.85k1 Year change-28.12%Beta-0.1598

[cmallwitz]

Looking at https://markets.ft.com/data/equities/tearsheet/summary?s=BVC:LSE it really has bid as 30 right now - unexpected but not much I can do about :-(

Note: ask is off the chart at 137 as well - but obviously no-one traded at that price

I actually have access to a private (company) LSE ticker feed - last trade was 16:35:26 @ 95.6 but the last time ask ticked/changed (to 137) was @16:36:47 and the bid ticked/changed a little longer (to 30) @16:38:07 - before the exchange fully closed - or rather the (after hours) closing auction ended. It is those bid/asks you see on the FT web site.

This is something that I would expect to happen with less liquid and/or small cap stock especially outside trading hours. If you really care about this, you need to have some validation logic using bid/ask, implied spread + last price and then decide if the bid is really what you want with a fallback to last price

I have seen too even intra-day this with some little traded index ETFs - e.g. they could have only 10 reported trades for the whole day but bid/ask is ticking a few times a second and moving away from last traded price quite substantially. Not as much as in this case but noticeable.

If Yahoo https://finance.yahoo.com/quote/BVC.L?p=BVC.L&.tsrc=fin-srch doesn't show the same it just means they have a different data feed or do some filtering on their own.

[R7914T]

What makes the situation difficult is that most of the shares are showing the correct spread, it is just a few that are very unrealistic. I note that those shares do not show the bid and ask prices, and so somehow the app is looking at unrealistic book prices. It is interpreting a bid as a true purchase price and an Ask as true sell price, when both could be offers that were not taken up.

I would much prefer to have an error message rather than a price which is not realistic. I can then use the market price for opening as well as closing in that instance. Is this something you could incorporate into the function?

[cmallwitz]

But as far as the extension is concerned the bid/ask of 30/137 is the correct last bid/ask value - even from the exchanges perspective.

What logic should the extension use when deciding to supress a bid/ask displayed on web site? Just a wide spread? How wide? What if just one side of bid/ask is moving away from last traded price (or last mid)? What if in the morning after exchange open bid/ask are moving away from last price (from previous day) when there are some market news or corporate actions (e.g. on ex-div days)? The bid/ask would actually reflect the current price - even if there was no trade yet.

I really think if you are using bid/ask (instead of last traded price) you would have do some validation on your side if you don't want to use certain bid/ask values - the decision has to be yours. Technically this is something that can happen to any company stock...

[R7914T]

I have very little doubt that what you have done with the outliers is consistent, but you are defending the inclusion of bid and asking prices that failed. Could you please suggest the steps I need to take to automate the checking of the bid asking prices so as to exclude these outliers. As the web pages of the prices for these inactive shares do not quote the bid and asking prices I thought it was easy for you to exclude them as outliers, but it is very hard for me and others to do it, without your skill and expertise.

[cmallwitz]

While I agree the bid/ask of 30/137 were outliers compared to last traded price, the FT web site did display them just like any other bid/ask (just forwarding the information it received from exchange itself) - at least when checked it on Saturday. Although being outliers they did not represent any failure - they were valid and correct at the time you saw them.

If the extension doesn't find or receive a particular data point, I try to return "N/A" whenever possible.

If you think about it from the perspective of someone who has a live ticker data feed from the exchange with bid/ask and bid/ask sizes being updated a couple of times (or hundreds of times) a second, when this is processed e.g. to make automatic trading decisions the code has to decide if a bid/ask change triggers any action. This would include any validation e.g if the spread suddenly jumps (increasing or decreasing). The exchange feeds will contain loads of noise e.g. sometimes the bid/ask is reversed - i.e. bid < ask. Usually things revert to "normal" fairly quickly but not after the exchange is closed as was the case here.

So if the exchange quotes bid/ask as 30/137 here what is the extension to do other than forwarding this to LibreCalc?

I guess you would have to get bid/ask and last price, calculate the spread between bid/ask, and only use bid if spread is within a % of last price and switch to last price otherwise. Or check mid price (calculated from bid/ask) against last price and check how far off it is. But these checks would have to be fine tuned or calibrated taking historic spread for a stock into account.

I'm no sure how the extension could make that decision.

[R7914T]

What address are you using to obtain that outlier bid and asking prices? I did not see them on the BATM page over the weekend.

Your suggestion to calculate the bid and ask price based on the market price is in practice impossible because there is no figure in your system for spread other than your bid and ask prices converted into a spread. At the time the prices shown by your function were non traded outlier prices and so to use them is to cause very strange results.

Let us say that the spreadsheet had a calc indicating the spread was too high, what should be used in its place when there is no function for spread? Where does the information come from when the only information is the excessive spread produced by the outliers? In other words in order to correct the spread I should not use the outliers to produce the correct result!

That is why I suggested that if the the ask and bid prices are not shown on the main page for the share then it would be better to give an error message that could then be used to apply the market price for that special situation.

In my case the BATM outliers produced a spread calculation of 350%, but there is such a variation in spreads that I can not take a standard one for all shares.

[cmallwitz]

This is the page used to pull data from FT for BVC https://markets.ft.com/data/equities/tearsheet/summary?s=BVC:LSE - towards the bottom under "Key Statistics". When I checked on Saturday, it displayed bid/ask(offer) of 30/137 there.

if you want to use bid/ask instead of last price in your spreadsheet, you need to decide when to use it and when not to. For that decision you would have to get at least three data points: bid/ask/last price

spread is just (ask-bid) but as I pointed out, sometimes the exchanges will publish bid/asks where bid > ask and spread could become negative (for a short period of time)

and yes, you are correct, the spread is very much a variable for a specific stock and exchange but in your case you could just say: if abs(spread)/last price > 5% -> use last price instead of bid in order to filter out any unrealistic wide bid/ask you want to ignore. This obviously depends on what you are trying to achieve when you use bid prices in first place.

[R7914T]

Thank you for your comments. Unfortunately, I can see that there is not a lot you can do about the outliers. I like to use bid prices to value my holdings, because they give a more accurate indication of what I will get if I sell. What I am considering is daily copying over the spread percentage column to another location, then checking the subsequent price updates with the copied percentages. If the new prices are materially in excess I would use the copied rate instead.

[cmallwitz]

That would be one way of doing it. Maybe you could establish an average spread for each stock and use that to discount your valuation of your holdings when using last price. Either way it will be just a estimate because bid/ask move all the time - same as actual last price traded when there are any.

[R7914T]

Am I right to assume that there is no way for the spreadsheet to know if the last price is the bid or asking price?

[cmallwitz]

Too much to type so I post a link here: https://www.investopedia.com/trading/basics-of-the-bid-ask-spread/

Basically, once a bid&ask price is matched, that price becomes the last price and bid/ask immediately start to diverge - until they get matched again and a "new" last price is established.

18 Jul 2021 17:29:28 R7914T @.***>:

Am I right to assume that there is no way for the spreadsheet to know if the last price is the bid or asking price?

— You are receiving this because you commented. Reply to this email directly, view it on GitHub[https://github.com/cmallwitz/Financials-Extension/issues/31#issuecomment-882082672], or unsubscribe[https://github.com/notifications/unsubscribe-auth/AA226T7X63NM2IHX6LFKF53TYL6ONANCNFSM5AERWJ2Q]. [data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAEgAAABICAYAAABV7bNHAAAAAXNSR0IArs4c6QAAAARzQklUCAgICHwIZIgAAAArSURBVHic7cEBDQAAAMKg909tDjegAAAAAAAAAAAAAAAAAAAAAAAAAAA+DFFIAAEctgHwAAAAAElFTkSuQmCC###24x24:true###][Tracking image][https://github.com/notifications/beacon/AA226T5U7KJZGVSK7RWYR6LTYL6ONA5CNFSM5AERWJ22YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOGSJYG4A.gif]

[R7914T]

Thank you for explaining it. I can now refine my SS