Shares in excel

[julian64]

I'm running excel in 2019 and 2016, don't want t ouse 360 if possible and am trying to get dynamic up to date share prices in a spreadsheet, maybe updating hourly

Has anyone achieved this because at the moment it seems a confused bunch of pay per view APIs with some serious restrictions

[Harpoon]

I don't have an older copy to test but there's a built-in function in Excel 365 for getting stock prices

https://www.thespreadsheetguru.com/blog/add-real-t...

[Mr Pointy]

Someone on one of the IM sticky forums has done it using Sheets I think, picking up the data from a Google link. Not terribly helpful but have a search.

[julian64]

Yep trying to avoid 365 as previously said as don't want to be paying a subscription for just this functionality over 2016/19

possibly need to go searching elsewhere. Thanks anyway

[simon_harris]

I think in 2016 you can do it using power query

[paulrockliffe]

Yes, use Power Query to connect to a webpage or an API that will give you the share prices, then do whatever you need to do to get the numbers to play nicely in there and drop it back into Excel. The VBA to trigger a Refresh of the Power Query process is trivial and you can run that with a button, or on opening the Worksheet or whatever you need.

[julian64]

yup. power query seems to be the way forward. I'll play with it later tonight but wondering on the best site to use to grab data

[Mr Pointy]

Here's a link to the page where Mr KTF mentions his spreadsheet which tracks the shares in the IM PHE fund:
https://www.pistonheads.com/gassing/topic.asp?h=0&...

This is a link to his sheet:
https://docs.google.com/spreadsheets/d/1OcmNBHzEYi...

[paulrockliffe]

The IM website will happily give you .csv files, they're trivial to load and combine in Power Query, you can easily set things up so you download the file, put it in a folder, click refresh and get the updated time series to track performance.

I keep meaning to set it up as a feed into my Power BI setup so I can put some simple graphs on my wife's phone alongside all our other financial stuff, but the list of things I keep meaning to do is never ending....

[julian64]

thanks all. I've been converting over to google sheets finance, but certainly feel more at home with excel and will have a look.