Share Prices in Excel - Fast and Easy

[adamc]

Thanks for this solution, works perfectly. Is it possible to add more cases? I'd like to add 50-day and 200-day moving average data - had a quick play with adding cases in the vba, but couldn't get any field names that worked.

Also, to make it easier for people transitioning from the csv api, might be worth adding the same short codes, such as l1, c1, p, and so on:

Case 1, "n", "nm", "name", "longname", "shortname": ParseField = ",shortName:"
Case 2, "b", "bid": ParseField = "bid:"
Case 3, "a", "ask", "offer": ParseField = "ask:"
Case 4, "l1", "l", "last": ParseField = "regularMarketPrice:"
Case 5, "c1", "c", "chg", "change": ParseField = "regularMarketChange:"
Case 6, "p", "prv", "prvcls", "close", "previousclose": ParseField = "regularMarketPreviousClose:"
Case 7, "g", "daylow", "dl": ParseField = ",regularMarketDayLow:"
Case 8, "h", "dayhigh", "dh": ParseField = ",regularMarketDayHigh:"
Case 9, "j", "52wk_low", "52wl": ParseField = ",fiftyTwoWeekLow:"
Case 10, "k", "52wk_high", "52wh": ParseField = ",fiftyTwoWeekHigh:"
Case 11, "delay", "sourceinterval": ParseField = "exchangeDataDelayedBy:"
Case 12, "ccy", "currency": ParseField = "currency:"
Case 13, "type", "quotetype": ParseField = "quoteType:"
Case 14, "j1", "mktcap", "mc", "marketCap": ParseField = ",marketCap:"
Case 15, "y", "divyld", "dy", "dividendyield": ParseField = "trailingAnnualDividendYield:"
Case 16, "d", "div", "ltmdivs": ParseField = "trailingAnnualDividendRate:"
Case 17, "e", "eps": ParseField = "epsTrailingTwelveMonths:"

One further question - why do some of the field names have a preceding comma?

[adamc]

Never mind, solved my own issue - found the fields by following the link in a web browser: query1.finance.yahoo.com/v7/finance/quote?symbols=BARC.L

Case 1, "n", "nm", "name", "longname", "shortname": ParseField = ",shortName:"
Case 2, "b", "bid": ParseField = "bid:"
Case 3, "a", "ask", "offer": ParseField = "ask:"
Case 4, "l1", "l", "last": ParseField = "regularMarketPrice:"
Case 5, "c1", "c", "chg", "change": ParseField = "regularMarketChange:"
Case 6, "p", "prv", "prvcls", "close", "previousclose": ParseField = "regularMarketPreviousClose:"
Case 7, "g", "daylow", "dl": ParseField = ",regularMarketDayLow:"
Case 8, "h", "dayhigh", "dh": ParseField = ",regularMarketDayHigh:"
Case 9, "j", "52wk_low", "52wl": ParseField = ",fiftyTwoWeekLow:"
Case 10, "k", "52wk_high", "52wh": ParseField = ",fiftyTwoWeekHigh:"
Case 11, "delay", "sourceinterval": ParseField = "exchangeDataDelayedBy:"
Case 12, "ccy", "currency": ParseField = "currency:"
Case 13, "type", "quotetype": ParseField = "quoteType:"
Case 14, "j1", "mktcap", "mc", "marketCap": ParseField = ",marketCap:"
Case 15, "y", "divyld", "dy", "dividendyield": ParseField = "trailingAnnualDividendYield:"
Case 16, "d", "div", "ltmdivs": ParseField = "trailingAnnualDividendRate:"
Case 17, "e", "eps": ParseField = "epsTrailingTwelveMonths:"
Case 18, "m3": ParseField = "fiftyDayAverage:"
Case 19, "m4": ParseField = "twoHundredDayAverage:"
Case Else: ParseField = "regularMarketPrice:"

[eventide]

Thanks for this solution, works perfectly. Is it possible to add more cases? I'd like to add 50-day and 200-day moving average data - had a quick play with adding cases in the vba, but couldn't get any field names that worked.

Also, to make it easier for people transitioning from the csv api, might be worth adding the same short codes, such as l1, c1, p, and so on:

One further question - why do some of the field names have a preceding comma?

Added: l1 as alt for l
Added c1 as alt for c
Not adding g,h,j,k as they aren't usefully able to identify what the field is even if thats a valid shortcode
Added e as alt for eps
Added 50dma
Added 200dma

The preceding comma helps locate certain fields where some textstring is repeated in the overall json output. You can ignore it. I've made it consistent for all of them anyway.

Field selection is now as follows:

Case 1, "n", "nm", "name", "longname", "shortname"
Case 2, "b", "bid"
Case 3, "a", "ask", "offer"
Case 4, "l", "l1", "last"
Case 5, "c", "c1", "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", "exchangedelay"
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, "e", "eps"
Case 18, "50dma"
Case 19, "200dma"
Anything else: last

ANYONE WANTING TO USE THESE WILL HAVE TO RE-DOWNLOAD THE ADDIN

[GN100]

Instep wrote:-

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


The code certainly is great and the double mouse click to make the add on run automatically is a great little tip. Rethinking the way spreadsheets are laid out - fortunately my Excel workbook has page one as a list of all holdings with prices and amounts, etc. All other data in the sheets refers back to the first page and when the Yahoo feed stopped my whole workbook was a mess. As soon as the add on was running and the correct commands replaced the links to the old live price sheet everything fell in to place.
One point I have come across is that there are times when I need to use my laptop instead of my main desktop. To do this I simply copy my financial data folder across to the laptop and carry on working. I noticed that when I did this the new add on was difficult to run because it was trying to find the add on in the old location. I found that removing the document history and reopening the add on caused it to be remembered with the correct address.

Thanks again Eventide.

[Gostevie]

I just want to add my very sincere thanks to anc1/Eventide for all your efforts over the years.

The original LivePrice macro has been the basis for my own spreadsheets pretty much since I started investing, and the thought of losing its functionality really did cause me to lose sleep.

So thank you so much for coming up with the new fix - and so quickly too. I am so grateful, as I'm sure many others are too.

Gostevie

[jkretsch]

The add-in works great on my Office 2010. However, I have Office 2013 on my other computer (on Windows 10) and when I try to add the add-in it says it's not a valid add-in. Do you have a version compatible with Office 2013?

[eventide]

The add-in works great on my Office 2010. However, I have Office 2013 on my other computer (on Windows 10) and when I try to add the add-in it says it's not a valid add-in. Do you have a version compatible with Office 2013?

Check the properties in file explorer, Windows may have disabled functionality from the original download, and you can permit this. It works on versions from 2003 to 2016 as far as I can see. There will only ever be 1 version. I have just resaved the d/l file so maybe try downloading it again

I notice you joined the forum today, presumably as you had been linked to this thread from somewhere else. Could you let me know where you came across it?

Thanks

[eventide]

I have now deleted the link to the dropbox addin while I do some work on the file.

I would ask that older versions of my addin aren't hosted elsewhere by others for upload, for example at the weebly repository.

[GN100]

One small operational query. I have the addin set to run when I open my main price spreadsheet. Having updated the prices yesterday and clicked save and closed the spreadsheet when I next open the spreadsheet I am asked if I want to update it, I can see the last lot of prices correctly in the spreadsheet. Whether I choose update or don't update the prices immediately vanish and display queued. It would be nice to just have them sitting there saved - is there any simple procedure to accomplish this? Possibly I am wanting my cake and eating it as I realise this may be caused by having the addin running automatically when I open my spreadsheet.
If that's the case then I guess I shouldn't have the addin auto run.

[eventide]

One small operational query. I have the addin set to run when I open my main price spreadsheet. Having updated the prices yesterday and clicked save and closed the spreadsheet when I next open the spreadsheet I am asked if I want to update it, I can see the last lot of prices correctly in the spreadsheet. Whether I choose update or don't update the prices immediately vanish and display queued. It would be nice to just have them sitting there saved - is there any simple procedure to accomplish this? Possibly I am wanting my cake and eating it as I realise this may be caused by having the addin running automatically when I open my spreadsheet.
If that's the case then I guess I shouldn't have the addin auto run.

(1) to eliminate the alert:
data>edit links>startup prompt> "don't display and update links "

I do recommend this.


(2) to stop automatic flush to ::queued::
tools>options>calculation>manual

I do not recommend this but it is personal preference. Any .xls or .xlsm, no matter how large, which needs manual calculation enabled is badly constructed.

Leaving this setting at automatic means that opening a wb will naturally deliver ::queued:: but that is exactly the behaviour desired given the price data will be stale. Calls to the server should be and are user controlled by a mouse right click. It would a trivial code change for the addin to automatically refresh the price when a new ticker (or a new opened workbook) is encountered, but I have preferred not to go down this route.

[GN100]

Thanks Eventide, I'll give those a try and see how I work with them. It's nice to put the final polish on a shiny new tool by understanding how to work with it.

[peterh]

Has anyone experienced problems with the add-in?

Within a single Excel session, the 'getdata' function works perfectly and I can see the right click items. However, after closing the spreadsheet and Excel, I am not able to start Excel again (either to open the file that uses the add-in or just to creae a new file) without error/warning messages.

The first time I try, I see that 'Microsoft Excel has stopped working' and I have to 'close program'. The second time, the message I get is that 'Excel is running into problems with the 'c\users\...\addins\tlfaddin.xla' and I then have to take the option to disable the add-in to get into Excel. I can then work in Excel as normal. The 'getdata' formulae I created in my LivePrice lookalike file now contain a reference with the full path to 'tlfaddin.xla'. I can er-enable the add-in to work on the file, but I have to disable the add-in afterwards in order to be able get back into Excel in the future!

I'm using Excel 2016 (build 8625) which appears to be up to date.

[eventide]

I can recreate this error and I am looking into it.
When there is a new version which has eliminated this problem, I will update this thread.

[Instep]

One small operational query. I have the addin set to run when I open my main price spreadsheet. Having updated the prices yesterday and clicked save and closed the spreadsheet when I next open the spreadsheet I am asked if I want to update it, I can see the last lot of prices correctly in the spreadsheet. Whether I choose update or don't update the prices immediately vanish and display queued. It would be nice to just have them sitting there saved - is there any simple procedure to accomplish this? Possibly I am wanting my cake and eating it as I realise this may be caused by having the addin running automatically when I open my spreadsheet.
If that's the case then I guess I shouldn't have the addin auto run.

.... Calls to the server should be and are user controlled by a mouse right click. It would a trivial code change for the addin to automatically refresh the price when a new ticker (or a new opened workbook) is encountered, but I have preferred not to go down this route.

Thanks eventide for all your efforts.
Hope this is a quick query.
Is it possible to call Sub RefreshPrices() from vba code in my own routine, rather than from the right mouse click.
I have built a sheet called tlfgetdata and routinely copy data from that and paste the values into my own spreadsheet.
Thanks again
Instep

[peterh]

I can recreate this error and I am looking into it.
When there is a new version which has eliminated this problem, I will update this thread.

Thanks for the speedy reply, eventide! Hope you find a solution!

[eventide]

Hope this is a quick query.
Is it possible to call Sub RefreshPrices() from vba code in my own routine, rather than from the right mouse click.
I have built a sheet called tlfgetdata and routinely copy data from that and paste the values into my own spreadsheet.
Thanks again
Instep

I suppose, but it seems a bit circuitous. The whole idea of addins is they expose their functionality to all open workbooks.

[eventide]

To keep the addin download link at the top, and because the link is changed so I'm not hosting it myself, I made a new thread and will try and support user queries on there (& not here anymore)

viewtopic.php?f=27&t=8332

[Scruple]

Is it possible to call Sub RefreshPrices() from vba code in my own routine, rather than from the right mouse click.
I have built a sheet called tlfgetdata and routinely copy data from that and paste the values into my own spreadsheet.
Thanks again
Instep

Hi Instep,

I have done this:

Create the following macro in your spreadsheet:

Public Sub Refresh()
Application.Run ("RefreshPrices")
End Sub

I have used macro options to attach a shortcut key, Ctrl+e in my case, to invoke the refresh.

Regards,

Nigel

[Instep]

Is it possible to call Sub RefreshPrices() from vba code in my own routine, rather than from the right mouse click.
I have built a sheet called tlfgetdata and routinely copy data from that and paste the values into my own spreadsheet.
Thanks again
Instep

Hi Instep,

I have done this:

Create the following macro in your spreadsheet:

Public Sub Refresh()
Application.Run ("RefreshPrices")
End Sub

I have used macro options to attach a shortcut key, Ctrl+e in my case, to invoke the refresh.

Regards,

Nigel

Thanks Nigel
Application.Run ("RefreshPrices")
was the syntax that I needed to be able to make sure the price update is always done before my circuitous copy and paste routine.
It still works with the Updated version that eventide has now produced.

Instep

[dowdman49]

I'm running a bit behind with this thread and have only just tried to open Eventide's Dropbox link but getting a 404 error.
Has it been removed ?