'getdata' Excel add-in (Yahoo API manual fix process)

[Itsallaguess]

The process below can be used to manually fix the 'getdata' Excel add-in, so that it will work with the v6 Yahoo API, and not the usual v7 API, which is currently not returning any live prices -

1. Open folder location of tlfaddin.xla file

2. Open Excel

3. Drag the tlfaddin.xla file into Excel

4. Open the Developer tab in Excel, and click the 'Visual Basic' icon on the left hand side of the Developer ribbon

5. In the Visual Basic window, in the left-hand panel, find the tlfaddin.xla entry and select the cross icon to the left to open the sub-folders

6. One of the sub-folders is called 'Modules' - click on the cross icon to the left of that line to open the sub-modules

7. Double-click the 'sPrices' module

8. The right-hand side VBA code-window should now show a section of VBA code headed 'Sub UpdatePrices()'

9. Change the URLheader string, underneath the 'Sub UpdatePrices()' line to read v6 instead of the current v7, leaving the rest of the URL string as it is

10. Select the 'SAVE' (disc) icon on the upper Visual Basic window icon bar

11. Close the Visual Basic and Excel windows

Your usual spreadsheet that uses the above add-in should now work again with the expected Yahoo price scrape.

Feedback on the above process would be welcome, and I'm happy to help if anything is unclear.

Cheers,

Itsallaguess

[peterh]

The process below can be used to manually fix the 'getdata' Excel add-in, so that it will work with the v6 Yahoo API, and not the usual v7 API, which is currently not returning any live prices -

1. Open folder location of tlfaddin.xla file

2. Open Excel

3. Drag the tlfaddin.xla file into Excel

4. Open the Developer tab in Excel, and click the 'Visual Basic' icon on the left hand side of the Developer ribbon

5. In the Visual Basic window, in the left-hand panel, find the tlfaddin.xla entry and select the cross icon to the left to open the sub-folders

6. One of the sub-folders is called 'Modules' - click on the cross icon to the left of that line to open the sub-modules

7. Double-click the 'sPrices' module

8. The right-hand side VBA code-window should now show a section of VBA code headed 'Sub UpdatePrices()'

9. Change the URLheader string, underneath the 'Sub UpdatePrices()' line to read v6 instead of the current v7, leaving the rest of the URL string as it is

10. Select the 'SAVE' (disc) icon on the upper Visual Basic window icon bar

11. Close the Visual Basic and Excel windows

Your usual spreadsheet that uses the above add-in should now work again with the expected Yahoo price scrape.

Feedback on the above process would be welcome, and I'm happy to help if anything is unclear.

Cheers,

Itsallaguess

Thanks very much for this! I'd tried to have a go myself and changed v7 to v10 (a stab in the dark), but that didn't work (obviously!)

My update method was slightly different to yours:

1. Open the excel file that uses the tlfaddin (actually, you could just start Excel with a new workbook - the addin will be opened at the same time)
2. Open the VBA window with the Alt+F11 key combination

Then I followed your steps from 5 onwards but, after step 9, I did 'debug>compile' from the menu ribbon and then your steps 10 and 11.

Edit: you'll need the password for the addin at step 5, but that can be found in a post from eventide (sp?) in one of the threads here.

[spiderbill]

The process below can be used to manually fix the 'getdata' Excel add-in, so that it will work with the v6 Yahoo API, and not the usual v7 API, which is currently not returning any live prices -
(Snip details)
Feedback on the above process would be welcome, and I'm happy to help if anything is unclear.

Thanks for that. Have now got it working but had to do a bit of digging. I'm on an old version of Excel - 2007 - and the visuals of opening the file look a bit different. Most importantly the Save step seems to be superfluous and can cause confusion if, as in my case, the file is read-only. After Googling I discovered the IsAddin parameter and changed it to False and then just closed the file without hitting Save. (It still asked me if I wanted to save and I said no) As I've noticed in the past with Macros that still saves the changes. Reopened and checked and it still retained the change to v6 so all is well.

Microsoft loves to confuse people!

cheers
Spiderbill

[funduffer]

OK I had a go at this using IAAG's instructions using comments from the other two posts:

I can get tlfaddin to work changing /v7/ to /v6/ with peterh's debug->compile step added.

But I can't save the result as it says tlfaddin is read only. I suppose could save it with a different name (tlfaddinv6, for example) somewhere else, and then add it in as a new addin - will this work? Presumably I would remove tlfaddin as an addin and would add tlfaddinv6 instead?

After the debug->compile step, getdata works (returns share prices) until I close Excel and re-open it, when it presumably it picks up the original (/v7/) version and stops working.

So I know the fix works, but I can't make it permanent.

(I also tried to make spiderbill's IsAddin parameter change, but I can't seem to get access to this property without a password - and it is not 'pleaseletmein'!)

Any advice gratefully received

FD

[peterh]

Any advice gratefully received

FD

One idea - can you find the .xla file on your computer? Mine is in c:\users\<user name>\AppData\Roaming\Microsoft\AddIns. When you find it, right click on it and then click on 'properties' at the bottom of the pop-up menu. See if the 'read-only' attribute box is ticked or not and untick it if it is.

Also, after you run the 'debug' step, are you clicking the 'save' icon in the VBA window before closing that window and returning to excel? As I said in my earlier post, the save worked for me using the steps outlined.

And, I don't think that I am breaking any confidences, because it has been posted in this sub forum several times, but the password for my version of the addin is 'ipisnotfree'.

[funduffer]

Anyway, thanks for everyone's help.

I couldn't work out how to save the modified tlfaddin - when saving it just says it is read-only and you have to save it as something else. Trying to change the IsAddin parameter, as spiderbill suggested, couldn't be done because it needs a password and the one peterh suggested is not it!

In the end I saved the modified VBA as 'tlfaddinv6' on a personal directory.

I then excluded tlfaddin from Excel and put tlfaddinv6 in instead as an addin.

It works!

It will do for now, until some more permanent solution comes along.....

FD

[GN100]

Absolutely Brilliant itsallaguess and thanks a million. My spreadsheet prices updating again now.

GN

[midgesgalore]

Thank you Itsallaguess for instructions to update the tlfaddin.xla macro.
Also a nod to perterh

I had time to sit down and apply the code fix, including the compile step. All is good.

Until now I had a manual workaround in my summary excel spreadsheet where I added a couple of columns to manually import my stock prices from a Hargreaves Lansdown watchlist (in excel format) and resolve a price update. I needed to adjust the same stocks listing alphabetical order to match my spreadsheet to the watchlist (one for single stocks and one for Investment Trusts).

The reason for adding a manual workaround was to provide a means of backwards compatibility to the getdata() call once it was working again.

I added one column "G" next to my problematic "F" column ::tick_err:: resulting from getdata() call then cut and paste the prices from my downloaded watchlists into the added "G" column.

To forward the price information to the rest of my spreasheet I added a final "H" column to provide either the workaround prices to my spreadsheet or normal getdata() functionality once a fix had been identified.

The entry in each active row of the final "H" column is:
=IF((F5="::tick_err::"), G5, F5)

where F5 is the result of the getdata() call, G5 is the pasted prices from the watchlists. Therefore the result of this "IF" statement gives either the resolved getdata() call or the manually inserted share price.


So it was quick and easy for me and kept me going during the hiatus due to the ::tick_err:: syndrome.
Thanks and much appreciation to eventide, Itsallaguess, peterh and mc2fool for providing the tlfaddin.xla and patches.

midgesgalore