Share Prices in Excel - Fast and Easy

[eventide]

If you used the original LivePrice.xls excel spreadsheet from TMF back in the day, it will not work as of 2 November. If you still want to retrieve share prices in excel, there is a solution, at least for now. Although the HYPTUS guys are incorporating some variant of this into their project, I suspect there are a number of users out there who have their own spreadsheet and layouts, and just want to continue to have a very simple function to retrieve the data of interest.

The excel addin at the following link provides exactly that functionality:

- It adds a new function to excel =getdata("ticker", "field") which is basically the same as the old function in the liveprice spreadsheet
- It adds a command to the mouse's right click menu which will update prices

If you open the addin, you won't see anything except a new button on a mouse right click (which will disappear when the addin is closed when excel is closed). You can use the getdata function anywhere on your existing spreadsheets if the addin is running, which is an improvement on the old system.

If this works for you, it would make sense to recommend this post, not for my vanity but (i) to give comfort to other potential users and (ii) visibility on the popular posts list.

The link is: https://www.dropbox.com/s/11z5wycj77rr0 ... n.xla?dl=0

The code is open for inspection. It is saved as a 1997-2003 .xla file - although I use Office 365 this may have maximum compatibility with older versions of excel.

Example usage

=getdata("^FTSE", "last") in cell A1 will update the latest index value every refresh.

it will return ::queued:: while you have not refreshed the data
it will return ::tick_Err:: if the ticker is badly formed
it will return ::no_data:: if the ticker is valid but there is no field data available

Notes on Field Selection (case insensitive) in the function
1, "nm", "name": RETURNS SECURITY NAME
2, "b", "bid": RETURNS BID PRICE
3, "a", "ask", "offer": RETURNS ASK PRICE
4, "l", "last": RETURNS LAST PRICE
5, "c", "chg", "change": RETURNS CHANGE
6, "prvcls", "close", "previousclose": RETURNS PREVIOUS CLOSE
7, "daylow", "dl": RETURNS LOW OF DAY
8, "dayhigh", "dh": RETURNS HIGH OF DAY
9, "52wk_low", "52wl": RETURNS 52 WEEK CLOSING LOW
10, "52wk_high", "52wh" RETURNS 52 WEEK CLOSING HIGH
11, "delay", "exchangedelay": RETURNS EXCHANGE DELAY IN MINUTES
12, "ccy", "currency": RETURNS CCY OF QUOTES (GBP, GBp, USD etc)
13, "type", "quotetype": RETURNS SECURITY TYPE (STOCK, CURRENCY, INDEX, FUTURE ETC)

Anything else (or omitted): defaults to last price

[kiloran]

That's great, eventide. I haven't had time to look at it yet, and my time will be very limited for the next few days, but I'll add it to our Weebly Financial Software website when I get a chance if that is OK with you so that we have a permanent home for it.

--kiloran

[eventide]

Please don't for now, thanks. I am sure it will need tweaking.

[JMN2]

This is sorcery! Now you are just showing off!

[kiloran]

Please don't for now, thanks. I am sure it will need tweaking.

No problem, eventide. I've got far more important priorities for now so it'll be a few days at least before I can even look at it (can't wait!!)

I'll contact you before I consider publishing

--kiloran

[eventide]

This is sorcery! Now you are just showing off!

haha thank you. I considered making the code literally go fetch the price as you enter the function for the first time so you never saw "queued", but it would lead to a large drain on the server and slowed response if you opened a sheet with a lot of tickers. It would have appeared a lot more like sorcery though...

[Scruple]

Hello eventide,

This version works fine for me. I am using Vista and Excel 2010.

I've been using the previous version for a long time and it's fantastically useful. Thanks very much.

I would like to make a couple of requests, could you:

1. make the ".L" post fix a separate parameter or default if omitted?

2. add a market capitalization field selection (not sure if that is available)?

Regards

Nigel

[simoan]

Firstly, I'd like to thank eventide for this most excellent piece of work! I previously used the Yahoo CSV API to download data using the WEBSERVICE function built-in to Excel 2013 and it worked brilliantly, until last week. So using the getdata() function is a great replacement.

I have tried the add-in on both Excel 2010 and 2013 on Win10. The 2013 version is running on an Asus tablet. I have a few things to report:

1. On the 2013 tablet, the "TLF refresh" does not appear in the menu when you do the touch equivalent of a mouse right click.

2. On both versions, when I save, close and re-open the spreadsheet I get the full directory path to getdata() shown in each cell it is called and the cell itself shows a #NAME? error. I realise this is probably something I am doing wrong as I have not used Excel add-ins before.

Any advice on where the add-in (.XLA file) should be located and what you need to do to re-enable it each time you open the spreadsheet would be greatly appreciated because I feel sure I am doing something wrong.

All the best, Si

[eventide]

Hello eventide,

I would like to make a couple of requests, could you:

1. make the ".L" post fix a separate parameter or default if omitted?

2. add a market capitalization field selection (not sure if that is available)?

Regards

Nigel

1. No
2. Done

The new fields are

14 or "mktcap", "mc", "marketcap"
15 or "divyld", "dy", "dividendyield"
16 or "eps"

these are not always available from the pricing source in which case you will see ::no_data::

[eventide]

Firstly, I'd like to thank eventide for this most excellent piece of work! I previously used the Yahoo CSV API to download data using the WEBSERVICE function built-in to Excel 2013 and it worked brilliantly, until last week. So using the getdata() function is a great replacement.

I have tried the add-in on both Excel 2010 and 2013 on Win10. The 2013 version is running on an Asus tablet. I have a few things to report:

1. On the 2013 tablet, the "TLF refresh" does not appear in the menu when you do the touch equivalent of a mouse right click.

2. On both versions, when I save, close and re-open the spreadsheet I get the full directory path to getdata() shown in each cell it is called and the cell itself shows a #NAME? error. I realise this is probably something I am doing wrong as I have not used Excel add-ins before.

Any advice on where the add-in (.XLA file) should be located and what you need to do to re-enable it each time you open the spreadsheet would be greatly appreciated because I feel sure I am doing something wrong.

All the best, Si

1. No idea sorry. Possibly because I backwards saved the addin to excel 2003 from office365.
2. The add isn't open if you see that, or, if it is, you have moved it from where you originally referenced it. use "edit links" to point your own file to the correct location of the addin. Alternatively, you can tell excel to load the addin behind the scenes every time you open excel. ("addins>browse>double click on the addin)

[simoan]

1. No idea sorry. Possibly because I backwards saved the addin to excel 2003 from office365.
2. The add isn't open if you see that, or, if it is, you have moved it from where you originally referenced it. use "edit links" to point your own file to the correct location of the addin. Alternatively, you can tell excel to load the addin behind the scenes every time you open excel. ("addins>browse>double click on the addin)

Thanks, eventide. I've been doing some reading on how Excel add-ins work so understand more now. I think the problem was finger trouble on my part and the add-in wasn't open; when it is open I can confirm the "TLF refresh" option is present on the right-click menu on both Excel 2013 tablet and Excel 2010 desktop, both Win10 machines.

My spreadsheets are up and running with prices now using getdata(). Thanks very much for your impressive work!

All the best, Si

[eventide]

My spreadsheets are up and running with prices now using getdata(). Thanks very much for your impressive work!

All the best, Si

Thanks. I 'm pleased it works for you, and I hope the price server remains available. If there's any way you think I could make it more simple for the average excel user, please let me know as I think that is the most valuable feature of the utility.

[Instep]

My spreadsheets are up and running with prices now using getdata(). Thanks very much for your impressive work!

All the best, Si

Thanks. I 'm pleased it works for you, and I hope the price server remains available. If there's any way you think I could make it more simple for the average excel user, please let me know as I think that is the most valuable feature of the utility.

Hi eventide

I will echo what Si said above. I tried LivePriceTLF-BETA2 and it just seems like magic, but I can't get tlfAddin.xla to work at all.
I'm using Office 365, (all fully up-dated), but I can NOT see any change to the Right-Click menu whether the xla file is activated or not in the list of available add-ins.

I used to know my way around vba, so I ought to be more capable than "the average excel use" but I can't make it work.

Regards

Instep

[simoan]

Thanks. I 'm pleased it works for you, and I hope the price server remains available. If there's any way you think I could make it more simple for the average excel user, please let me know as I think that is the most valuable feature of the utility.

Yes, let's hope Yahoo don't take down the JSON price server too. I'd been using the CSV API for several years to update prices in Excel and so it was quite a loss to my day-to-day investment monitoring. The getdata() function is a great drop-in replacement for the WEBSERVICE function calls to the CSV API I was using in Excel 2013, so switching over was simple once I understood how the add-in process worked.

If you do ever feel the need to update the add-in, some might find it useful to have access to the trailing dividend amount (in pence) to calculate income. I had a look through some examples for the shares I hold and the data looked to be correct. I don't recall this information being available for UK stocks using the CSV API.

All the best, Si

[eventide]

I will echo what Si said above. I tried LivePriceTLF-BETA2 and it just seems like magic, but I can't get tlfAddin.xla to work at all.
I'm using Office 365, (all fully up-dated), but I can NOT see any change to the Right-Click menu whether the xla file is activated or not in the list of available add-ins.

I used to know my way around vba, so I ought to be more capable than "the average excel use" but I can't make it work.

Regards

Instep

Try the following:

1) Open the VBA editor (Alt-F11) and check the addin is there. If it isn't, you haven't opened it (or installed it so it opens every time)
2) If it is there, but the right click isn't working, close all sheets, close excel, and reopen excel, and check the right click. This should make certain the right click button creation process fires.

[eventide]

If you do ever feel the need to update the add-in, some might find it useful to have access to the trailing dividend amount (in pence) to calculate income. I had a look through some examples for the shares I hold and the data looked to be correct. I don't recall this information being available for UK stocks using the CSV API.

All the best, Si

Done. The full list of fields and the number or textstring you can use to get them is below. you will need to re-download the addin from the link. If the dividends you find do not accord with the truth, that is a fault at the server end not the addin.

Case 1, "n", "nm", "name", "longname", "shortname"
Case 2, "b", "bid"
Case 3, "a", "ask", "offer"
Case 4, "l", "last"
Case 5, "c", "chg", "change"
Case 6, "prv", "prvcls", "close", "previousclose"
Case 7, "daylow", "dl"
Case 8, "dayhigh", "dh"
Case 9, "52wk_low", "52wl"
Case 10, "52wk_high", "52wh"
Case 11, "delay", "sourceinterval"
Case 12, "ccy", "currency"
Case 13, "type", "quotetype"
Case 14, "mktcap", "mc", "marketCap"
Case 15, "divyld", "dy", "dividendyield"
Case 16, "d", "div", "ltmdivs"
Case 17, "eps"

[Instep]

Try the following:

1) Open the VBA editor (Alt-F11) and check the addin is there. If it isn't, you haven't opened it (or installed it so it opens every time)
2) If it is there, but the right click isn't working, close all sheets, close excel, and reopen excel, and check the right click. This should make certain the right click button creation process fires.

1) it's not there this time.

So I close Excel - Open it again with a blank sheet. Save the blank sheet as .xlsm (Macro enabled file)
Click File - Options - Add-ins

Tlfaddin is listed as in Active Application Add-ins.
Click Go.. to Manage Excel Add-ins and it shows as ticked in the list, so click OK.
Still NO joy.

[eventide]

Tlfaddin is listed as in Active Application Add-ins.
Click Go.. to Manage Excel Add-ins and it shows as ticked in the list, so click OK.
Still NO joy.

I was able to recreate your problem.

Your operating system may have blocked functionality on the original download as a security measure.
Using file explorer (not excel) browse to where you saved the addin, right click and investigate its properties. Check unblock if necessary.

If I could only edit the first post in this thread, I would add this information to it!

[Instep]

Tlfaddin is listed as in Active Application Add-ins.
Click Go.. to Manage Excel Add-ins and it shows as ticked in the list, so click OK.
Still NO joy.

I was able to recreate your problem.

Your operating system may have blocked functionality on the original download as a security measure.
Using file explorer (not excel) browse to where you saved the addin, right click and investigate its properties. Check unblock if necessary.

If I could only edit the first post in this thread, I would add this information to it!

Many Thanks - That should solve it! I'll test later.

[Instep]

I was able to recreate your problem.

Yes, I found the message about the operating system possibly blocking the add-in and unblocked it.
TLF Refresh and TLF Reset then appeared at the top of the Right Click Menu.

Your code works brilliantly. I just need to rethink the way my spreadsheets work to make best use of it.

Thanks again.