The Lemon Fool

Hi All,

Since the issues early last week getting prices from Yahoo using the TLF addin (written by eventide I think) I have been having strange problems with one of my portfolio spreadsheets. (I don't know if the timing is a coincidence)

Before last week, whenever I opened my spreadsheet, the prices would be populated. Right-clicking and selecting 'TLF Refresh' would update them. This was the same on 2 spreadsheets I run for 2 portfolios (call them A and B).

Since last week's issues, though, when I open spreadsheet A, all the prices say ':queued:', and running 'TLF Refresh' has no effect. I have discovered a workaround is to go into each of the cells, press F2 and Enter (so effectively editing the cell although making no changes), and then run TLF Refresh and the prices will update. If I press 'F2' on some of the cells, but not others, then the prices will only update in the cells which have been edited - the rest remain saying ":queued:".

Spreadsheet B, for a different portfolio, still works the original way - prices update first time with no need to edit the cells. I have compared the code in each spreadsheet and they are visually identical.

After last weeks issues, I did update a line of code in both spreadsheets, per eventide's instructions. It was to change an error check from checking .StatusText, to .Status. I made the same change in both my spreadsheets.

I tried creating a new spreadsheet, copying my data into it, and importing the code from spreadsheet A, and it suffers exactly the same issue. So I create a second new spreadsheet, and this time copied the code in from spreadsheet B, and it still has the same issue. It seems that even starting afresh with a new spreadsheet is not working.

I'm curious whether anyone else uses the TLF add-in and 'getdata' function, and if they have the same problem - it would be good to know what shows in the spreadsheet cells when they first open the spreadsheet - the last value, or 'queued'. And does anyone have any suggestions on how to resolve this?

Thanks,
StepOne

Hi StepOne

My instances of Getdata all show as ::queued:: until they are refreshed, so I do not recognise your problem.

However, I have been having lots of problems since the latest Windows 10 update. I can not easily select a group of cells with the mouse. Clicking on a cell and starting to drag to a selection sometimes goes straight into edit mode, which is what I now discover F2 does, but it makes it impossible to select a group of cells.

Sorry this does not help with your problem, but I'm wondering if the update has affected you too.

Regards InStep

You appear to have 3 instances of this question running on the different boards at the moment? The main board for the addin is/was:

viewtopic.php?f=27&t=8332&start=83

I cannot replicate the symptom you describe. It may be a manual vs automatic calculation issue of your own making. It may be that the code has been modified incorrectly per the recent change. Hard to know without being able to replicate the issue.

Either way, for my own use I've deprecated the addin in favour of a python based websockets solution from another datasource so I won't really be able to support it much and it is now removed from the d/l site. The code has had a good 10+ year run but it's not particularly efficient and is aways at risk of the api vanishing. However the crowd who maintain the other vba projects on here may be able to help more now the vba is open, or steer you to their projects which are still maintained.

What I'm basically saying is sorry I can't be of much help

eventide wrote:What I'm basically saying is sorry I can't be of much help

No problem eventide, it's not too much of an issue, just a slightly annoying workaround. Thanks for getting back to me,

Cheers,
StepOne

StepOne wrote:

eventide wrote:What I'm basically saying is sorry I can't be of much help

No problem eventide, it's not too much of an issue, just a slightly annoying workaround. Thanks for getting back to me,

Cheers,
StepOne

Have you tried pressing F9, which will force the workbook to recalculate? If this works, then possibly the workbook was changed to manual calculation from automatic?

Hi,

Just to say, if anyone has a similar issue, that I discovered that the spreadsheet with the issue is a .xlsm file (i.e. a macro enabled workbook for the newest versions of Excel), whereas the spreadsheet which worked okay is a .xls file - i.e. the old excel format for versions 97-2003.

When I convert the old spreadsheet to a new version, it suffers from the same problem. So, in the new version of Excel I still have the issue where I have to go into every cell with the 'getdata' function individually and press F2/Enter, otherwise the 'TLF Refresh' does nothing. It would be good to know whether other people need to do this.

And it still doesn't really explain why both spreadsheets worked perfectly up until a couple of weeks ago.

Cheers,
StepOne