Donate to Remove ads

Got a credit card? use our Credit Card & Finance Calculators

Thanks to johnstevens77,Bhoddhisatva,scotia,Anonymous,Cornytiv34, for Donating to support the site

Share Prices in Excel - NEW update

Discussions regarding financial software
eventide
2 Lemon pips
Posts: 102
Joined: October 24th, 2017, 3:29 pm
Has thanked: 3 times
Been thanked: 83 times

Re: Share Prices in Excel - NEW update

#373724

Postby eventide » January 6th, 2021, 9:56 am

I have not used the Add-In "tlfAddin.xla" - or do you mean tfsAddin.xla - that you mention, but could the stock data function's disappearance be due to its function being replaced by the recently released function STOCKHISTORY: https://sfmagazine.com/post-entry/augus ... nd%20bonds.


No he doesn't mean that at all. It is a UDF not a native function.



For quite a few years I have been using the "tlfAddin.xla" piece of magic and its predecessor to update prices in my Excel spreadsheets, but just recently whenever I try to use it every line brings up the error message:

::tick_err::

Can anybody advise me as to what might be causing this and if there is anything I can so to rectify it? I am using 'Microsoft Office Home and Student 2019' on Windows 10.



Have you followed the steps on page 4 of this thread to modify the code?

scrumpyjack
Lemon Quarter
Posts: 4812
Joined: November 4th, 2016, 10:15 am
Has thanked: 605 times
Been thanked: 2675 times

Re: Share Prices in Excel - NEW update

#373748

Postby scrumpyjack » January 6th, 2021, 10:41 am

eventide wrote:
I have not used the Add-In "tlfAddin.xla" - or do you mean tfsAddin.xla - that you mention, but could the stock data function's disappearance be due to its function being replaced by the recently released function STOCKHISTORY: https://sfmagazine.com/post-entry/augus ... nd%20bonds.


No he doesn't mean that at all. It is a UDF not a native function.



For quite a few years I have been using the "tlfAddin.xla" piece of magic and its predecessor to update prices in my Excel spreadsheets, but just recently whenever I try to use it every line brings up the error message:

::tick_err::

Can anybody advise me as to what might be causing this and if there is anything I can so to rectify it? I am using 'Microsoft Office Home and Student 2019' on Windows 10.



Have you followed the steps on page 4 of this thread to modify the code?


I did modify the code and it then usually worked, but not always for some reason. So I now use the STOCKS function on Excel365 which is fine.
STOCKHISTORY has not in fact been generally released. It is still only available to some Insider Excel users, I think on a random basis. I registered for the Insider programme but still did not get access to the STOCKHISTORY function. It would be useful to use it to lookup share prices at any selected date in the past, but in the end I got round that by downloading the last 5 years daily share prices of all the shares I have invested in via Yahoo Finance and loading those into my investment database- bit of a chore but it's done now!

eventide
2 Lemon pips
Posts: 102
Joined: October 24th, 2017, 3:29 pm
Has thanked: 3 times
Been thanked: 83 times

Re: Share Prices in Excel - NEW update

#373786

Postby eventide » January 6th, 2021, 11:50 am

Heres a sheet which might help with historic downloads (ohlc)


viewtopic.php?f=27&t=23368&p=309874#p307625

Gostevie
2 Lemon pips
Posts: 222
Joined: November 4th, 2016, 11:35 am
Has thanked: 838 times
Been thanked: 158 times

Re: Share Prices in Excel - NEW update

#373793

Postby Gostevie » January 6th, 2021, 11:57 am

eventide wrote:
I have not used the Add-In "tlfAddin.xla" - or do you mean tfsAddin.xla - that you mention, but could the stock data function's disappearance be due to its function being replaced by the recently released function STOCKHISTORY: https://sfmagazine.com/post-entry/augus ... nd%20bonds.


No he doesn't mean that at all. It is a UDF not a native function.



For quite a few years I have been using the "tlfAddin.xla" piece of magic and its predecessor to update prices in my Excel spreadsheets, but just recently whenever I try to use it every line brings up the error message:

::tick_err::

Can anybody advise me as to what might be causing this and if there is anything I can so to rectify it? I am using 'Microsoft Office Home and Student 2019' on Windows 10.



Have you followed the steps on page 4 of this thread to modify the code?


Thank you. I will do that.

Gostevie

scrumpyjack
Lemon Quarter
Posts: 4812
Joined: November 4th, 2016, 10:15 am
Has thanked: 605 times
Been thanked: 2675 times

Re: Share Prices in Excel - NEW update

#373805

Postby scrumpyjack » January 6th, 2021, 12:20 pm

eventide wrote:Heres a sheet which might help with historic downloads (ohlc)


viewtopic.php?f=27&t=23368&p=309874#p307625


Thanks that is very helpful

funduffer
Lemon Quarter
Posts: 1327
Joined: November 4th, 2016, 12:11 pm
Has thanked: 122 times
Been thanked: 831 times

Re: Share Prices in Excel - NEW update

#403090

Postby funduffer » April 10th, 2021, 8:12 am

An old thread, but a new problem for me:

I have purchased a new laptop, with Office 365, transferring my excel files to the new device.

I have a few key spreadsheets that use the tlfAddin to download stock prices.

It works fine on my old PC (Office 2013), but not on the new laptop (Office 365). I have copied the add-in from the old PC to the laptop and activated it in the new Excel 365. When I right click the cell the "Refresh prices once" appears on the menu, but the cells still show #VALUE!

Should I be downloading a more recent version of the add-in specific to Office 365? If so, where can I download?

Is the add-in being blocked in some way?

HYPTUSS works fine, but I am not sure it uses tlfAddin!

I suspect it is something simple I am missing. Any help much appreciated.

FD

genou
Lemon Quarter
Posts: 1070
Joined: November 4th, 2016, 1:12 pm
Has thanked: 177 times
Been thanked: 370 times

Re: Share Prices in Excel - NEW update

#403238

Postby genou » April 10th, 2021, 5:28 pm

funduffer wrote:An old thread, but a new problem for me:

I have purchased a new laptop, with Office 365, transferring my excel files to the new device.

I have a few key spreadsheets that use the tlfAddin to download stock prices.


FD

Not an answer to your question, but O365 has a stocks data type, which could replace tlfAddin for you, if you can't get a workaround.

funduffer
Lemon Quarter
Posts: 1327
Joined: November 4th, 2016, 12:11 pm
Has thanked: 122 times
Been thanked: 831 times

Re: Share Prices in Excel - NEW update

#403270

Postby funduffer » April 10th, 2021, 7:00 pm

genou wrote:
funduffer wrote:An old thread, but a new problem for me:

I have purchased a new laptop, with Office 365, transferring my excel files to the new device.

I have a few key spreadsheets that use the tlfAddin to download stock prices.


FD

Not an answer to your question, but O365 has a stocks data type, which could replace tlfAddin for you, if you can't get a workaround.


Thanks, yes, I have just discovered it, and it works just fine, so I may stick with O365 STOCKS.

JamesMuenchen
Lemon Slice
Posts: 668
Joined: November 4th, 2016, 9:05 pm
Has thanked: 141 times
Been thanked: 167 times

Re: Share Prices in Excel - NEW update

#403364

Postby JamesMuenchen » April 11th, 2021, 10:36 am

Hi

I saw this one posted on TMF USA
https://themeasureofaplan.com/investmen ... o-tracker/

I haven't tested it at all, and am still happily using the TLFAddin but it may be of interest.

eventide
2 Lemon pips
Posts: 102
Joined: October 24th, 2017, 3:29 pm
Has thanked: 3 times
Been thanked: 83 times

Re: Share Prices in Excel - NEW update

#403393

Postby eventide » April 11th, 2021, 12:50 pm

funduffer wrote:An old thread, but a new problem for me:

I have purchased a new laptop, with Office 365, transferring my excel files to the new device.

I have a few key spreadsheets that use the tlfAddin to download stock prices.

It works fine on my old PC (Office 2013), but not on the new laptop (Office 365). I have copied the add-in from the old PC to the laptop and activated it in the new Excel 365. When I right click the cell the "Refresh prices once" appears on the menu, but the cells still show #VALUE!

Should I be downloading a more recent version of the add-in specific to Office 365? If so, where can I download?

Is the add-in being blocked in some way?

HYPTUSS works fine, but I am not sure it uses tlfAddin!

I suspect it is something simple I am missing. Any help much appreciated.

FD



Possibly a VBA reference missing in the new instance of Excel. Check what references are ticked in the old pc (Alt-F11 > Tools > References), and check same ones in the new instance of Excel.

Also check that macros enabled, VBA enabled etc in the new Excel instance. If you are seeing the right click menu, but nothing works on clicking a menu item, this means the addin is loaded properyl but nothing fires.

funduffer
Lemon Quarter
Posts: 1327
Joined: November 4th, 2016, 12:11 pm
Has thanked: 122 times
Been thanked: 831 times

Re: Share Prices in Excel - NEW update

#403534

Postby funduffer » April 12th, 2021, 9:40 am

eventide wrote:
funduffer wrote:An old thread, but a new problem for me:

I have purchased a new laptop, with Office 365, transferring my excel files to the new device.

I have a few key spreadsheets that use the tlfAddin to download stock prices.

It works fine on my old PC (Office 2013), but not on the new laptop (Office 365). I have copied the add-in from the old PC to the laptop and activated it in the new Excel 365. When I right click the cell the "Refresh prices once" appears on the menu, but the cells still show #VALUE!

Should I be downloading a more recent version of the add-in specific to Office 365? If so, where can I download?

Is the add-in being blocked in some way?

HYPTUSS works fine, but I am not sure it uses tlfAddin!

I suspect it is something simple I am missing. Any help much appreciated.

FD



Possibly a VBA reference missing in the new instance of Excel. Check what references are ticked in the old pc (Alt-F11 > Tools > References), and check same ones in the new instance of Excel.

Also check that macros enabled, VBA enabled etc in the new Excel instance. If you are seeing the right click menu, but nothing works on clicking a menu item, this means the addin is loaded properyl but nothing fires.


Hi eventide, thanks for your reply.

I checked the VBA references, and they are the same on both PC's. Macros are enabled.

I opened a blank workbook, and typed: =getdata("VOD.L") into a cell.

It comes up with ::tick.err:: in the Office 365 version, and the correct share price on the old PC (Office 2013).

I read the advice you provided on p4 of this thread, and wondered if it is still relevant:

ALT-F11 to open VBA editor:

CTRL-F to open search/replace box

Search for the following string in the whole project:
If .StatusText <> "OK"


Replace it with the following string:
If .Status <> 200


I can open the VBA editor, but I don't know how to search for the StatusText string!
Just pressing CTRL-F does nothing.
How do I do this search?

Any advice much appreciated.

FD

peterh
2 Lemon pips
Posts: 128
Joined: November 4th, 2016, 12:08 pm
Has thanked: 18 times
Been thanked: 35 times

Re: Share Prices in Excel - NEW update

#403580

Postby peterh » April 12th, 2021, 11:12 am

funduffer wrote:
I can open the VBA editor, but I don't know how to search for the StatusText string!
Just pressing CTRL-F does nothing.
How do I do this search?

Any advice much appreciated.

FD

Find tflAddin in the Project Explorer in the VBA editor.

Click the boxes with the plus signs in them to show the contents and open the list of modules in tflAddin.

Open the 'fUtilities' module in the tflAddin add in by double clicking it (or click it once and press F7).

The code should show in the big window. There isn't a lot of code, so it should be easy to spot the line to change.

I was going to show a screenshot, but I can't post it!

funduffer
Lemon Quarter
Posts: 1327
Joined: November 4th, 2016, 12:11 pm
Has thanked: 122 times
Been thanked: 831 times

Re: Share Prices in Excel - NEW update

#403655

Postby funduffer » April 12th, 2021, 3:28 pm

peterh wrote:
funduffer wrote:
I can open the VBA editor, but I don't know how to search for the StatusText string!
Just pressing CTRL-F does nothing.
How do I do this search?

Any advice much appreciated.

FD

Find tflAddin in the Project Explorer in the VBA editor.

Click the boxes with the plus signs in them to show the contents and open the list of modules in tflAddin.

Open the 'fUtilities' module in the tflAddin add in by double clicking it (or click it once and press F7).

The code should show in the big window. There isn't a lot of code, so it should be easy to spot the line to change.

I was going to show a screenshot, but I can't post it!


Thankyou peterh.

I have found the relevant piece of code in fUtilities, made the change to the StatusText string..............but it won't let me save it!

After making the change, I went to the 'File' menu and chose 'Save tlfaddin..xla'.

The error message says I cannot access the relevant folder, for several possible reasons:

The file name or path does not exist (the path quoted is correct)
The file is being used by another program (I only have a blank workbook open in Excel)
The workbook you are trying to save has the same name as a currently open workbook.(not sure what this means?)

Maybe I am being thick, but I feel I am so close to fixing this!

FD

ReformedCharacter
Lemon Quarter
Posts: 3120
Joined: November 4th, 2016, 11:12 am
Has thanked: 3590 times
Been thanked: 1509 times

Re: Share Prices in Excel - NEW update

#403667

Postby ReformedCharacter » April 12th, 2021, 4:35 pm

funduffer wrote:
Thankyou peterh.

I have found the relevant piece of code in fUtilities, made the change to the StatusText string..............but it won't let me save it!

After making the change, I went to the 'File' menu and chose 'Save tlfaddin..xla'.

The error message says I cannot access the relevant folder, for several possible reasons:

The file name or path does not exist (the path quoted is correct)
The file is being used by another program (I only have a blank workbook open in Excel)
The workbook you are trying to save has the same name as a currently open workbook.(not sure what this means?)

Maybe I am being thick, but I feel I am so close to fixing this!

FD

I think you just need to save the workbook, not the addin.

RC

peterh
2 Lemon pips
Posts: 128
Joined: November 4th, 2016, 12:08 pm
Has thanked: 18 times
Been thanked: 35 times

Re: Share Prices in Excel - NEW update

#403668

Postby peterh » April 12th, 2021, 4:46 pm

funduffer wrote:
Thankyou peterh.

I have found the relevant piece of code in fUtilities, made the change to the StatusText string..............but it won't let me save it!

After making the change, I went to the 'File' menu and chose 'Save tlfaddin..xla'.

The error message says I cannot access the relevant folder, for several possible reasons:

The file name or path does not exist (the path quoted is correct)
The file is being used by another program (I only have a blank workbook open in Excel)
The workbook you are trying to save has the same name as a currently open workbook.(not sure what this means?)

Maybe I am being thick, but I feel I am so close to fixing this!

FD

When I go through the same steps, I can save tflAddin.xla successfully, so I'm not sure what is happening for you?

How have you got tflAddin.xla open? Have you opened it as a file in its own right, or is it installed in Excel as an add in? You could also try rebooting your PC and starting from scratch in case something is lingering in the background!

funduffer
Lemon Quarter
Posts: 1327
Joined: November 4th, 2016, 12:11 pm
Has thanked: 122 times
Been thanked: 831 times

Re: Share Prices in Excel - NEW update

#403679

Postby funduffer » April 12th, 2021, 5:30 pm

ReformedCharacter wrote:I think you just need to save the workbook, not the addin.

RC


Thank you, thank you - that did the trick!

I am back up and running - thanks to RC, peterh and eventide.

Believe it or not, I used to be a programmer, many years ago - FORTRAN!

Never got involved with VBA or macros in Excel, except to use other people's work like this.

Maybe I should learn it!

FD

innocuous
Posts: 27
Joined: September 19th, 2020, 6:34 pm
Has thanked: 8 times
Been thanked: 3 times

Re: Share Prices in Excel - NEW update

#419585

Postby innocuous » June 15th, 2021, 1:42 am

Hi guys,

I had the tlfaddin running pretty well for the last 4 months. Suddenly and for no apparent reason I am getting errors all over the place. I have already tried deleting and adding a new copy of the addin to excel. I have also replaced the:

Search for the following string in the whole project:
If .StatusText <> "OK"


Replace it with the following string:
If .Status <> 200


For standard tickers e.g. "AAPL" I am getting a #NAME? error, and then for other VOD.L I am getting ::tick_err:: errors. Can anyone help me work-out what is going on?

Lastly - I have also tried doing this in a fresh new sheet...
B2 = AAPL
C2 = '=getdate(B2,bid)'
OUTCOME = '::tick_err::'

B3 = 'VOD.L'
C3 = '=getdata(B3,1)'
Outcome = '::tick_err::'

Any help you can provide would be really helpful....Thanks

innocuous
Posts: 27
Joined: September 19th, 2020, 6:34 pm
Has thanked: 8 times
Been thanked: 3 times

Re: Share Prices in Excel - NEW update

#419646

Postby innocuous » June 15th, 2021, 11:46 am

Just a thought....are there any VBA references that need to be installed for this to work?

Itsallaguess
Lemon Half
Posts: 9129
Joined: November 4th, 2016, 1:16 pm
Has thanked: 4140 times
Been thanked: 10023 times

Re: Share Prices in Excel - NEW update

#419662

Postby Itsallaguess » June 15th, 2021, 12:36 pm

innocuous wrote:
I had the tlfaddin running pretty well for the last 4 months.

Suddenly and for no apparent reason I am getting errors all over the place.

I have already tried deleting and adding a new copy of the addin to excel.

Any help you can provide would be really helpful....Thanks


I've just downloaded a fresh copy of the XLA add-in from here -

http://lemonfoolfinancialsoftware.weebly.com/share-prices-in-excel.html

I've opened a new Excel workbook (Windows 10 / Office 2007), with the following VBA references set -

  • Visual Basic For Applications
  • Microsoft Excel 12.0 Object Library
  • OLE Automation
  • Microsoft Office 12.0 Object Library

I've then activated the downloaded add-in via Excel Options / Add-ins / Manage Excel Add-ins, and made sure that the .xla file is added to the add-in references and activated.

I've then entered 'VOD.L' into Cell E7, and in Cell G7, I've typed the formula '=getdata(E7)', and pressed return.

At first, Cell G7 contained the text '::queued::', but when I right-clicked the cell, I got the option to 'Refresh prices once', and when I selected that option, Cell G7 then refreshed to display the correct Vodafone price of 131.28p

Cheers,

Itsallaguess

innocuous
Posts: 27
Joined: September 19th, 2020, 6:34 pm
Has thanked: 8 times
Been thanked: 3 times

Re: Share Prices in Excel - NEW update

#419682

Postby innocuous » June 15th, 2021, 1:38 pm

Itsallaguess wrote:
innocuous wrote:
I had the tlfaddin running pretty well for the last 4 months.

Suddenly and for no apparent reason I am getting errors all over the place.

I have already tried deleting and adding a new copy of the addin to excel.

Any help you can provide would be really helpful....Thanks


I've just downloaded a fresh copy of the XLA add-in from here -

http://lemonfoolfinancialsoftware.weebly.com/share-prices-in-excel.html

I've opened a new Excel workbook (Windows 10 / Office 2007), with the following VBA references set -

  • Visual Basic For Applications
  • Microsoft Excel 12.0 Object Library
  • OLE Automation
  • Microsoft Office 12.0 Object Library

I've then activated the downloaded add-in via Excel Options / Add-ins / Manage Excel Add-ins, and made sure that the .xla file is added to the add-in references and activated.

I've then entered 'VOD.L' into Cell E7, and in Cell G7, I've typed the formula '=getdata(E7)', and pressed return.

At first, Cell G7 contained the text '::queued::', but when I right-clicked the cell, I got the option to 'Refresh prices once', and when I selected that option, Cell G7 then refreshed to display the correct Vodafone price of 131.28p

Cheers,

Itsallaguess


Thanks for checking - I am going to try it again totally fresh. I will also try on another PC and see if that works or not. Back shortly with the results. :-)


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 5 guests