How to download data?

Discuss your favourite picks, broker, and trading or investment style.
BRIAN5000
Veteran Contributor
Veteran Contributor
Posts: 9063
Joined: 08 Jun 2007 23:27

Re: How to download data?

Post by BRIAN5000 »

pmj wrote: 20 Nov 2017 18:23
BRIAN5000 wrote: 20 Nov 2017 15:28 Spreadsheet seems to be working ok except how do request a reload of new/current info? On Gummies spreadsheet there was a button to click on must be something easy?
Easiest way is just to change a cell. Doesn't have to be part of the data. That will force a reload.
Yeah I found that out but now another problem saved it, closed it, reopened it, just getting "name" in every cell, dam it.
This information is believed to be from reliable sources but may include rumor and speculation. Accuracy is not guaranteed
User avatar
Peculiar_Investor
Administrator
Administrator
Posts: 13267
Joined: 01 Mar 2005 14:52
Location: Calgary
Contact:

Re: How to download data?

Post by Peculiar_Investor »

BRIAN5000 wrote: 20 Nov 2017 15:28 Spreadsheet seems to be working ok except how do request a reload of new/current info? On Gummies spreadsheet there was a button to click on must be something easy?
I would suggest you read FAQ: What is the smfForceRecalculation macro and how do I use it?
Imagefiniki, the Canadian financial wiki New editors wanted and welcomed, please help collaborate and improve the wiki.

Normal people… believe that if it ain’t broke, don’t fix it. Engineers believe that if it ain’t broke, it doesn’t have enough features yet. – Scott Adams
User avatar
Arby
Veteran Contributor
Veteran Contributor
Posts: 3125
Joined: 20 Feb 2005 19:23
Location: Ottawa, ON

Re: How to download data?

Post by Arby »

There are some posts upthread about using the SMF add-in with Libre Office, however the downloaded price quotes are not accurate. Has anyone been able to figure out why? The Libre Office formula for obtaining the price quote is
VALUE(com.smf.ticker.getinfo.python.smfimpl.getyahoo($C3,21))
knotley
Contributor
Contributor
Posts: 244
Joined: 20 Feb 2005 19:19

Re: How to download data?

Post by knotley »

chufinora wrote: 08 Nov 2017 19:44 Here is a link to an (adapted) spreadsheet (Google sheet) I use to track my portfolio daily changes



Just select make a copy (You will need to be logged into a google account to do this) under file tab

Change ticker symbols, and the amount of stocks for each one and 'voila'

Each time you open the sheet - the sheet will update with the latest quote values (or will update periodically if you leave it open)

(And to Oldpos if you look at the sheet, it has conditional formatting just like Excel)

Thank you for this spreadsheet. Wonderful.
BRIAN5000
Veteran Contributor
Veteran Contributor
Posts: 9063
Joined: 08 Jun 2007 23:27

Re: How to download data?

Post by BRIAN5000 »

however the downloaded price quotes are not accurate
Down loaded prices don't seem correct with Excel either.
This information is believed to be from reliable sources but may include rumor and speculation. Accuracy is not guaranteed
User avatar
Peculiar_Investor
Administrator
Administrator
Posts: 13267
Joined: 01 Mar 2005 14:52
Location: Calgary
Contact:

Re: How to download data?

Post by Peculiar_Investor »

Does anyone have a solution to the case when Yahoo! Finance is missing a ticker symbol? I found Missing ticker symbol on Yahoo Finance | Finance for Desktop Help - SLN2257
Yahoo Finance doesn't fully cover all pink sheets, preferred stocks, or American Depository Receipts (ADRs).
Is there a means to request Yahoo! Finance to cover symbols they don't already cover?

There are a couple of Brookfield preferred shares (BAM.PR.A and BAM.PR.F) that I'm beginning to study and neither has a quote available via Yahoo! Finance. I could get quotes elsewhere, but I'm hoping to integrate them into an existing spreadsheet that is based on Yahoo! Finance quotes via the SMF Add-in.
Imagefiniki, the Canadian financial wiki New editors wanted and welcomed, please help collaborate and improve the wiki.

Normal people… believe that if it ain’t broke, don’t fix it. Engineers believe that if it ain’t broke, it doesn’t have enough features yet. – Scott Adams
User avatar
Peculiar_Investor
Administrator
Administrator
Posts: 13267
Joined: 01 Mar 2005 14:52
Location: Calgary
Contact:

Re: How to download data?

Post by Peculiar_Investor »

Peculiar_Investor wrote: 28 Nov 2017 11:33 There are a couple of Brookfield preferred shares (BAM.PR.A and BAM.PR.F) that I'm beginning to study and neither has a quote available via Yahoo! Finance.
Further sleuthing has revealed that Yahoo! Finance does have quotes for these two preferred shares, they just don't follow the normal convention that Yahoo! Finance uses for Canadian preferred shares and even other Brookfield preferred shares. Found that they are https://finance.yahoo.com/quote/BAM-PFA.TO and https://finance.yahoo.com/quote/BAM-PFF.TO.
Imagefiniki, the Canadian financial wiki New editors wanted and welcomed, please help collaborate and improve the wiki.

Normal people… believe that if it ain’t broke, don’t fix it. Engineers believe that if it ain’t broke, it doesn’t have enough features yet. – Scott Adams
BRIAN5000
Veteran Contributor
Veteran Contributor
Posts: 9063
Joined: 08 Jun 2007 23:27

Re: How to download data?

Post by BRIAN5000 »

I'm looking for symbols to download data for TOTAL RETURN indexes for the TSX, S&P 500 and a European index from YAHOO according to my master investable ETF's won't do.

There is this https://markets.ft.com/data/indices/tea ... TR0000:TOR from FT but can't pull with speardsheet, using these makes no sense to me at all but I'm not the boss.
This information is believed to be from reliable sources but may include rumor and speculation. Accuracy is not guaranteed
Hogwild
Contributor
Contributor
Posts: 393
Joined: 18 Aug 2015 21:46

Re: How to download data?

Post by Hogwild »

For anyone who's still figuring out how to recalc/refresh the data in the SMF plugin, right click on any cell, hover your mouse over SMF and click "Recalculate Worksheet" (or Recalculate Cell if you want to speed things up by just doing one cell)

Now, if only I could figure out how to obtain a simple stock quote using SMF and Google Finance.
Attachments
SMF Plugin Recalc.jpg
BRIAN5000
Veteran Contributor
Veteran Contributor
Posts: 9063
Joined: 08 Jun 2007 23:27

Re: How to download data?

Post by BRIAN5000 »

Not knowing anything about arrays and just some general ability to work with excel I found it hard to use SMF after saving my spreadsheet. Upon opening the array returned NAME in each cell. A uTube video on how to use the smfFixLinks macro to remove the hard coded location in SMF add-in templates helped,

And this page which may be already posted may be of help, http://ogres-crypt.com/SMF/Tips-and-FAQs/
This information is believed to be from reliable sources but may include rumor and speculation. Accuracy is not guaranteed
FI40
Contributor
Contributor
Posts: 60
Joined: 05 Nov 2015 20:47

Re: How to download data?

Post by FI40 »

I use this free service:

https://www.alphavantage.co/

I use this in my portfolio tracker Excel sheet to download just the latest stock price after Yahoo stopped working for me, and it has been great. I think you can get time series from it too, and other stuff.

I can post an example spreadsheet that pulls in current prices for a given ticker list if there is interest.
Hogwild
Contributor
Contributor
Posts: 393
Joined: 18 Aug 2015 21:46

Re: How to download data?

Post by Hogwild »

Is there a good tutorial somewhere on how to use the SMF plugin? I'm finding it confusing, and I used to be an IT tech. for al living.
Hogwild
Contributor
Contributor
Posts: 393
Joined: 18 Aug 2015 21:46

Re: How to download data?

Post by Hogwild »

I think I got the SMF plugin installed (of course the instructions are wildly out of date). I put it in a trusted Excel folder and installed it from there. It still doesn't work with one of the standard SMF templates. Can anyone suggest anything? No one is responding to my questions on Google's SMF Group.
User avatar
Peculiar_Investor
Administrator
Administrator
Posts: 13267
Joined: 01 Mar 2005 14:52
Location: Calgary
Contact:

Re: How to download data?

Post by Peculiar_Investor »

The original developer passed away and it is now maintained at https://climbermel.github.io/SMF_Add-in/

Try the instructions at SMF Add-In --Tips and FAQS -- Installation
Imagefiniki, the Canadian financial wiki New editors wanted and welcomed, please help collaborate and improve the wiki.

Normal people… believe that if it ain’t broke, don’t fix it. Engineers believe that if it ain’t broke, it doesn’t have enough features yet. – Scott Adams
Hogwild
Contributor
Contributor
Posts: 393
Joined: 18 Aug 2015 21:46

Re: How to download data?

Post by Hogwild »

That's what I used, thanks. I guess? Excel is loading the plugin, as there's an SMF context menu when you right-click on a cell. But it's still not downloading data, just displaying #NAME? for every cell.

Anyone?
BRIAN5000
Veteran Contributor
Veteran Contributor
Posts: 9063
Joined: 08 Jun 2007 23:27

Re: How to download data?

Post by BRIAN5000 »

Hogwild wrote: 20 Sep 2023 13:13 That's what I used, thanks. I guess? Excel is loading the plugin, as there's an SMF context menu when you right-click on a cell. But it's still not downloading data, just displaying #NAME? for every cell.

Anyone?
With spreadsheet open hold down ALT button then hit "T" then "I" when the box opens hit "browse" go find the SMF folder, open it, click on RCH.....then OK, YES OK, I think.
SMF.png
SMF.png (11.39 KiB) Viewed 960 times
This information is believed to be from reliable sources but may include rumor and speculation. Accuracy is not guaranteed
Hogwild
Contributor
Contributor
Posts: 393
Joined: 18 Aug 2015 21:46

Re: How to download data?

Post by Hogwild »

Thanks, I already did that. The add-in is listed in the add-ins list in options. But it isn't fetching quotes.
BRIAN5000
Veteran Contributor
Veteran Contributor
Posts: 9063
Joined: 08 Jun 2007 23:27

Re: How to download data?

Post by BRIAN5000 »

Hogwild wrote: 21 Sep 2023 12:34 Thanks, I already did that. The add-in is listed in the add-ins list in options. But it isn't fetching quotes.
Each time my computer shuts down I have to do as per the previous message to get the APP to work. Your close, when it says NAME I just redo and it works.
This information is believed to be from reliable sources but may include rumor and speculation. Accuracy is not guaranteed
Hogwild
Contributor
Contributor
Posts: 393
Joined: 18 Aug 2015 21:46

Re: How to download data?

Post by Hogwild »

Thanks for the suggestion, but it's not working for me.

Did you read this section at the bottom of the Install instructions? That may help you with your problem above.

Special Notes

In July of 2016, Microsoft did a security update that affects the latest several versions of the O/S and EXCEL. This security update inadvertently prevents EXCEL from being able to load add-ins when it starts up. Until they can fix the problem, they have suggested a workaround of unblocking the XLA file. For more information.
Maybe that'll help you?
Hogwild
Contributor
Contributor
Posts: 393
Joined: 18 Aug 2015 21:46

Re: How to download data?

Post by Hogwild »

Does anyone know how to formulate simple queries for the SMF add-in?

I finally got it loaded and working, but the documentation for it is..well...a mess, and I don't understand it.
BRIAN5000
Veteran Contributor
Veteran Contributor
Posts: 9063
Joined: 08 Jun 2007 23:27

Re: How to download data?

Post by BRIAN5000 »

Hogwild wrote: 13 Dec 2023 18:34 Does anyone know how to formulate simple queries for the SMF add-in?

I finally got it loaded and working, but the documentation for it is..well...a mess, and I don't understand it.
Send me a copy of your spreadsheet that is working I will give it a go.
This information is believed to be from reliable sources but may include rumor and speculation. Accuracy is not guaranteed
Hogwild
Contributor
Contributor
Posts: 393
Joined: 18 Aug 2015 21:46

Re: How to download data?

Post by Hogwild »

Thanks BRIAN5000 for the generous offer. However, I have a lot of private stuff on the spreadsheet, and it would take quite a while to remove it.

I tried the history function, and it appeared to fetch correct data. But I'm looking for whatever function is simplest. 99% of the time, all I need is last price. As I understand it, Yahoo removed an earlier function (Can't remember the name of it) that allowed for simple, one item quotes.

Is there no substitute for whatever Yahoo disabled? Can I use another data source?
BRIAN5000
Veteran Contributor
Veteran Contributor
Posts: 9063
Joined: 08 Jun 2007 23:27

Re: How to download data?

Post by BRIAN5000 »

Hogwild wrote: 18 Dec 2023 12:21 Thanks BRIAN5000 for the generous offer. However, I have a lot of private stuff on the spreadsheet, and it would take quite a while to remove it.

I tried the history function, and it appeared to fetch correct data. But I'm looking for whatever function is simplest. 99% of the time, all I need is last price. As I understand it, Yahoo removed an earlier function (Can't remember the name of it) that allowed for simple, one item quotes.

Is there no substitute for whatever Yahoo disabled? Can I use another data source?
I'm surely not looking for personal data did you download the spreadsheet from me on the other thread, did it work?
This information is believed to be from reliable sources but may include rumor and speculation. Accuracy is not guaranteed
OnlyMyOpinion
Veteran Contributor
Veteran Contributor
Posts: 4231
Joined: 24 Jan 2014 23:17

Re: How to download data?

Post by OnlyMyOpinion »

Hogwild wrote: 18 Dec 2023 12:21 Thanks BRIAN5000 for the generous offer. However, I have a lot of private stuff on the spreadsheet, and it would take quite a while to remove it.

I tried the history function, and it appeared to fetch correct data. But I'm looking for whatever function is simplest. 99% of the time, all I need is last price. As I understand it, Yahoo removed an earlier function (Can't remember the name of it) that allowed for simple, one item quotes.

Is there no substitute for whatever Yahoo disabled? Can I use another data source?
Maybe not ths solution you're looking for, particularly if these are securities you don't own but want to download for some reason... but:
When I need data related to my holdings, I just download the csv 'holdings' file from TDDI (other brokers must be similar).

This gives me a list of securities, number, BV, current MV, total BV and MV, $gain/loss, etc. To consolidate all 7 accounts I just download each and paste them into one worksheet that can roll up the total, subtotal by acc, %, etc.
It takes about 10 minutes.

I have some added columns at the end that I copy from the previous rollup that lists div/sh (I update when increases occur), and multiplies by the #sh column, so I know what our total acc income is.

Works for us anyway.
BRIAN5000
Veteran Contributor
Veteran Contributor
Posts: 9063
Joined: 08 Jun 2007 23:27

Re: How to download data?

Post by BRIAN5000 »

Getting closer losing interest. I think you need to make a new array on a sheet in your spreadsheet then just link it to where you want the price to show up.
New Array.png
New Array.png (14.72 KiB) Viewed 265 times
Last edited by BRIAN5000 on 18 Dec 2023 16:53, edited 2 times in total.
This information is believed to be from reliable sources but may include rumor and speculation. Accuracy is not guaranteed
Post Reply