Update to Simple Yahoo Price Scrape

[kiloran]

The original Simple Yahoo Price Scrape was released in Excel by Itsallaguess, and used truncated Yahoo symbols to be consistent with HYPTUSS, so the stock symbol for Yahoo, for example, was VOD; the macro automatically added .L to get the price from Yahoo. This worked fine, but did not work for non-UK shares, or indices such as ^FTSE since the macro converted this to ^FTSE.L
I subsequently released a LibreOffice version which used the native Yahoo stock symbol so it could be used for any stock, fund, index or currency provided by Yahoo.

Following a recent discussion with

SSNW1610

we've updated the Excel and LibreOffice versions to be consistent and allow the use of anything on Yahoo. This is done by added a Parameters sheet, with a parameter Automatically add ".L" suffix to stock symbols? which has the default set to Yes for the Excel version and No for the LibreOffice version. The default can be changed by the user. If the value is set to No, the data also displays the currency. For Yes, the data is in GBp, as previously

This should make both versions more useful and versatile. They can be used as a simple price source for customised spreadsheets, using a VLOOKUP to extract the data.

The new versions are at: http://lemonfoolfinancialsoftware.weebl ... crape.html

--kiloran

[PrefInvestor]

My daughter (truly an Excel wizard) told me off big time for the extensive use of VLOOKUP in my old investment spreadsheet when she took a look at it one day. With her assistance I totally reworked my investment spreadsheet using excel tables everywhere and of course replacing all VLOOKUPs with INDEX and MATCH avoiding the need for lists to be sorted and making it both faster and more compact too. As I’m sure you are aware use of VLOOKUP can on occasion result in the return of incorrect values in certain situations.

For anyone unaware of the differences see here https://trumpexcel.com/vlookup-vs-index ... bate-ends/

Well I must say I am a convert and I have never used VLOOKUP again since. INDEX and MATCH takes a bit of getting used to but it’s very simple really and works really well with tables and structured references (which allow column names to be used instead of the old An type references) helping to make your spreadsheet more readable and less error prone.

A pedantic point perhaps, but I certainly feel that it has resulted in a significant improvement in my spreadsheets. A must for anyone working with spreadsheets on a regular basis IMHO.

ATB

Pref

PS I have no idea whether this approach is compatible with HYPTUSS or whether use of LibreOffice affects the situation in any way. While I use Excel 365 exclusively myself i can appreciate that use of features that are present in both Excel and Libre Office is probably essential to avoid compatibility problems in your work on HYPTUSS.

[Itsallaguess]

Well I must say I am a convert and I have never used VLOOKUP again since.

INDEX and MATCH takes a bit of getting used to but it’s very simple really and works really well with tables and structured references (which allow column names to be used instead of the old An type references) helping to make your spreadsheet more readable and less error prone.

Thanks Pref - that's a very useful reminder that VLOOKUP has some drawbacks sometimes. In the majority of cases those drawbacks might well never be exposed, so VLOOKUP is still a very useful and simple to use function, especially for those of us who pick up our Excel knowledge on an ad-hoc basis and like to leave things alone if they 'already work'.

With that said, for anyone interested in looking into how INDEX and MATCH can be used instead, then here's a very good step-by-step tutorial that clearly explains how we can use these two very powerful functions together as an improved alternative -

This article explains in simple terms how to use INDEX and MATCH together to perform lookups. It takes a step-by-step approach, first explaining INDEX, then MATCH, then showing you how to combine the two functions together to create a dynamic two-way lookup.

https://exceljet.net/index-and-match

Cheers,

Itsallaguess

[EssDeeAitch]

For anyone unaware of the differences see here https://trumpexcel.com/vlookup-vs-index ... bate-ends/

Well I must say I am a convert and I have never used VLOOKUP again since. INDEX and MATCH takes a bit of getting used to but it’s very simple really and works really well with tables and structured references (which allow column names to be used instead of the old An type references) helping to make your spreadsheet more readable and less error prone.

Very true, Index and Match is far superior to Vlookup. Another useful feature being that it can "look/find" right to left as well as left to right.

[kiloran]

My daughter (truly an Excel wizard) told me off big time for the extensive use of VLOOKUP in my old investment spreadsheet when she took a look at it one day. With her assistance I totally reworked my investment spreadsheet using excel tables everywhere and of course replacing all VLOOKUPs with INDEX and MATCH avoiding the need for lists to be sorted and making it both faster and more compact too. As I’m sure you are aware use of VLOOKUP can on occasion result in the return of incorrect values in certain situations.

For anyone unaware of the differences see here https://trumpexcel.com/vlookup-vs-index ... bate-ends/

Well I must say I am a convert and I have never used VLOOKUP again since. INDEX and MATCH takes a bit of getting used to but it’s very simple really and works really well with tables and structured references (which allow column names to be used instead of the old An type references) helping to make your spreadsheet more readable and less error prone.

A pedantic point perhaps, but I certainly feel that it has resulted in a significant improvement in my spreadsheets. A must for anyone working with spreadsheets on a regular basis IMHO.

ATB

Pref

PS I have no idea whether this approach is compatible with HYPTUSS or whether use of LibreOffice affects the situation in any way. While I use Excel 365 exclusively myself i can appreciate that use of features that are present in both Excel and Libre Office is probably essential to avoid compatibility problems in your work on HYPTUSS.

Many thanks for your thought-provoking comments, Pref, though I think your comment "and I have never used VLOOKUP again since" is perhaps rather extreme. Horses for courses, and in around 30 years of using Excel professionally and personally, I would say that VLOOKUP works perfectly in over 99% of cases. The only times I've known it to return incorrect values is when a 'non-expert' user has left off the final parameter or set it to TRUE (there lies danger!!). Set the parameter to FALSE and there is no need for the list to be sorted.
INDEX and MATCH certainly make for a less readable formula, but that can be overcome to a very large extent by the use of data range names (which can also be used in VLOOKUP). One of these days, I'll actually practice what I preach and use data range names far more than I do.

Thanks for tossing this into the discussion

--kiloran
PS.... yes, your approach is used in parts of HYPTUSS, and works equally well with Excel and LibreOffice

[PrefInvestor]

INDEX and MATCH certainly make for a less readable formula, but that can be overcome to a very large extent by the use of data range names (which can also be used in VLOOKUP). One of these days, I'll actually practice what I preach and use data range names far more than I do.

Again forgive me for not being up to date with what you use in HYPTUSS, but I have found that the use of Excel tables and structured references has enormously enhanced the readability and hence the maintainability of my spreadsheet(s). All tables come with named ranges by default which are the same as the table name, and of course columns are simply referenced in the form [column name].

So my spreadsheet contains formulae that look like this for example:-

=IFERROR(INDEX(SWPrices,MATCH([@Epic],SWPrices[Epic],0),Price),"")

=IFERROR(INDEX(Commission,MATCH([Account],Commission[Account],0),2),11.95)

I have very few formulae of the old R1C1 variety I am pleased to say !.

Wrt to VLOOKUP the problems mainly arise through the incorrect use that you have described, or with trying to do a lookup against an unsorted table of course. But such problems also arise in formulae that you think are working and fully tested through previous use, but are somehow changed as a result of other modifications to the spreadsheet.

ATB

Pref

PS I really must get around to taking a look at HYPTUSS one day !.

[kiloran]

So my spreadsheet contains formulae that look like this for example:-

=IFERROR(INDEX(SWPrices,MATCH([@Epic],SWPrices[Epic],0),Price),"")

=IFERROR(INDEX(Commission,MATCH([Account],Commission[Account],0),2),11.95)

I have very few formulae of the old R1C1 variety I am pleased to say !.

Wrt to VLOOKUP the problems mainly arise through the incorrect use that you have described, or with trying to do a lookup against an unsorted table of course. But such problems also arise in formulae that you think are working and fully tested through previous use, but are somehow changed as a result of other modifications to the spreadsheet.

ATB

Pref

Oh, I'm jealous! If only I could get to your level of organisation, the formulas look so much neater and readable
Perhaps I'm living proof of old dogs and new tricks.

But I'm still confused by your comment about lookups against an unsorted table. Just set the final VLOOKUP parameter to FALSE, and the lookup table can be in any order, sorted or unsorted (unless you are doing something other than an exact match, of course). I imagine an unsorted table might be a bit less efficient, but that would not matter in most cases unless the lookup table was very large indeed.

--kiloran

[PrefInvestor]

But I'm still confused by your comment about lookups against an unsorted table. Just set the final VLOOKUP parameter to FALSE......

Well you covered the problem in your first reply really, failing to set the last argument to FALSE to force an exact match is likely the most common error, and for relational type lookups that’s what you need to do.

But if you really DO want the closest match then you need to have sorted the table or what you get back will likely not be the closest match.

Unfortunately both types of error may be totally invisible to the user unless they do some rigorous testing…..as you know.

ATB

Pref

[kiloran]

But I'm still confused by your comment about lookups against an unsorted table. Just set the final VLOOKUP parameter to FALSE......

Well you covered the problem in your first reply really, failing to set the last argument to FALSE to force an exact match is likely the most common error, and for relational type lookups that’s what you need to do.

But if you really DO want the closest match then you need to have sorted the table or what you get back will likely not be the closest match.

Unfortunately both types of error may be totally invisible to the user unless they do some rigorous testing…..as you know.

ATB

Pref

Thanks Pref.

I guess I've never had a situation where I wanted the closest match, I've always wanted an exact match or #N/A so table sorting has never been an issue.

--kiloran

[GPhelan]

Hi Kiloran,
I think in your comments you have identified the key difference between Pref's daughter's concerns and your application.
IF an Excel workbook is put 'out there' for general users to use and fiddle with - as we all do, then techniques such as INDEX/MATCH may help them to avoid falling into the problems Pref has described.

HOWEVER when a Workbook is created for a fixed purpose and distributed on that basis to a mostly well controlled user population, then the internal workings are less important. Speed of development and ease of occasional support become paramount, rather than insuring the structure against user sabotage. Like you I have NEVER EVER wanted an match that was anything other than EXACT, though I have pondered the need for a 'but I found more than one match' Excel event!

I have a track record in making these comments. Some years ago I was involved in an IT project upgrading my Company TO Windows 7 and unexpectedly ended up distributing an Access + Excel application worldwide within the company. The application was a 'throwaway' tool used to gather data for planning the upgrade. 'Unexpectedly' meant I had no budget for developing robust systems and I just had to distribute the internal systems I had developed for UK use, running mostly within my direct control. The end users were IT project staff - more likely than most to peek 'under the covers', but whilst the Access system generated some justified queries, the Excel side, filled with VLOOKUPs just worked.

[PrefInvestor]

Hi GPhelan, For users just using HYPTUSS as supplied on this site then the issues that I have described are not going to be a problem. itsallaguess and kiloran are clearly experienced spreadsheet developers and their efforts will I’m sure ensure that it performs as expected.

However the original post in this topic mentioned users making their own custom extensions to HYPTUSS and specifically mentioned using VLOOKUP in that context. Hopefully it is clear that the methods I have described (as promoted by my daughter !) are really aimed at anyone doing spreadsheet development in their own right. For anyone doing such development I feel that an knowledge (and hopefully an understanding) of the things that I have described will help them avoid problems in making any additions to HYPTUSS that they develop for themselves or in any other spreadsheet work that they might undertake.

If nothing else I hope it will have brought to peoples attention the criticality of NOT just omitting the optional 4th argument to VLOOKUP and always inserting FALSE in this field where you want an exact match. Failing to do this will have quite unpredictable results in your spreadsheets that might not be readily apparent. Better still take the time to learn how to use INDEX and MATCH instead which is a far better and safer alternative.

Apologies once again if my comments are overly pedantic.

ATB

Pref