15 Messages
Digest #4325
Messages
Thu Apr 19, 2018 4:53 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The first thing I would check is to make sure both computers are running
the same version of the add-in:
=RCHGetElementNumber("Version")
The normal EXCEL recalculate shouldn't make a difference. You would need to
use the smfForceRecalculation macro from the add-in.
On Thu, Apr 19, 2018 at 3:57 PM, jterrenceho@
...
wrote:
> SmfPricesByDates works on one computer but not on another. I remember
> reading on this before, but I couldn't find the thread. I remember you
> saying that it has something to do with security settings.
>
> I tried recalculate and I also tried clearing my browser.
>
>
>
the same version of the add-in:
=RCHGetElementNumbe
The normal EXCEL recalculate shouldn'
use the smfForceRecalculati
On Thu, Apr 19, 2018 at 3:57 PM, jterrenceho@
...
wrote:
> SmfPricesByDates works on one computer but not on another. I remember
> reading on this before, but I couldn't find the thread. I remember you
> saying that it has something to do with security settings.
>
> I tried recalculate and I also tried clearing my browser.
>
>
>
Fri Apr 20, 2018 1:36 am (PDT) . Posted by:
zellabettic
Hello,
I have this function: =RCHGetTableCell("http://etfs.morningstar.com/quote-banner?&t=SPY",0,"Category")
The data retrieved is: "CategoryLarge Growth"
Does anyone know how to adjust the formula to just pull "Large Growth"?
Any help would be much appreciated. Thank you,
Clete
I have this function: =RCHGetTableCell("http://etfs.morningstar.com/quote-banner?&t=SPY",0,"
The data retrieved is: "CategoryLarge Growth"
Does anyone know how to adjust the formula to just pull "Large Growth"
Any help would be much appreciated. Thank you,
Clete
Fri Apr 20, 2018 4:08 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Either of these should work:
=SUBSTITUTE(RCHGetTableCell("http://etfs.morningstar.com/quote-banner?&t=SPY
",0,"Category"),"Category","")
=smfGetTagContent("http://etfs.morningstar.com/quote-banner?&t=SPY
","span",1,"Category")
On Fri, Apr 20, 2018 at 1:26 AM, zellabettic@
...
wrote:
> I have this function: =RCHGetTableCell("http://etfs.morningstar..com/
> quote-banner?&t=SPY",0,"Category")
>
> The data retrieved is: "CategoryLarge Growth"
>
> Does anyone know how to adjust the formula to just pull "Large Growth"?
>
>
>
=SUBSTITUTE(RCHGetTableCell("http://etfs.morningstar.com/quote-banner?&t=SPY
",0,"Category"),"Category","")
=smfGetTagContent("http://etfs.morningstar.com/quote-banner?&t=SPY
","span",1,"Category")
On Fri, Apr 20, 2018 at 1:26 AM, zellabettic@
...
wrote:
> I have this function: =RCHGetTableCell("http://etfs.morningstar..com/
> quote-banner?
>
> The data retrieved is: "CategoryLarge Growth"
>
> Does anyone know how to adjust the formula to just pull "Large Growth"
>
>
>
Fri Apr 20, 2018 9:25 am (PDT) . Posted by:
"Kermit W. Prather" kermitpra
It maybe that you have the cell formatted wrong.
If you right click on the cell, then click format cell, what does it show for the format?
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Thursday, April 19, 2018 4:07 AM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] I can not get rid of the decimal
Hello:
I have an excel data sheet, every time I type numbers in 1,000 excel automatically inserts a decimal in the middle so it looks like 10.00. I have tried everything to get rid of it and so far nothing has worked. So I turn to you fine folks for help. Any suggestions? Thanks in advance.
Regards
Ethan
If you right click on the cell, then click format cell, what does it show for the format?
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Thursday, April 19, 2018 4:07 AM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] I can not get rid of the decimal
Hello:
I have an excel data sheet, every time I type numbers in 1,000 excel automatically inserts a decimal in the middle so it looks like 10.00. I have tried everything to get rid of it and so far nothing has worked. So I turn to you fine folks for help. Any suggestions? Thanks in advance.
Regards
Ethan
Fri Apr 20, 2018 1:19 pm (PDT) . Posted by:
cancerfixer
Don't know if this is a transient problem or if Yahoo is up to their tricks again.
I have an excel spreadsheet that, until today, was routinely retrieving quotes for 255 tickers every day using the smfGetYahooPortfolioView array entered formula
I sort & copy tickers over to a scratch page, query yahoo, then copy the data over to a different portfolio sheet and erase the scratch sheet. a4:a404 on the scratch sheet contains up to 400 tickers, which depends on what is on my portfolio page. (I have maxed lookups via hard code to 400 tickers). b2 to ad2 contains the desired numeric field codes in text format.
Here's the data retrieval line:
Range("b4:ad404").FormulaArray = "=smfGetYahooPortfolioView(a4:a404,b2:ad2,,0)"
Today I discovered that the bottom part of my ticker list was not returning data. Digging into the macro code and pausing execution after the query, I found that yahoo was only retrieving data for the first 142 tickers - every time. The remainder of the tickers contained only dashes for data.
It's not my code or the smf routine. It's worked for months, and continues to work - just limits me to 142 tickers.... Now, not so well. I'm suspicious Yahoo changed something.
I have another routine that can get the data from barchart (written just in case). It works fine, but does not have as much data as yahoo. I'd prefer to keep using yahoo, but if others find the same limitation, I'll have to re-write the code to do several sequential queries, each with a max of 100 or 140 or so tickers. Comments? Others noted this limitation?
I have an excel spreadsheet that, until today, was routinely retrieving quotes for 255 tickers every day using the smfGetYahooPortfoli
I sort & copy tickers over to a scratch page, query yahoo, then copy the data over to a different portfolio sheet and erase the scratch sheet. a4:a404 on the scratch sheet contains up to 400 tickers, which depends on what is on my portfolio page. (I have maxed lookups via hard code to 400 tickers). b2 to ad2 contains the desired numeric field codes in text format.
Here's the data retrieval line:
Range("
Today I discovered that the bottom part of my ticker list was not returning data. Digging into the macro code and pausing execution after the query, I found that yahoo was only retrieving data for the first 142 tickers - every time. The remainder of the tickers contained only dashes for data.
It's not my code or the smf routine. It's worked for months, and continues to work - just limits me to 142 tickers.... Now, not so well. I'm suspicious Yahoo changed something.
I have another routine that can get the data from barchart (written just in case). It works fine, but does not have as much data as yahoo. I'd prefer to keep using yahoo, but if others find the same limitation, I'll have to re-write the code to do several sequential queries, each with a max of 100 or 140 or so tickers. Comments? Others noted this limitation?
Fri Apr 20, 2018 2:08 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
I just tried with the template, pasting in the S&P 500 tickers and got over
400 tickers back (whatever the default size of the template was).
However, when I first pasted it, I only got 96 tickers returned. It turned
out I had an incorrect ticker symbol for Yahoo, as I was pasting a list of
tickers I got from Google -- one had a period instead of a dash. Once I did
a "change all" to fix such ticker errors, I got all of the array.
Check your ticker symbols. If that isn't the issue, pull up a copy of the
template from the web site, paste in your list of ticker symbols, and
attach that workbook to a message and I'll see if I can spot something.
On Fri, Apr 20, 2018 at 1:09 PM, cancerfixer@
... w
rote:
> Don't know if this is a transient problem or if Yahoo is up to their
> tricks again.
>
> I have an excel spreadsheet that, until today, was routinely retrieving
> quotes for 255 tickers every day using the smfGetYahooPortfolioView array
> entered formula
>
>
> I sort & copy tickers over to a scratch page, query yahoo, then copy the
> data over to a different portfolio sheet and erase the scratch sheet. a4:a404
> on the scratch sheet contains up to 400 tickers, which depends on what is
> on my portfolio page. (I have maxed lookups via hard code to 400 tickers).
> b2 to ad2 contains the desired numeric field codes in text format.
>
> Here's the data retrieval line:
>
> Range("b4:ad404").FormulaArray = "=smfGetYahooPortfolioView(a4:
> a404,b2:ad2,,0)"
>
> Today I discovered that the bottom part of my ticker list was not
> returning data. Digging into the macro code and pausing execution after the
> query, I found that yahoo was only retrieving data for the first 142
> tickers - every time. The remainder of the tickers contained only dashes
> for data.
>
> It's not my code or the smf routine. It's worked for months, and
> continues to work - just limits me to 142 tickers.... Now, not so well..
> I'm suspicious Yahoo changed something.
>
> I have another routine that can get the data from barchart (written just
> in case). It works fine, but does not have as much data as yahoo. I'd
> prefer to keep using yahoo, but if others find the same limitation, I'll
> have to re-write the code to do several sequential queries, each with a max
> of 100 or 140 or so tickers. Comments? Others noted this limitation?
>
>
>
400 tickers back (whatever the default size of the template was).
However, when I first pasted it, I only got 96 tickers returned. It turned
out I had an incorrect ticker symbol for Yahoo, as I was pasting a list of
tickers I got from Google -- one had a period instead of a dash. Once I did
a "change all" to fix such ticker errors, I got all of the array.
Check your ticker symbols. If that isn't the issue, pull up a copy of the
template from the web site, paste in your list of ticker symbols, and
attach that workbook to a message and I'll see if I can spot something.
On Fri, Apr 20, 2018 at 1:09 PM, cancerfixer@
... w
rote:
> Don't know if this is a transient problem or if Yahoo is up to their
> tricks again.
>
> I have an excel spreadsheet that, until today, was routinely retrieving
> quotes for 255 tickers every day using the smfGetYahooPortfoli
> entered formula
>
>
> I sort & copy tickers over to a scratch page, query yahoo, then copy the
> data over to a different portfolio sheet and erase the scratch sheet. a4:a404
> on the scratch sheet contains up to 400 tickers, which depends on what is
> on my portfolio page. (I have maxed lookups via hard code to 400 tickers).
> b2 to ad2 contains the desired numeric field codes in text format.
>
> Here's the data retrieval line:
>
> Range("
> a404,b2:ad2,
>
> Today I discovered that the bottom part of my ticker list was not
> returning data. Digging into the macro code and pausing execution after the
> query, I found that yahoo was only retrieving data for the first 142
> tickers - every time. The remainder of the tickers contained only dashes
> for data.
>
> It's not my code or the smf routine. It's worked for months, and
> continues to work - just limits me to 142 tickers.... Now, not so well..
> I'm suspicious Yahoo changed something.
>
> I have another routine that can get the data from barchart (written just
> in case). It works fine, but does not have as much data as yahoo. I'd
> prefer to keep using yahoo, but if others find the same limitation, I'll
> have to re-write the code to do several sequential queries, each with a max
> of 100 or 140 or so tickers. Comments? Others noted this limitation?
>
>
>
Fri Apr 20, 2018 7:07 pm (PDT) . Posted by:
cancerfixer
Fixed it! It was failing to pull up NRZ and all tickers after that Just 2 rows above NRZ was a ticker in the list as "NO NUMBER". I think I now how it got there. When I deleted it, the query worked.
First, I'll try to figure out how it got there. Probably one of my bank notes that use CUSIPS and are not tracked in my spreadsheet. Just to be sure, I will insert a brief if/then check for "NO NUMBER" and exclude that from the queries.
Thanks for your help!
First, I'll try to figure out how it got there. Probably one of my bank notes that use CUSIPS and are not tracked in my spreadsheet. Just to be sure, I will insert a brief if/then check for "NO NUMBER" and exclude that from the queries.
Thanks for your help!
Fri Apr 20, 2018 1:21 pm (PDT) . Posted by:
freefaller6
Hello Group, anyone having issues with Element #7786-7795? These are the ROCI (or ROIC) annual #s for years FY1-FY10.
Second question - why such a big difference in the ROCI values depending on the source website? Which source do most people use, or find the most accurate?
Thanks,
Brent
Second question - why such a big difference in the ROCI values depending on the source website? Which source do most people use, or find the most accurate?
Thanks,
Brent
Fri Apr 20, 2018 1:56 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
What issues are you having with the element number? They are returning data
here. I looked at MMM and it appears they change the magnitude (e.g. 0.2
versus 20) when comparing the most recent years to older years. But the
element numbers are returning what's on the web site.
One problem with mixing data from different sources is that they can define
them differently.
On Fri, Apr 20, 2018 at 12:51 PM, freefaller6@
...
wrote:
> Hello Group, anyone having issues with Element #7786-7795? These are the
> ROCI (or ROIC) annual #s for years FY1-FY10.
>
> Second question - why such a big difference in the ROCI values depending
> on the source website? Which source do most people use, or find the most
> accurate?
>
>
>
here. I looked at MMM and it appears they change the magnitude (e.g. 0.2
versus 20) when comparing the most recent years to older years. But the
element numbers are returning what's on the web site.
One problem with mixing data from different sources is that they can define
them differently.
On Fri, Apr 20, 2018 at 12:51 PM, freefaller6@
...
wrote:
> Hello Group, anyone having issues with Element #7786-7795? These are the
> ROCI (or ROIC) annual #s for years FY1-FY10.
>
> Second question - why such a big difference in the ROCI values depending
> on the source website? Which source do most people use, or find the most
> accurate?
>
>
>
Fri Apr 20, 2018 3:24 pm (PDT) . Posted by:
freefaller6
Hi Randy, it has to be something with their site - most values are 0.0, 0.1, 0.2, and unfortunately, if you expand the number of decimals in excel, there is nothing but zeros after the tenths digit.
I tried for a few hours to create an element # from this source: http://financials.morningstar.com/ratios/r.html?t=f http://financials.morningstar..com/ratios/r.html?t=f, but I just kept getting errors. Then I looked at all the templates to see if any of them had the 10 year annual ROIC values, and one of them did! The Advfn template, so I am back where I started.
I drafted the "Creating Elements for Dummies" post if you recall, but on sites with multiple tabs and tables, I just couldn't figure it out.
Any guidance is always appreciated, thanks.
Brent
I tried for a few hours to create an element # from this source: http://financials.morningstar.com/ratios/r.html?t=f http://financials.morningstar..com/ratios/r.html?t=f, but I just kept getting errors. Then I looked at all the templates to see if any of them had the 10 year annual ROIC values, and one of them did! The Advfn template, so I am back where I started.
I drafted the "Creating Elements for Dummies" post if you recall, but on sites with multiple tabs and tables, I just couldn't figure it out.
Any guidance is always appreciated, thanks.
Brent
Fri Apr 20, 2018 4:53 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The ratios and financials from Morningstar are available on a CSV file. See
this from the "FAQs and Tips" page on the web site:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/19776
You could define an element for the extraction. For example, the formula to
just extract TTM would be something like:
=smfWord(smfStrExtr(RCHGetWebData("
http://financials.morningstar.com/ajax/exportKR2CSV.html?t=F","Return on
Invested Capital"),"~","Interest"),12,",",1)
Just vary the 12 from 11 to 2 for the other years.
On Fri, Apr 20, 2018 at 2:44 PM, freefaller6@
...
wrote:
>
> Hi Randy, it has to be something with their site - most values are 0.0,
> 0.1, 0.2, and unfortunately, if you expand the number of decimals in excel,
> there is nothing but zeros after the tenths digit.
>
> I tried for a few hours to create an element # from this source:
> http://financials.morningstar.com/ratios/r.html?t=f, but I just kept
> getting errors. Then I looked at all the templates to see if any of them
> had the 10 year annual ROIC values, and one of them did! The Advfn
> template, so I am back where I started.
>
> I drafted the "Creating Elements for Dummies" post if you recall, but on
> sites with multiple tabs and tables, I just couldn't figure it out..
>
> Any guidance is always appreciated, thanks.
>
>
this from the "FAQs and Tips" page on the web site:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/19776
You could define an element for the extraction. For example, the formula to
just extract TTM would be something like:
=smfWord(smfStrExtr(RCHGetWebData("
http://financials.morningstar.com/ajax/exportKR2CSV.html?t=F","Return on
Invested Capital"),"~","Interest"),12,",",1)
Just vary the 12 from 11 to 2 for the other years.
On Fri, Apr 20, 2018 at 2:44 PM, freefaller6@
...
wrote:
>
> Hi Randy, it has to be something with their site - most values are 0.0,
> 0.1, 0.2, and unfortunately, if you expand the number of decimals in excel,
> there is nothing but zeros after the tenths digit.
>
> I tried for a few hours to create an element # from this source:
> http://financials.morningstar.com/ratios/r.html?t=f, but I just kept
> getting errors. Then I looked at all the templates to see if any of them
> had the 10 year annual ROIC values, and one of them did! The Advfn
> template, so I am back where I started.
>
> I drafted the "Creating Elements for Dummies" post if you recall, but on
> sites with multiple tabs and tables, I just couldn't figure it out..
>
> Any guidance is always appreciated, thanks.
>
>
Fri Apr 20, 2018 7:48 pm (PDT) . Posted by:
"Pieter van Leeuwen" pietertvl
Hi again Randy
Tried to give your detailed response a thorough review before replying, but I'm also constrained by the limited time I've had available since your reply.
You cannot array-enter RCHGetElementNumber() over a range to get multiple data items.
OK ... no array entry formatting for RCH Get Element. That was a reach..
If you're creating a table like that, I would suggest looking at the smfUpdateDownloadTable macro.
Did look briefly at the smf Update Download Table ... but there's no documentation for it (just placeholder) so I don't know how to point it to the elements that I need: Dividend, Annual Yield, and Payout ratio. Given the webpage pulls that this uses though, this doesn't seem like the ideal answer anyway.
Aren't the data items you want available with Yahoo Portfolio View or Barchart Portfolio View?
I can get some of that via Yahoo PV, but data are reported only for perhaps 25% of the symbols that are or were known divvie stocks. YF is built into my charting software from years ago and its API days, so when the API was disabled, I lost the ability to download fundamental data like this. EVEN SO, before that happened, YF was sketchy at best and like you said, probably out of date often too. Simply not very suitable, then or now.
I also tried out BarChart PV, and just as in your example (see its documentation, "example template") all the entries come up #NAME?. As below.
Documentation notes that it uses a Barchart Watchlist JSON file. but I'm not clear on what if anything I need to do regarding this JSON file, which I don't knowingly possess.
49
48
47
53
1
9
DATE
DIV
YIELD
RATE
Payout
Symbol
Last
AAV
4/20/2018
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
GGN
4/20/2018
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
=smfGetBarchartPortfolioView(A11:A12,C9:H9)
Given that your example displays similarly, for all listed elements and all your selected symbols, I'm going to assume the issue is likely not at my end. Something changed from when the documentation was created. Needless to say, I have no idea whether the BC data are any better than Yahoo's. AAV is no longer issuing dividends, and GGN does not report a payout ratio from what I can tell, although it has a current yield of 11%.
Just a heads up though that I'm not at all savvy with advanced Excel, macros or visual basic. I can usually follow instructions and examples well, but I'm not good making the leap from a sketch to results.
Unless there's a simple tweak to the BC PV to get the #NAME? cells to display, I'm probably out of luck here.
If anyone else is having success pulling down dividends basics in some fashion from public sources, please chime in! I think the payout ratio is important, otherwise the divvies you are collecting amount to the return of capital, not a real return.
Thanks!
Pieter
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Sunday, April 15, 2018 4:19 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Setup to Get Elements re Dividends
You cannot array-enter RCHGetElementNumber() over a range to get multiple data items. I'm not sure what from the documentation would imply that?
Most of the array-entered functions are for data sources that provide multiple data items for multiple tickers in a single Internet access. There's no reason to do that for RCHGetElementNumber(), because each ticker symbol you use would need to go get its own individual web page anyway. So there would be no speed advantage for me to allow an array-entered capability. Especially since array-entered ranges are difficult to edit and cannot be sorted.
If you're creating a table like that, I would suggest looking at the smfUpdateDownloadTable macro. It can place values into the table when you run the macro, so there is no automatic recalculation. You are in control of which columns, rows, or ranges get updated when the macro runs. This is especially handy on data items that don't change often, like dividend or financial statement data.
In fact, my smfUpdateDownloadTable dividend worksheet does the next earnings announcements and estimated ex-dividend dates. But it's still a work in progress that I'm playing around with. I use it as the source of data for my workbooks that want such information, rather than re-retrieving it from the Internet every time I need it. This way, I update my source workbook and do all my data retrievals there. Then everything runs off of it. It makes it a 2-step process, but speeds up the workbooks downstream. At least for the universe of tickers I've defined. Right now, it looks something like this:
Ticker
TimeStamp
GuruFocus
Next Earn
Date
GuruFocus
Ex-Dividend
Date
GuruFocus
Ex-Dividend
Amount
Yahoo
Ex-Dividend
Date
Best
Ex-Dividend
Date
Future
Ex-Dividend
Date
Flag
NUE
2018-02-11 02:38
2018-04-20
2017-12-28
$0.38
2017-03-29
2018-03-29
2018-03-29
****
CSCO
2018-02-11 02:38
2018-02-15
2018-01-04
$0.29
2017-04-04
2018-04-04
2018-04-04
****
The "Flag" column indicates it is an estimated ex-dividend date and not a published one.
One reason NOT to release something like that is people start building their own databases. That's not a bad thing if you're dealing with a couple dozen stocks, but it's an unfair burden on the free data sources when someone wants to do it for hundreds or thousands of tickers. I get nervous even with your 250 ticker symbols. For the four element numbers you mention, you'd end up retrieving 500 web pages. There is no confirmation element number for any elements.
Aren't the data items you want available with Yahoo Portfolio View or Barchart Portfolio View? I haven't looked at those fields in detail, although in the past Yahoo has been slow on updating dividend items. That's why mine above uses published data items from GuruFocus. I only use Yahoo historical quotes to find out when the ex-dividend date was last year.
On Sun, Apr 15, 2018 at 3:10 PM, 'Pieter van Leeuwen' pieter_biz@
....
wrote:
Looking at the Get Elements Function for the first time. Have been using YahooPortfolioView, which is working well for OHLC quotes.
What's the best way to do something similar for dividend data? 250 tickers, four elements, more or less.
Example from Get Elements documentation implies an array entry retrieval setup may work, but example link is 404.
Can I use an Array Entried format to report back a grid of results? Names down, elements across?
I want to run company names down the B column.
Plan on using
989, 990, 4929 and 13863 (Div, Yld, Payout, Co. Name) on row 9.
And what element if there is one, returns the ticker symbol (as confirmation) ?
So far, my initial attempt to set this up is reporting all #VALUE s. None of the symbol names are in quotes.
Here is the arrayed cell entry formula.
=RCHGetElementNumber(B11:B255,D9:G9)
The Version cell reports back ....
Stock Market Functions add-in, Version 2.1.2017.11.02 (C:\SMF Add-in; Windows (32-bit) NT 6.01; 12.0; ; ; 1)
Thanks for any guidance!
Pieter
Tried to give your detailed response a thorough review before replying, but I'm also constrained by the limited time I've had available since your reply.
You cannot array-enter RCHGetElementNumber() over a range to get multiple data items.
OK ... no array entry formatting for RCH Get Element. That was a reach..
If you're creating a table like that, I would suggest looking at the smfUpdateDownloadTable macro.
Did look briefly at the smf Update Download Table ... but there's no documentation for it (just placeholder) so I don't know how to point it to the elements that I need: Dividend, Annual Yield, and Payout ratio. Given the webpage pulls that this uses though, this doesn't seem like the ideal answer anyway.
Aren't the data items you want available with Yahoo Portfolio View or Barchart Portfolio View?
I can get some of that via Yahoo PV, but data are reported only for perhaps 25% of the symbols that are or were known divvie stocks. YF is built into my charting software from years ago and its API days, so when the API was disabled, I lost the ability to download fundamental data like this. EVEN SO, before that happened, YF was sketchy at best and like you said, probably out of date often too. Simply not very suitable, then or now.
I also tried out BarChart PV, and just as in your example (see its documentation, "example template") all the entries come up #NAME?. As below.
Documentation notes that it uses a Barchart Watchlist JSON file. but I'm not clear on what if anything I need to do regarding this JSON file, which I don't knowingly possess.
49
48
47
53
1
9
DATE
DIV
YIELD
RATE
Payout
Symbol
Last
AAV
4/20/2018
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
GGN
4/20/2018
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
=smfGetBarchartPortfolioView(A11:A12,C9:H9)
Given that your example displays similarly, for all listed elements and all your selected symbols, I'm going to assume the issue is likely not at my end. Something changed from when the documentation was created. Needless to say, I have no idea whether the BC data are any better than Yahoo's. AAV is no longer issuing dividends, and GGN does not report a payout ratio from what I can tell, although it has a current yield of 11%.
Just a heads up though that I'm not at all savvy with advanced Excel, macros or visual basic. I can usually follow instructions and examples well, but I'm not good making the leap from a sketch to results.
Unless there's a simple tweak to the BC PV to get the #NAME? cells to display, I'm probably out of luck here.
If anyone else is having success pulling down dividends basics in some fashion from public sources, please chime in! I think the payout ratio is important, otherwise the divvies you are collecting amount to the return of capital, not a real return.
Thanks!
Pieter
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Sunday, April 15, 2018 4:19 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Setup to Get Elements re Dividends
You cannot array-enter RCHGetElementNumber
Most of the array-entered functions are for data sources that provide multiple data items for multiple tickers in a single Internet access. There's no reason to do that for RCHGetElementNumber
If you're creating a table like that, I would suggest looking at the smfUpdateDownloadTa
In fact, my smfUpdateDownloadTa
Ticker
TimeStamp
GuruFocus
Next Earn
Date
GuruFocus
Ex-Dividend
Date
GuruFocus
Ex-Dividend
Amount
Yahoo
Ex-Dividend
Date
Best
Ex-Dividend
Date
Future
Ex-Dividend
Date
Flag
NUE
2018-02-11 02:38
2018-04-20
2017-12-28
$0.38
2017-03-29
2018-03-29
2018-03-29
****
CSCO
2018-02-11 02:38
2018-02-15
2018-01-04
$0.29
2017-04-04
2018-04-04
2018-04-04
****
The "Flag" column indicates it is an estimated ex-dividend date and not a published one.
One reason NOT to release something like that is people start building their own databases. That's not a bad thing if you're dealing with a couple dozen stocks, but it's an unfair burden on the free data sources when someone wants to do it for hundreds or thousands of tickers. I get nervous even with your 250 ticker symbols. For the four element numbers you mention, you'd end up retrieving 500 web pages. There is no confirmation element number for any elements.
Aren't the data items you want available with Yahoo Portfolio View or Barchart Portfolio View? I haven't looked at those fields in detail, although in the past Yahoo has been slow on updating dividend items. That's why mine above uses published data items from GuruFocus. I only use Yahoo historical quotes to find out when the ex-dividend date was last year.
On Sun, Apr 15, 2018 at 3:10 PM, 'Pieter van Leeuwen' pieter_biz@
....
wrote:
Looking at the Get Elements Function for the first time. Have been using YahooPortfolioView, which is working well for OHLC quotes.
What's the best way to do something similar for dividend data? 250 tickers, four elements, more or less.
Example from Get Elements documentation implies an array entry retrieval setup may work, but example link is 404.
Can I use an Array Entried format to report back a grid of results? Names down, elements across?
I want to run company names down the B column.
Plan on using
989, 990, 4929 and 13863 (Div, Yld, Payout, Co. Name) on row 9.
And what element if there is one, returns the ticker symbol (as confirmation) ?
So far, my initial attempt to set this up is reporting all #VALUE s. None of the symbol names are in quotes.
Here is the arrayed cell entry formula.
=RCHGetElementNumbe
The Version cell reports back ....
Stock Market Functions add-in, Version 2.1.2017.11.
Thanks for any guidance!
Pieter
Fri Apr 20, 2018 10:27 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Replies are below...
On Fri, Apr 20, 2018 at 7:48 PM, 'Pieter van Leeuwen' pieter_biz@
...
wrote:
>
> Tried to give your detailed response a thorough review before
> replying, but I'm also constrained by the limited time I've had available
> since your reply.
>
>
>
> You cannot array-enter RCHGetElementNumber() over a range to get multiple
> data items.
>
>
>
> OK ... no array entry formatting for RCH Get Element. That was
> a reach.
>
>
>
> If you're creating a table like that, I would suggest looking at the
> smfUpdateDownloadTable macro.
>
>
>
> Did look briefly at the smf Update Download Table ... but there's
> no documentation for it (just placeholder) so I don't know how to point it
> to the elements that I need: Dividend, Annual Yield, and Payout ratio.
> Given the webpage pulls that this uses though, this doesn't seem like
> the ideal answer anyway.
>
Right now, the only documentation is links on the "FAQs and Tips" web
page. And the sample workbook on the website, to a lesser degree.
>
>
> *Aren't the data items you want available with Yahoo Portfolio View or
> Barchart Portfolio View?*
>
>
>
> I can get some of that via Yahoo PV, but data are reported only for
> perhaps 25% of the symbols that are or were known divvie stocks. YF is
> built into my charting software from years ago and its API days, so when
> the API was disabled, I lost the ability to download fundamental data like
> this. EVEN SO, before that happened, YF was sketchy at best and like you
> said, probably out of date often too. Simply not very suitable, then or
> now.
>
Yeah. As I indicated, I don't use them much for dividend info.
> I also tried out BarChart PV, and just as in your example (see its
> documentation, "example template") all the entries come up #NAME?. As
> below.
>
> Documentation notes that it uses a Barchart Watchlist JSON file.
> but I'm not clear on what if anything I need to do regarding this JSON
> file, which I don't knowingly possess.
>
>
>
>
> **
>
>
>
> 49
>
> 48
>
> 47
>
> 53
>
> 1
>
> 9
>
>
>
>
>
> *DATE*
>
> *DIV*
>
> *YIELD*
>
> *RATE*
>
> *Payout*
>
> *Symbol*
>
> *Last*
>
>
>
> *AAV*
>
> 4/20/2018
>
> #NAME?
>
> #NAME?
>
> #NAME?
>
> #NAME?
>
> #NAME?
>
> #NAME?
>
> *GGN*
>
> 4/20/2018
>
> #NAME?
>
> #NAME?
>
> #NAME?
>
> #NAME?
>
> #NAME?
>
> #NAME?
>
>
>
>
>
> =smfGetBarchartPortfolioView(A11:A12,C9:H9)
>
>
>
> Given that your example displays similarly, for all listed elements
> and all your selected symbols, I'm going to assume the issue is likely not
> at my end. Something changed from when the documentation was
> created. Needless to say, I have no idea whether the BC data are any
> better than Yahoo's. AAV is no longer issuing dividends, and GGN does
> not report a payout ratio from what I can tell, although it has a current
> yield of 11%.
>
My first suspicion would be that you are running an older version of the
add-in, before I added this function? Usually #NAME? either means your
add-in is in a different location from when I saved the workbook (which can
be fixed with the smfFixLinks macro from the add-in) or the function
doesn't exist (as in having an older version of the add-in).
> Just a heads up though that I'm not at all savvy with advanced
> Excel, macros or visual basic. I can usually follow instructions and
> examples well, but I'm not good making the leap from a sketch to results.
>
>
>
> Unless there's a simple tweak to the BC PV to get the #NAME? cells
> to display, I'm probably out of luck here.
>
>
>
> If anyone else is having success pulling down dividends basics in
> some fashion from public sources, please chime in! I think the payout
> ratio is important, otherwise the divvies you are collecting amount to the
> return of capital, not a real return.
>
Wouldn't payout ratio just be dividends per share divided by earnings per
share?
On Fri, Apr 20, 2018 at 7:48 PM, 'Pieter van Leeuwen' pieter_biz@
...
wrote:
>
> Tried to give your detailed response a thorough review before
> replying, but I'm also constrained by the limited time I've had available
> since your reply.
>
>
>
> You cannot array-enter RCHGetElementNumber
> data items.
>
>
>
> OK ... no array entry formatting for RCH Get Element. That was
> a reach.
>
>
>
> If you're creating a table like that, I would suggest looking at the
> smfUpdateDownloadTa
>
>
>
> Did look briefly at the smf Update Download Table ... but there's
> no documentation for it (just placeholder) so I don't know how to point it
> to the elements that I need: Dividend, Annual Yield, and Payout ratio.
> Given the webpage pulls that this uses though, this doesn't seem like
> the ideal answer anyway.
>
Right now, the only documentation is links on the "FAQs and Tips" web
page. And the sample workbook on the website, to a lesser degree.
>
>
> *Aren't the data items you want available with Yahoo Portfolio View or
> Barchart Portfolio View?*
>
>
>
> I can get some of that via Yahoo PV, but data are reported only for
> perhaps 25% of the symbols that are or were known divvie stocks. YF is
> built into my charting software from years ago and its API days, so when
> the API was disabled, I lost the ability to download fundamental data like
> this. EVEN SO, before that happened, YF was sketchy at best and like you
> said, probably out of date often too. Simply not very suitable, then or
> now.
>
Yeah. As I indicated, I don't use them much for dividend info.
> I also tried out BarChart PV, and just as in your example (see its
> documentation, "example template"
> below.
>
> Documentation notes that it uses a Barchart Watchlist JSON file.
> but I'm not clear on what if anything I need to do regarding this JSON
> file, which I don't knowingly possess.
>
>
>
>
> **
>
>
>
> 49
>
> 48
>
> 47
>
> 53
>
> 1
>
> 9
>
>
>
>
>
> *DATE*
>
> *DIV*
>
> *YIELD*
>
> *RATE*
>
> *Payout*
>
> *Symbol*
>
> *Last*
>
>
>
> *AAV*
>
> 4/20/2018
>
> #NAME?
>
> #NAME?
>
> #NAME?
>
> #NAME?
>
> #NAME?
>
> #NAME?
>
> *GGN*
>
> 4/20/2018
>
> #NAME?
>
> #NAME?
>
> #NAME?
>
> #NAME?
>
> #NAME?
>
> #NAME?
>
>
>
>
>
> =smfGetBarchartPort
>
>
>
> Given that your example displays similarly, for all listed elements
> and all your selected symbols, I'm going to assume the issue is likely not
> at my end. Something changed from when the documentation was
> created. Needless to say, I have no idea whether the BC data are any
> better than Yahoo's. AAV is no longer issuing dividends, and GGN does
> not report a payout ratio from what I can tell, although it has a current
> yield of 11%.
>
My first suspicion would be that you are running an older version of the
add-in, before I added this function? Usually #NAME? either means your
add-in is in a different location from when I saved the workbook (which can
be fixed with the smfFixLinks macro from the add-in) or the function
doesn't exist (as in having an older version of the add-in).
> Just a heads up though that I'm not at all savvy with advanced
> Excel, macros or visual basic. I can usually follow instructions and
> examples well, but I'm not good making the leap from a sketch to results.
>
>
>
> Unless there's a simple tweak to the BC PV to get the #NAME? cells
> to display, I'm probably out of luck here.
>
>
>
> If anyone else is having success pulling down dividends basics in
> some fashion from public sources, please chime in! I think the payout
> ratio is important, otherwise the divvies you are collecting amount to the
> return of capital, not a real return.
>
Wouldn't payout ratio just be dividends per share divided by earnings per
share?
Sat Apr 21, 2018 12:18 am (PDT) . Posted by:
"Pieter van Leeuwen" pietertvl
First, thanks for the speedy reply!
My first suspicion would be that you are running an older version of the add-in, before I added this function?
Yup ... thought I'd copied over the Jan updated xla file, but it didn't take for some reason. And this function wasn't in the Nov version apparently.
Now Barchart is delivering! Too soon to evaluate the quality of the data, but its a good start!
Wouldn't payout ratio just be dividends per share divided by earnings per share?
Yup .... from other sources I'm getting there via that route. If I can import DPS and EPS, I can work with that.
Have a great weekend!
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Friday, April 20, 2018 9:25 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Dividend data?
Replies are below...
On Fri, Apr 20, 2018 at 7:48 PM, 'Pieter van Leeuwen' pieter_biz@
...
wrote:
Tried to give your detailed response a thorough review before replying, but I'm also constrained by the limited time I've had available since your reply.
You cannot array-enter RCHGetElementNumber() over a range to get multiple data items.
OK ... no array entry formatting for RCH Get Element. That was a reach..
If you're creating a table like that, I would suggest looking at the smfUpdateDownloadTable macro.
Did look briefly at the smf Update Download Table ... but there's no documentation for it (just placeholder) so I don't know how to point it to the elements that I need: Dividend, Annual Yield, and Payout ratio. Given the webpage pulls that this uses though, this doesn't seem like the ideal answer anyway.
Right now, the only documentation is links on the "FAQs and Tips" web page. And the sample workbook on the website, to a lesser degree.
Aren't the data items you want available with Yahoo Portfolio View or Barchart Portfolio View?
I can get some of that via Yahoo PV, but data are reported only for perhaps 25% of the symbols that are or were known divvie stocks. YF is built into my charting software from years ago and its API days, so when the API was disabled, I lost the ability to download fundamental data like this. EVEN SO, before that happened, YF was sketchy at best and like you said, probably out of date often too. Simply not very suitable, then or now.
Yeah. As I indicated, I don't use them much for dividend info.
I also tried out BarChart PV, and just as in your example (see its documentation, "example template") all the entries come up #NAME?. As below.
Documentation notes that it uses a Barchart Watchlist JSON file. but I'm not clear on what if anything I need to do regarding this JSON file, which I don't knowingly possess.
49
48
47
53
1
9
DATE
DIV
YIELD
RATE
Payout
Symbol
Last
AAV
4/20/2018
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
GGN
4/20/2018
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
=smfGetBarchartPortfolioView(A11:A12,C9:H9)
Given that your example displays similarly, for all listed elements and all your selected symbols, I'm going to assume the issue is likely not at my end. Something changed from when the documentation was created. Needless to say, I have no idea whether the BC data are any better than Yahoo's. AAV is no longer issuing dividends, and GGN does not report a payout ratio from what I can tell, although it has a current yield of 11%.
My first suspicion would be that you are running an older version of the add-in, before I added this function? Usually #NAME? either means your add-in is in a different location from when I saved the workbook (which can be fixed with the smfFixLinks macro from the add-in) or the function doesn't exist (as in having an older version of the add-in).
Just a heads up though that I'm not at all savvy with advanced Excel, macros or visual basic. I can usually follow instructions and examples well, but I'm not good making the leap from a sketch to results.
Unless there's a simple tweak to the BC PV to get the #NAME? cells to display, I'm probably out of luck here.
If anyone else is having success pulling down dividends basics in some fashion from public sources, please chime in! I think the payout ratio is important, otherwise the divvies you are collecting amount to the return of capital, not a real return.
Wouldn't payout ratio just be dividends per share divided by earnings per share?
My first suspicion would be that you are running an older version of the add-in, before I added this function?
Yup ... thought I'd copied over the Jan updated xla file, but it didn't take for some reason. And this function wasn't in the Nov version apparently.
Now Barchart is delivering! Too soon to evaluate the quality of the data, but its a good start!
Wouldn't payout ratio just be dividends per share divided by earnings per share?
Yup .... from other sources I'm getting there via that route. If I can import DPS and EPS, I can work with that.
Have a great weekend!
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Friday, April 20, 2018 9:25 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Dividend data?
Replies are below...
On Fri, Apr 20, 2018 at 7:48 PM, 'Pieter van Leeuwen' pieter_biz@
...
wrote:
Tried to give your detailed response a thorough review before replying, but I'm also constrained by the limited time I've had available since your reply.
You cannot array-enter RCHGetElementNumber
OK ... no array entry formatting for RCH Get Element. That was a reach..
If you're creating a table like that, I would suggest looking at the smfUpdateDownloadTa
Did look briefly at the smf Update Download Table ... but there's no documentation for it (just placeholder) so I don't know how to point it to the elements that I need: Dividend, Annual Yield, and Payout ratio. Given the webpage pulls that this uses though, this doesn't seem like the ideal answer anyway.
Right now, the only documentation is links on the "FAQs and Tips" web page. And the sample workbook on the website, to a lesser degree.
Aren't the data items you want available with Yahoo Portfolio View or Barchart Portfolio View?
I can get some of that via Yahoo PV, but data are reported only for perhaps 25% of the symbols that are or were known divvie stocks. YF is built into my charting software from years ago and its API days, so when the API was disabled, I lost the ability to download fundamental data like this. EVEN SO, before that happened, YF was sketchy at best and like you said, probably out of date often too. Simply not very suitable, then or now.
Yeah. As I indicated, I don't use them much for dividend info.
I also tried out BarChart PV, and just as in your example (see its documentation, "example template"
Documentation notes that it uses a Barchart Watchlist JSON file. but I'm not clear on what if anything I need to do regarding this JSON file, which I don't knowingly possess.
49
48
47
53
1
9
DATE
DIV
YIELD
RATE
Payout
Symbol
Last
AAV
4/20/2018
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
GGN
4/20/2018
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
=smfGetBarchartPort
Given that your example displays similarly, for all listed elements and all your selected symbols, I'm going to assume the issue is likely not at my end. Something changed from when the documentation was created. Needless to say, I have no idea whether the BC data are any better than Yahoo's. AAV is no longer issuing dividends, and GGN does not report a payout ratio from what I can tell, although it has a current yield of 11%.
My first suspicion would be that you are running an older version of the add-in, before I added this function? Usually #NAME? either means your add-in is in a different location from when I saved the workbook (which can be fixed with the smfFixLinks macro from the add-in) or the function doesn't exist (as in having an older version of the add-in).
Just a heads up though that I'm not at all savvy with advanced Excel, macros or visual basic. I can usually follow instructions and examples well, but I'm not good making the leap from a sketch to results.
Unless there's a simple tweak to the BC PV to get the #NAME? cells to display, I'm probably out of luck here.
If anyone else is having success pulling down dividends basics in some fashion from public sources, please chime in! I think the payout ratio is important, otherwise the divvies you are collecting amount to the return of capital, not a real return.
Wouldn't payout ratio just be dividends per share divided by earnings per share?
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar