8 Messages
Digest #4229
Messages
Fri Nov 10, 2017 4:42 pm (PST) . Posted by:
eclipsme
Yes, the forward dividend seems to be what was needed. Thanks!
Fri Nov 10, 2017 4:48 pm (PST) . Posted by:
eadamy
Thank you for smfGetYahooPortfolioView which I am using with memory based arrays after studying your example worksheet to see what it does. You may have covered my questions previously but the conversations search in Y groups is ineffective.
Originally, my tickers were in a one dimensional array but it didn't work. When I traced execution to "Select Case VarType(pTickers)", I found it requires a string or array cells; however the end result is "aTickers"; which is a one dimensional array. I worked around this by converting my arrays to strings; however I wonder if the routine could check to see if it's being passed an array of symbols and work with it?
I had a lot of trouble converting the Unix date until I found your smfunix2date utility function. Conversion techniques I found with searches just didn't work. Now that it's working, I'm seeing a time of 9:00PM for the close. Does this mean the time needs to be adjusted for GMT basis?
I'm always looking for the next backup for the day Yahoo once again changes the ballgame. I tried substituting the Barcharts function for the Yahoo function and it blew up. It appears that the request codes are 3 digits and different. I know you have your hands full but it should would be great to be able to throw a switch to change the data source.
Earl Adamy
Originally, my tickers were in a one dimensional array but it didn't work. When I traced execution to "Select Case VarType(pTickers)
I had a lot of trouble converting the Unix date until I found your smfunix2date utility function. Conversion techniques I found with searches just didn't work. Now that it's working, I'm seeing a time of 9:00PM for the close. Does this mean the time needs to be adjusted for GMT basis?
I'm always looking for the next backup for the day Yahoo once again changes the ballgame. I tried substituting the Barcharts function for the Yahoo function and it blew up. It appears that the request codes are 3 digits and different. I know you have your hands full but it should would be great to be able to throw a switch to change the data source.
Earl Adamy
Fri Nov 10, 2017 5:29 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
On Fri, Nov 10, 2017 at 5:48 PM, earladamy@
...
wrote:
> Thank you for smfGetYahooPortfolioView which I am using with memory based
> arrays after studying your example worksheet to see what it does. You may
> have covered my questions previously but the conversations search in Y
> groups is ineffective.
>
> Originally, my tickers were in a one dimensional array but it didn't work.
> When I traced execution to "Select Case VarType(pTickers)", I found it
> requires a string or array cells; however the end result is "aTickers";
> which is a one dimensional array. I worked around this by converting my
> arrays to strings; however I wonder if the routine could check to see if
> it's being passed an array of symbols and work with it?
>
If a range is passed, it just appends them all into one long string, as a
comma-delimited list. That way, no matter which method is used, the
"aTickers"; processing is on a similar list of ticker symbols.
These two invocations of the function should work the same:
=smfGetYahooPortfolioView(B6:B7,"15")
=smfGetYahooPortfolioView(smfJoin(B6:B7,","),"15")
Or are you asking about VBA processing? If so, I would prefer it be
handled externally.
Maybe some time if I make the "ticker processing" a separate subroutine,
since so many functions have that same copied piece of code. Programming
involves a lot of copying. :)
I had a lot of trouble converting the Unix date until I found
> your smfunix2date utility function. Conversion techniques I found with
> searches just didn't work. Now that it's working, I'm seeing a time of
> 9:00PM for the close. Does this mean the time needs to be adjusted for GMT
> basis?
>
The 2017.11.08 version of the add-in should be doing both the UNIX
conversion the GMT adjustment. But the results are different field numbers.
I'm always looking for the next backup for the day Yahoo once again changes
> the ballgame. I tried substituting the Barcharts function for the Yahoo
> function and it blew up. It appears that the request codes are 3 digits and
> different. I know you have your hands full but it should would be great to
> be able to throw a switch to change the data source.
>
When I started writing smfGetBarchartPortfolioView(), I was wishing I had
made smfGetYahooPortfolioView() use 3-digit field numbers, so they had been
consistent. But when pulling numbers out of a range, it doesn't matter
whether the function uses 2-digit or 3-digit strings, since the function
formats each number as needed.
It's primarily just a matter of getting the field numbers aligned for any
data retrieval worksheet, which is a one-shot job. Just like converting
from RCHGetYahooQuotes() to smfGetYahooPortfolioView().
This is the problem when something is developed piecemeal. :(
...
wrote:
> Thank you for smfGetYahooPortfoli
> arrays after studying your example worksheet to see what it does. You may
> have covered my questions previously but the conversations search in Y
> groups is ineffective.
>
> Originally, my tickers were in a one dimensional array but it didn't work.
> When I traced execution to "Select Case VarType(pTickers)
> requires a string or array cells; however the end result is "aTickers"
> which is a one dimensional array. I worked around this by converting my
> arrays to strings; however I wonder if the routine could check to see if
> it's being passed an array of symbols and work with it?
>
If a range is passed, it just appends them all into one long string, as a
comma-delimited list. That way, no matter which method is used, the
"aTickers"
These two invocations of the function should work the same:
=smfGetYahooPortfol
=smfGetYahooPortfol
Or are you asking about VBA processing? If so, I would prefer it be
handled externally.
Maybe some time if I make the "ticker processing" a separate subroutine,
since so many functions have that same copied piece of code. Programming
involves a lot of copying. :)
I had a lot of trouble converting the Unix date until I found
> your smfunix2date utility function. Conversion techniques I found with
> searches just didn't work. Now that it's working, I'm seeing a time of
> 9:00PM for the close. Does this mean the time needs to be adjusted for GMT
> basis?
>
The 2017.11.08 version of the add-in should be doing both the UNIX
conversion the GMT adjustment. But the results are different field numbers.
I'm always looking for the next backup for the day Yahoo once again changes
> the ballgame. I tried substituting the Barcharts function for the Yahoo
> function and it blew up. It appears that the request codes are 3 digits and
> different. I know you have your hands full but it should would be great to
> be able to throw a switch to change the data source.
>
When I started writing smfGetBarchartPortf
made smfGetYahooPortfoli
consistent. But when pulling numbers out of a range, it doesn't matter
whether the function uses 2-digit or 3-digit strings, since the function
formats each number as needed.
It's primarily just a matter of getting the field numbers aligned for any
data retrieval worksheet, which is a one-shot job. Just like converting
from RCHGetYahooQuotes(
This is the problem when something is developed piecemeal. :(
Fri Nov 10, 2017 4:55 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
smfGetTagContent() cannot be array-entered over a range. There would be no
advantage to doing so. All of the data still needs to come from individual
web pages. You would need a new formula for each ticker/field combination,
and it would require a new web page to be retrieved for each ticker symbol.
FinViz used to allow CSV file downloads of their screening results (which
could be a list of ticker symbols), but that capability is now limited to
subscribers ($25/month).
You can do multiple ticker symbols at one time, but I'm not sure what the
limit is. For example:
https://finviz.com/quote.ashx?ta=1&p=d&t=MMM,IBM
I had started writing an smfGetFinvizPortfolioView() routine yesterday,
with 79 fields, but it was noticeably slower, even with only a few ticker
symbols and a dozen field requests for each. So I put it on the back
burner.
My "proof of concept" manual processing, which I did before starting the
VBA routine, looked like this:
Symbol Exchange Company Name Sector Industry Group Country Prev Close
Price Change
MMM MMM NYSE 3M Company Industrial Goods Diversified Machinery USA
228.39 227.45 -0.0041
IBM IBM NYSE International Business Machines Corporation Technology Information
Technology Services USA 150.3 149.16 -0.0076
The formula for "Prev Close" and "Price" look like:
=smfGetTagContent("https://finviz.com/quote.ashx?ta=1&p=d&t=
"&smfJoin($B$3:$B$102,","),"td",1,"?t="&$B3&"&",">"&K$2,,,1)
...where:
-- $B$3:$B$102 refers to the entire range of ticker symbols (i.e.
Yellow-shaded column).
-- $B3 refers to the ticker cell on the row in question
-- K$2 refers to the column header in question (i.e. "Prev Close" or
"Price")
I would suspect you could construct the table above for your 15 stocks and
10 to 15 data points. I've attached a copy of my "proof of concept"
workbook, without the VBA code for the started function.
On Fri, Nov 10, 2017 at 4:38 PM, Ron Spruell hashky@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Would you break down the finviz example?
>
> Can the tickers be array entered?
>
> My desire is to pull in 10 to 15 data points on 15 stocks. What would be
> a good way to do it. I am in the process of setting up portfolio view.
>
>
>
advantage to doing so. All of the data still needs to come from individual
web pages. You would need a new formula for each ticker/field combination,
and it would require a new web page to be retrieved for each ticker symbol.
FinViz used to allow CSV file downloads of their screening results (which
could be a list of ticker symbols), but that capability is now limited to
subscribers ($25/month).
You can do multiple ticker symbols at one time, but I'm not sure what the
limit is. For example:
https://finviz.com/quote.ashx?ta=1&p=d&t=MMM,IBM
I had started writing an smfGetFinvizPortfolioView() routine yesterday,
with 79 fields, but it was noticeably slower, even with only a few ticker
symbols and a dozen field requests for each. So I put it on the back
burner.
My "proof of concept" manual processing, which I did before starting the
VBA routine, looked like this:
Symbol Exchange Company Name Sector Industry Group Country Prev Close
Price Change
MMM MMM NYSE 3M Company Industrial Goods Diversified Machinery USA
228.39 227.45 -0.0041
IBM IBM NYSE International Business Machines Corporation Technology Information
Technology Services USA 150.3 149.16 -0.0076
The formula for "Prev Close" and "Price" look like:
=smfGetTagContent("https://finviz.com/quote.ashx?ta=1&p=d&t=
"&smfJoin($B$3:$B$102,","),"td",1,"?t="&$B3&"&",">"&K$2,,,1)
...where:
-- $B$3:$B$102 refers to the entire range of ticker symbols (i.e.
Yellow-shaded column).
-- $B3 refers to the ticker cell on the row in question
-- K$2 refers to the column header in question (i.e. "Prev Close" or
"Price")
I would suspect you could construct the table above for your 15 stocks and
10 to 15 data points. I've attached a copy of my "proof of concept"
workbook, without the VBA code for the started function.
On Fri, Nov 10, 2017 at 4:38 PM, Ron Spruell hashky@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Would you break down the finviz example?
>
> Can the tickers be array entered?
>
> My desire is to pull in 10 to 15 data points on 15 stocks. What would be
> a good way to do it. I am in the process of setting up portfolio view.
>
>
>
Fri Nov 10, 2017 4:58 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Because of the additional fields, I had started working on an
smfGetFinvizPortfolioView() function yesterday. For more info (and a sample
extraction workbook), see:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/33907
On Fri, Nov 10, 2017 at 5:01 PM, peterejkt@
...
wrote:
>
> A FinVIZPOrtfolioView would be useful as it has some fields that are
> useful that are currently not available from YahooPortfolioView
>
> ATR - for setting stop losses
> RSI - for BUY / SELL signals
>
>
>
smfGetFinvizPortfolioView() function yesterday. For more info (and a sample
extraction workbook), see:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/33907
On Fri, Nov 10, 2017 at 5:01 PM, peterejkt@
...
wrote:
>
> A FinVIZPOrtfolioView would be useful as it has some fields that are
> useful that are currently not available from YahooPortfolioView
>
> ATR - for setting stop losses
> RSI - for BUY / SELL signals
>
>
>
Fri Nov 10, 2017 5:05 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
I'm saying yes, but I'm not sure of the exact context for "that 10-year
data".
As a subscriber, this:
=smfGetCSVFile("
http://financials.morningstar.com/ajax/exportKR2CSV.html?t=MMM")
...currently returns me something like (only the first few lines shown):
Growth Profitability and Financial Ratios for 3M Co
Financials
2007-12 2008-12 2009-12 2010-12 2011-12 2012-12 2013-12 2014-12 2015-12
2016-12 TTM
Revenue USD Mil 24462 25269 23123 26662 29611 29904 30871 31821 30274 30109
30996
Gross Margin % 47.9 47.1 47.6 48.1 47 47.5 47.8 48.3 49.2 50 49.6
Operating Income USD Mil 6193 5218 4814 5918 6178 6483 6666 7135 6946 7223
7664
Operating Margin % 25.3 20.6 20.8 22.2 20.9 21.7 21.6 22.4 22.9 24 24.7
Net Income USD Mil 4096 3460 3193 4085 4283 4444 4659 4956 4833 5050 5490
Earnings Per Share USD 5.6 4.89 4.52 5.63 5.96 6.32 6.72 7.49 7.58 8.16 8.96
Dividends USD 1.92 2 2.04 2.1 2.2 2.36 2.54 3.42 4.1 4.44 4.63
I think if a non-subscriber tries that function, they'll only see five
years?
The above is just key ratios. There are similar CSV files for the financial
statements data.
On Fri, Nov 10, 2017 at 5:31 PM, thomas.strouse@
...
wrote:
>
> If I buy the premium package from Morningstar to gain access to their
> 10-year data, is it correct that I will be able to access that 10-year data
> from the *existing *SMF functions?
>
>
>
data".
As a subscriber, this:
=smfGetCSVFile("
http://financials.morningstar.com/ajax/exportKR2CSV.html?t=MMM")
...currently returns me something like (only the first few lines shown):
Growth Profitability and Financial Ratios for 3M Co
Financials
2007-12 2008-12 2009-12 2010-12 2011-12 2012-12 2013-12 2014-12 2015-12
2016-12 TTM
Revenue USD Mil 24462 25269 23123 26662 29611 29904 30871 31821 30274 30109
30996
Gross Margin % 47.9 47.1 47.6 48.1 47 47.5 47.8 48.3 49.2 50 49.6
Operating Income USD Mil 6193 5218 4814 5918 6178 6483 6666 7135 6946 7223
7664
Operating Margin % 25.3 20.6 20.8 22.2 20.9 21.7 21.6 22.4 22.9 24 24.7
Net Income USD Mil 4096 3460 3193 4085 4283 4444 4659 4956 4833 5050 5490
Earnings Per Share USD 5.6 4.89 4.52 5.63 5.96 6.32 6.72 7.49 7.58 8.16 8.96
Dividends USD 1.92 2 2.04 2.1 2.2 2.36 2.54 3.42 4.1 4.44 4.63
I think if a non-subscriber tries that function, they'll only see five
years?
The above is just key ratios. There are similar CSV files for the financial
statements data.
On Fri, Nov 10, 2017 at 5:31 PM, thomas.strouse@
...
wrote:
>
> If I buy the premium package from Morningstar to gain access to their
> 10-year data, is it correct that I will be able to access that 10-year data
> from the *existing *SMF functions?
>
>
>
Fri Nov 10, 2017 5:45 pm (PST) . Posted by:
cancerfixer
Aha. I knew I was doing something stupid. Details, details. It works fine with the smf version. Thanks!
Fri Nov 10, 2017 5:49 pm (PST) . Posted by:
davie_001
Randy,
It's amazing, during all the furor over the function disabling, that you can spend so much time helping. While I'm pretty familiar with Excel, macros are a complete unknown for me, and no matter what I've done to the URL callout, I can't get the concatenation right, so that the cell with FIG or MAW104 code gets referenced for the web address. Listing the two page calls with the embedded strings to be replaced by a Cell reference, and hope you can assist with building a working URL for it.
After the second section of the URL, the Column A absolute cell references are a lookup to the field contents, starting with Fixed Income, Canadian Equity, etc., as posted in my last message.
Thanx for any solutions you can offer:
=RCHGetTableCell("http://quote.morningstar.ca/QuickTakes/ETF/etf_Portfolionew.aspx?t=FIG®ion=CAN&culture=en-CA",1,$A$8,$A$9,$A$6)
=RCHGetTableCell("http://quote.morningstar.ca/QuickTakes/fund/PortfolioOverviewNew.aspx?t=0P0000714D®ion=CAN&culture=en-CA",1,$A$11,$A$7)
Fixed Income Canadian Equity U.S. Equity International Equity Other Cash Total
It's amazing, during all the furor over the function disabling, that you can spend so much time helping. While I'm pretty familiar with Excel, macros are a complete unknown for me, and no matter what I've done to the URL callout, I can't get the concatenation right, so that the cell with FIG or MAW104 code gets referenced for the web address. Listing the two page calls with the embedded strings to be replaced by a Cell reference, and hope you can assist with building a working URL for it.
After the second section of the URL, the Column A absolute cell references are a lookup to the field contents, starting with Fixed Income, Canadian Equity, etc., as posted in my last message.
Thanx for any solutions you can offer:
=RCHGetTableCell("http://quote.morningstar.ca/QuickTakes/ETF/etf_Portfolionew.aspx?t=FIG®ion=CAN&culture=en-CA",1,$A$8,$A$9,$A$6)
=RCHGetTableCell("http://quote.morningstar.ca/QuickTakes/fund/PortfolioOverviewNew.aspx?t=0P0000714D®ion=CAN&culture=en-CA",1,$A$
Fixed Income Canadian Equity U.S. Equity International Equity Other Cash Total
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar