15 Messages
Digest #4308
3b
Re: Is there automated way to GetYahooPortfolioView without having t by "Randy Harmelink" rharmelink
Messages
Sat Feb 24, 2018 1:14 pm (PST) . Posted by:
lexstar
Randy, I'm not sure what you're trying to say. Your message is blank?
Sat Feb 24, 2018 1:23 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
I replied after each of your questions, instead of top-posting..
On Sat, Feb 24, 2018 at 2:14 PM, lexstar@
...
wrote:
>
> Randy, I'm not sure what you're trying to say. Your message is blank?
>
On Sat, Feb 24, 2018 at 2:14 PM, lexstar@
...
wrote:
>
> Randy, I'm not sure what you're trying to say. Your message is blank?
>
Mon Feb 26, 2018 9:07 am (PST) . Posted by:
lexstar
If I set the dates to sometime in the past (ie.start date 1/1/2017 to end date 12/31/2017), for DAILY prices, why do I get OHL with the same price, but the C is different?
For example, FCNTX give....
Date Open High Low Close
12/29/2017 122.4700012 122.47 122.47 120.60
12/28/2017 123.1900024 123.19 123.19 121.31
12/27/2017 122.8600006 122.86 122.86 120.99
12/26/2017 122.6500015 122.65 122.65 120.78
12/22/2017 122.9199982 122.92 122.92 121.05
12/21/2017 123.0899963 123.09 123.09 121.21
But, if I set the End Date back to today's date, everything works fine.
Anyway to make this right?
For example, FCNTX give....
Date Open High Low Close
12/29/2017 122.4700012 122.47 122.47 120.60
12/28/2017 123.1900024 123.19 123.19 121.31
12/27/2017 122.8600006 122.86 122.86 120.99
12/26/2017 122.6500015 122.65 122.65 120.78
12/22/2017 122.9199982 122.92 122.92 121.05
12/21/2017 123.0899963 123.09 123.09 121.21
But, if I set the End Date back to today's date, everything works fine.
Anyway to make this right?
Mon Feb 26, 2018 11:12 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Parameters, please.
On Mon, Feb 26, 2018 at 10:05 AM, lexstar@
...
wrote:
>
> If I set the dates to sometime in the past (ie.start date 1/1/2017 to end
> date 12/31/2017), for DAILY prices, why do I get OHL with the same price,
> but the C is different?
>
> For example, FCNTX give....
>
> Date Open High Low Close
> 12/29/2017 122.4700012 122.47 122.47 120.60
> 12/28/2017 123.1900024 123.19 123.19 121.31
> 12/27/2017 122.8600006 122.86 122.86 120.99
> 12/26/2017 122.6500015 122.65 122.65 120.78
> 12/22/2017 122.9199982 122.92 122.92 121.05
> 12/21/2017 123.0899963 123.09 123.09 121.21
>
>
> But, if I set the End Date back to today's date, everything works fine.
>
> Anyway to make this right?
>
>
On Mon, Feb 26, 2018 at 10:05 AM, lexstar@
...
wrote:
>
> If I set the dates to sometime in the past (ie.start date 1/1/2017 to end
> date 12/31/2017), for DAILY prices, why do I get OHL with the same price,
> but the C is different?
>
> For example, FCNTX give....
>
> Date Open High Low Close
> 12/29/2017 122.4700012 122.47 122.47 120.60
> 12/28/2017 123.1900024 123.19 123.19 121.31
> 12/27/2017 122.8600006 122.86 122.86 120.99
> 12/26/2017 122.6500015 122.65 122.65 120.78
> 12/22/2017 122.9199982 122.92 122.92 121.05
> 12/21/2017 123.0899963 123.09 123.09 121.21
>
>
> But, if I set the End Date back to today's date, everything works fine.
>
> Anyway to make this right?
>
>
Sun Feb 25, 2018 9:08 am (PST) . Posted by:
rcstrauss
Hello Randy
For the last year or so I have been successfully getting data from cefconnect.com using your add-in. I am using Excel 2016 and the 2.1.2018.01.24 zip version of your addin
CEF_Connect is my defined name for http://www.cefconnect.com/Details/Summary.aspx?ticker= http://www.cefconnect.com/Details/Summary.aspx?ticker=
In the past, I have used RCHGetTableCell function to get NAV (for example): with ACP in cellE7
=RCHGetTableCell(CEF_Connect&$E7,2,"Current") --- gives the current nav for ACP
=RCHGetTableCell(CEF_Connect&$E7,3,"52 Wk Avg") -- gives 52 week average discount for ACP
Now both of the calls simply return Error. It is not a ticker specific issue, no ticker is returning anything but Error at this point.
Any suggestions?
Thanks
Rob
For the last year or so I have been successfully getting data from cefconnect.com using your add-in. I am using Excel 2016 and the 2.1.2018.01.24 zip version of your addin
CEF_Connect is my defined name for http://www.cefconnect.com/Details/Summary.aspx?ticker= http://www.cefconnect.com/Details/Summary.aspx?ticker=
In the past, I have used RCHGetTableCell function to get NAV (for example): with ACP in cellE7
=RCHGetTableCell(
=RCHGetTableCell(
Now both of the calls simply return Error. It is not a ticker specific issue, no ticker is returning anything but Error at this point.
Any suggestions?
Thanks
Rob
Sun Feb 25, 2018 9:56 am (PST) . Posted by:
"Randy Harmelink" rharmelink
When I use your URL in my browser:
http://www.cefconnect.com/Details/Summary.aspx?ticker=ACP
....I get redirected to:
https://www.cefconnect.com/fund/ACP
....so just changing the URL appears to fix the formulas.
On Sun, Feb 25, 2018 at 10:08 AM, rcstrauss@
...
wrote:
> For the last year or so I have been successfully getting data from
> cefconnect.com using your add-in. I am using Excel 2016 and the
> 2.1.2018.01.24 zip version of your addin
>
> CEF_Connect is my defined name for http://www.cefconnect.com/
> Details/Summary.aspx?ticker=
>
> In the past, I have used RCHGetTableCell function to get NAV (for
> example): with ACP in cellE7
>
> =RCHGetTableCell(CEF_Connect&$E7,2,"Current") --- gives the current nav
> for ACP
>
> =RCHGetTableCell(CEF_Connect&$E7
>
> ,3,"52 Wk Avg") -- gives 52 week average discount for ACP
>
> Now both of the calls simply return Error. It is not a ticker specific
> issue, no ticker is returning anything but Error at this point.
>
> Any suggestions?
>
http://www.cefconnect.com/Details/Summary.aspx?ticker=ACP
....I get redirected to:
https://www.cefconnect.com/fund/ACP
....so just changing the URL appears to fix the formulas.
On Sun, Feb 25, 2018 at 10:08 AM, rcstrauss@
...
wrote:
> For the last year or so I have been successfully getting data from
> cefconnect.com using your add-in. I am using Excel 2016 and the
> 2.1.2018.01.24 zip version of your addin
>
> CEF_Connect is my defined name for http://www.cefconnect.com/
> Details/Summary.
>
> In the past, I have used RCHGetTableCell function to get NAV (for
> example): with ACP in cellE7
>
> =RCHGetTableCell(
> for ACP
>
> =RCHGetTableCell(
>
> ,3,"52 Wk Avg") -- gives 52 week average discount for ACP
>
> Now both of the calls simply return Error. It is not a ticker specific
> issue, no ticker is returning anything but Error at this point.
>
> Any suggestions?
>
Sun Feb 25, 2018 3:22 pm (PST) . Posted by:
limolyte
Hello,
Is there an automated way to get an Yahoo Portfolio View on many stocks without having to select the range, enter the smfGetYahooPortfolioView info and then hit Control+Shift+Enter.
My macros build a sheet with stock tickers in column A, and can be expanded to add Field Number in a row. I just don't know how to automate setting the range and Control+Shift+Enter to use the function.
Thanks
Is there an automated way to get an Yahoo Portfolio View on many stocks without having to select the range, enter the smfGetYahooPortfoli
My macros build a sheet with stock tickers in column A, and can be expanded to add Field Number in a row. I just don't know how to automate setting the range and Control+Shift+
Thanks
Sun Feb 25, 2018 3:43 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
I'm not understanding your question. Your question is the equivalent of "is
there an automated way to get data into a cell without entering a formula"?
Do you mean UPDATING the data? If so, use the smfForceRecalculation macro.
Do you mean expanding the range, for new tickers or new field number? If
so, just make it larger than you need initially, like the template is set
up. Then you can add tickers or field numbers to the template without ever
having to re-enter the formula.
Do you mean having your macro enter the formula? VBA can enter
array-entered formulas:
https://www.ozgrid.com/forum/forum/help-forums/excel-general/5865-solved-vba-how-do-i-enter-an-array-formula-in-vba
If you're asking something else, I need more information?
On Sun, Feb 25, 2018 at 4:22 PM, garoupaz@
...
wrote:
>
> Is there an automated way to get an Yahoo Portfolio View on many stocks
> without having to select the range, enter the smfGetYahooPortfolioView info
> and then hit Control+Shift+Enter.
>
> My macros build a sheet with stock tickers in column A, and can be
> expanded to add Field Number in a row. I just don't know how to automate
> setting the range and Control+Shift+Enter to use the function.
>
>
there an automated way to get data into a cell without entering a formula"?
Do you mean UPDATING the data? If so, use the smfForceRecalculation macro.
Do you mean expanding the range, for new tickers or new field number? If
so, just make it larger than you need initially, like the template is set
up. Then you can add tickers or field numbers to the template without ever
having to re-enter the formula.
Do you mean having your macro enter the formula? VBA can enter
array-entered formulas:
https://www.ozgrid.com/forum/forum/help-forums/excel-general/5865-solved-vba-how-do-i-enter-an-array-formula-in-vba
If you're asking something else, I need more information?
On Sun, Feb 25, 2018 at 4:22 PM, garoupaz@
...
wrote:
>
> Is there an automated way to get an Yahoo Portfolio View on many stocks
> without having to select the range, enter the smfGetYahooPortfoli
> and then hit Control+Shift+
>
> My macros build a sheet with stock tickers in column A, and can be
> expanded to add Field Number in a row. I just don't know how to automate
> setting the range and Control+Shift+
>
>
Mon Feb 26, 2018 9:28 am (PST) . Posted by:
"Jose Jacob" pepecan47
When going to a particular stock page, the graphic Price vs. Fair Value. If I move the mouse through the graphic, I can see date and Fair Value for the last 10 years.
Can this data be accessible to the SMF in the form of historical Fair Value for a stock?
Regards, Jose L. Jacob
Can this data be accessible to the SMF in the form of historical Fair Value for a stock?
Regards, Jose L. Jacob
Mon Feb 26, 2018 11:15 am (PST) . Posted by:
"Randy Harmelink" rharmelink
What web page? I don't see that at all.
Are you using the new Morningstar page? It's looking like Morningstar will
no longer be a viable source of data. It's taking me 20 to 30 seconds to
render a web page (over various browsers) and they now have the data hidden
behind an API that requires credentials.
On Mon, Feb 26, 2018 at 10:28 AM, Jose Jacob pepecan47@
...
wrote:
>
> When going to a particular stock page, the graphic Price vs. Fair Value.
> If I move the mouse through the graphic, I can see date and Fair Value for
> the last 10 years.
>
> Can this data be accessible to the SMF in the form of historical Fair
> Value for a stock?
>
>
Are you using the new Morningstar page? It's looking like Morningstar will
no longer be a viable source of data. It's taking me 20 to 30 seconds to
render a web page (over various browsers) and they now have the data hidden
behind an API that requires credentials.
On Mon, Feb 26, 2018 at 10:28 AM, Jose Jacob pepecan47@
...
wrote:
>
> When going to a particular stock page, the graphic Price vs. Fair Value.
> If I move the mouse through the graphic, I can see date and Fair Value for
> the last 10 years.
>
> Can this data be accessible to the SMF in the form of historical Fair
> Value for a stock?
>
>
Mon Feb 26, 2018 11:23 am (PST) . Posted by:
"Jose Jacob" pepecan47
I think it is the new Morningstar format
MCD McDonald's Corp Stock Quote Price | Morningstar
|
|
|
| | |
|
|
|
| |
MCD McDonald's Corp Stock Quote Price | Morningstar
Morningstar real-time stock quotes, graphs, and independent analysis for MCD keep you informed. Stay up to date ... | |
|
|
Regards, Jose L. Jacob
On Monday, February 26, 2018 2:16 PM, "Randy Harmelink rharmelink@gmail.com [smf_addin]" <smf_addin@yahoogroups.com> wrote:
What web page? I don't see that at all.
Are you using the new Morningstar page? It's looking like Morningstar will no longer be a viable source of data. It's taking me 20 to 30 seconds to render a web page (over various browsers) and they now have the data hidden behind an API that requires credentials.
On Mon, Feb 26, 2018 at 10:28 AM, Jose Jacob pepecan47@... wrote:
When going to a particular stock page, the graphic Price vs. Fair Value. If I move the mouse through the graphic, I can see date and Fair Value for the last 10 years.
Can this data be accessible to the SMF in the form of historical Fair Value for a stock?
#yiv9488187791 #yiv9488187791 -- #yiv9488187791ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv9488187791 #yiv9488187791ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv9488187791 #yiv9488187791ygrp-mkp #yiv9488187791hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv9488187791 #yiv9488187791ygrp-mkp #yiv9488187791ads {margin-bottom:10px;}#yiv9488187791 #yiv9488187791ygrp-mkp .yiv9488187791ad {padding:0 0;}#yiv9488187791 #yiv9488187791ygrp-mkp .yiv9488187791ad p {margin:0;}#yiv9488187791 #yiv9488187791ygrp-mkp .yiv9488187791ad a {color:#0000ff;text-decoration:none;}#yiv9488187791 #yiv9488187791ygrp-sponsor #yiv9488187791ygrp-lc {font-family:Arial;}#yiv9488187791 #yiv9488187791ygrp-sponsor #yiv9488187791ygrp-lc #yiv9488187791hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv9488187791 #yiv9488187791ygrp-sponsor #yiv9488187791ygrp-lc .yiv9488187791ad {margin-bottom:10px;padding:0 0;}#yiv9488187791 #yiv9488187791actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv9488187791 #yiv9488187791activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv9488187791 #yiv9488187791activity span {font-weight:700;}#yiv9488187791 #yiv9488187791activity span:first-child {text-transform:uppercase;}#yiv9488187791 #yiv9488187791activity span a {color:#5085b6;text-decoration:none;}#yiv9488187791 #yiv9488187791activity span span {color:#ff7900;}#yiv9488187791 #yiv9488187791activity span .yiv9488187791underline {text-decoration:underline;}#yiv9488187791 .yiv9488187791attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv9488187791 .yiv9488187791attach div a {text-decoration:none;}#yiv9488187791 .yiv9488187791attach img {border:none;padding-right:5px;}#yiv9488187791 .yiv9488187791attach label {display:block;margin-bottom:5px;}#yiv9488187791 .yiv9488187791attach label a {text-decoration:none;}#yiv9488187791 blockquote {margin:0 0 0 4px;}#yiv9488187791 .yiv9488187791bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv9488187791 .yiv9488187791bold a {text-decoration:none;}#yiv9488187791 dd.yiv9488187791last p a {font-family:Verdana;font-weight:700;}#yiv9488187791 dd.yiv9488187791last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv9488187791 dd.yiv9488187791last p span.yiv9488187791yshortcuts {margin-right:0;}#yiv9488187791 div.yiv9488187791attach-table div div a {text-decoration:none;}#yiv9488187791 div.yiv9488187791attach-table {width:400px;}#yiv9488187791 div.yiv9488187791file-title a, #yiv9488187791 div.yiv9488187791file-title a:active, #yiv9488187791 div.yiv9488187791file-title a:hover, #yiv9488187791 div.yiv9488187791file-title a:visited {text-decoration:none;}#yiv9488187791 div.yiv9488187791photo-title a, #yiv9488187791 div.yiv9488187791photo-title a:active, #yiv9488187791 div.yiv9488187791photo-title a:hover, #yiv9488187791 div.yiv9488187791photo-title a:visited {text-decoration:none;}#yiv9488187791 div#yiv9488187791ygrp-mlmsg #yiv9488187791ygrp-msg p a span.yiv9488187791yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv9488187791 .yiv9488187791green {color:#628c2a;}#yiv9488187791 .yiv9488187791MsoNormal {margin:0 0 0 0;}#yiv9488187791 o {font-size:0;}#yiv9488187791 #yiv9488187791photos div {float:left;width:72px;}#yiv9488187791 #yiv9488187791photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv9488187791 #yiv9488187791photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv9488187791 #yiv9488187791reco-category {font-size:77%;}#yiv9488187791 #yiv9488187791reco-desc {font-size:77%;}#yiv9488187791 .yiv9488187791replbq {margin:4px;}#yiv9488187791 #yiv9488187791ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv9488187791 #yiv9488187791ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv9488187791 #yiv9488187791ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv9488187791 #yiv9488187791ygrp-mlmsg select, #yiv9488187791 input, #yiv9488187791 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv9488187791 #yiv9488187791ygrp-mlmsg pre, #yiv9488187791 code {font:115% monospace;}#yiv9488187791 #yiv9488187791ygrp-mlmsg * {line-height:1.22em;}#yiv9488187791 #yiv9488187791ygrp-mlmsg #yiv9488187791logo {padding-bottom:10px;}#yiv9488187791 #yiv9488187791ygrp-msg p a {font-family:Verdana;}#yiv9488187791 #yiv9488187791ygrp-msg p#yiv9488187791attach-count span {color:#1E66AE;font-weight:700;}#yiv9488187791 #yiv9488187791ygrp-reco #yiv9488187791reco-head {color:#ff7900;font-weight:700;}#yiv9488187791 #yiv9488187791ygrp-reco {margin-bottom:20px;padding:0px;}#yiv9488187791 #yiv9488187791ygrp-sponsor #yiv9488187791ov li a {font-size:130%;text-decoration:none;}#yiv9488187791 #yiv9488187791ygrp-sponsor #yiv9488187791ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv9488187791 #yiv9488187791ygrp-sponsor #yiv9488187791ov ul {margin:0;padding:0 0 0 8px;}#yiv9488187791 #yiv9488187791ygrp-text {font-family:Georgia;}#yiv9488187791 #yiv9488187791ygrp-text p {margin:0 0 1em 0;}#yiv9488187791 #yiv9488187791ygrp-text tt {font-size:120%;}#yiv9488187791 #yiv9488187791ygrp-vital ul li:last-child {border-right:none !important;}#yiv9488187791
MCD McDonald's Corp Stock Quote Price | Morningstar
|
|
|
| | |
|
|
|
| |
MCD McDonald's Corp Stock Quote Price | Morningstar
Morningstar real-time stock quotes, graphs, and independent analysis for MCD keep you informed. Stay up to date ... | |
|
|
Regards, Jose L. Jacob
On Monday, February 26, 2018 2:16 PM, "Randy Harmelink rharmelink@gmail.com [smf_addin]" <smf_addin@yahoogroups.com> wrote:
What web page? I don't see that at all.
Are you using the new Morningstar page? It's looking like Morningstar will no longer be a viable source of data. It's taking me 20 to 30 seconds to render a web page (over various browsers) and they now have the data hidden behind an API that requires credentials.
On Mon, Feb 26, 2018 at 10:28 AM, Jose Jacob pepecan47@... wrote:
When going to a particular stock page, the graphic Price vs. Fair Value. If I move the mouse through the graphic, I can see date and Fair Value for the last 10 years.
Can this data be accessible to the SMF in the form of historical Fair Value for a stock?
#yiv9488187791 #yiv9488187791 -- #yiv9488187791ygrp-
Mon Feb 26, 2018 12:04 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Found it. Unfortunately, like I said, no data is within the source code of
the web page and needs to be retrieved via an API that requires
credentials. That means to grab the data, the EXCEL IE object needs to
render the page first, and then the add-in can extract the data from the
rendered web page. My attempts show that today it is taking 40 to 50
seconds for the EXCEL IE object to render the web page. A bit longer than
my stand-alone browsers. But, this formula:
=RCHGetHTMLTable("http://www.morningstar.com/stocks/XNYS/MMM/quote.html
","Price/Fair",1,"",2,,,,2)
....is returning this for me:
2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 YTD
0.68 0.97 0.86 0.82 0.93 1.17 1.25 0.94 1.07 1.28 1.2
-29.39 47.22 6.93 -2.75 16.49 53.79 19.6 -5.83 21.49 34.44 1.28
7.65 18.77 -9.86 -4.33 0.23 20.65 6.75 -6.52 9.05 12.97 -1.54
....after nearly 50 seconds of the IE object churning away to render the web
page. It's that last parameter of "2" that tells it to render the web page.
However, it's an experimental parameter and is NOT reliable over all
versions of IE and EXCEL. It's the main reason I extract data from the
source code of web pages instead of using the IE object.
On Mon, Feb 26, 2018 at 12:23 PM, Jose Jacob pepecan47@
...
wrote:
>
> I think it is the new Morningstar format
>
> MCD McDonald's Corp Stock Quote Price | Morningstar
> <http://www.morningstar.com/stocks/xnys/mcd/quote.html>
>
the web page and needs to be retrieved via an API that requires
credentials. That means to grab the data, the EXCEL IE object needs to
render the page first, and then the add-in can extract the data from the
rendered web page. My attempts show that today it is taking 40 to 50
seconds for the EXCEL IE object to render the web page. A bit longer than
my stand-alone browsers. But, this formula:
=RCHGetHTMLTable("http://www.morningstar.com/stocks/XNYS/MMM/quote.html
","Price/Fair",1,"",2,,,,2)
....is returning this for me:
2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 YTD
0.68 0.97 0.86 0.82 0.93 1.17 1.25 0.94 1.07 1.28 1.2
-29.39 47.22 6.93 -2.75 16.49 53.79 19.6 -5.83 21.49 34.44 1.28
7.65 18.77 -9.86 -4.33 0.23 20.65 6.75 -6.52 9.05 12.97 -1.54
....after nearly 50 seconds of the IE object churning away to render the web
page. It's that last parameter of "2" that tells it to render the web page.
However, it's an experimental parameter and is NOT reliable over all
versions of IE and EXCEL. It's the main reason I extract data from the
source code of web pages instead of using the IE object.
On Mon, Feb 26, 2018 at 12:23 PM, Jose Jacob pepecan47@
...
wrote:
>
> I think it is the new Morningstar format
>
> MCD McDonald's Corp Stock Quote Price | Morningstar
> <http://www.morningstar.com/stocks/xnys/mcd/quote.html>
>
Mon Feb 26, 2018 12:48 pm (PST) . Posted by:
"Jose Jacob" pepecan47
Thank you Randy, the formula works for me, It asked me to put a cookie in my computer and it took about 30 seconds.
What I am looking for is probably a bit complex, but you always surprise me.
In the Price vs. Fair Value graphic, if you move the mouse, you will get a pop up bubble with Date, Fair Value, High, Low, Close and M*'s.
Can you retrieve that data with the SMF?
Regards, Jose L. Jacob
On Monday, February 26, 2018 3:05 PM, "Randy Harmelink rharmelink@gmail.com [smf_addin]" <smf_addin@yahoogroups.com> wrote:
Found it. Unfortunately, like I said, no data is within the source code of the web page and needs to be retrieved via an API that requires credentials. That means to grab the data, the EXCEL IE object needs to render the page first, and then the add-in can extract the data from the rendered web page. My attempts show that today it is taking 40 to 50 seconds for the EXCEL IE object to render the web page. A bit longer than my stand-alone browsers. But, this formula:
=RCHGetHTMLTable("http://www.morningstar.com/stocks/XNYS/MMM/quote.html","Price/Fair",1,"",2,,,,2)
....is returning this for me:
| 2008 | 2009 | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | YTD |
| 0.68 | 0.97 | 0.86 | 0.82 | 0.93 | 1.17 | 1.25 | 0.94 | 1.07 | 1.28 | 1.2 |
| -29.39 | 47.22 | 6.93 | -2.75 | 16.49 | 53.79 | 19..6 | -5.83 | 21.49 | 34.44 | 1.28 |
| 7.65 | 18.77 | -9.86 | -4.33 | 0.23 | 20.65 | 6.75 | -6.52 | 9.05 | 12.97 | -1.54 |
....after nearly 50 seconds of the IE object churning away to render the web page. It's that last parameter of "2" that tells it to render the web page. However, it's an experimental parameter and is NOT reliable over all versions of IE and EXCEL. It's the main reason I extract data from the source code of web pages instead of using the IE object.
On Mon, Feb 26, 2018 at 12:23 PM, Jose Jacob pepecan47@... wrote:
I think it is the new Morningstar format
MCD McDonald's Corp Stock Quote Price | Morningstar
#yiv3515331525 #yiv3515331525 -- #yiv3515331525ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv3515331525 #yiv3515331525ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv3515331525 #yiv3515331525ygrp-mkp #yiv3515331525hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv3515331525 #yiv3515331525ygrp-mkp #yiv3515331525ads {margin-bottom:10px;}#yiv3515331525 #yiv3515331525ygrp-mkp .yiv3515331525ad {padding:0 0;}#yiv3515331525 #yiv3515331525ygrp-mkp .yiv3515331525ad p {margin:0;}#yiv3515331525 #yiv3515331525ygrp-mkp .yiv3515331525ad a {color:#0000ff;text-decoration:none;}#yiv3515331525 #yiv3515331525ygrp-sponsor #yiv3515331525ygrp-lc {font-family:Arial;}#yiv3515331525 #yiv3515331525ygrp-sponsor #yiv3515331525ygrp-lc #yiv3515331525hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv3515331525 #yiv3515331525ygrp-sponsor #yiv3515331525ygrp-lc .yiv3515331525ad {margin-bottom:10px;padding:0 0;}#yiv3515331525 #yiv3515331525actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv3515331525 #yiv3515331525activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv3515331525 #yiv3515331525activity span {font-weight:700;}#yiv3515331525 #yiv3515331525activity span:first-child {text-transform:uppercase;}#yiv3515331525 #yiv3515331525activity span a {color:#5085b6;text-decoration:none;}#yiv3515331525 #yiv3515331525activity span span {color:#ff7900;}#yiv3515331525 #yiv3515331525activity span .yiv3515331525underline {text-decoration:underline;}#yiv3515331525 .yiv3515331525attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv3515331525 .yiv3515331525attach div a {text-decoration:none;}#yiv3515331525 .yiv3515331525attach img {border:none;padding-right:5px;}#yiv3515331525 .yiv3515331525attach label {display:block;margin-bottom:5px;}#yiv3515331525 .yiv3515331525attach label a {text-decoration:none;}#yiv3515331525 blockquote {margin:0 0 0 4px;}#yiv3515331525 .yiv3515331525bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv3515331525 .yiv3515331525bold a {text-decoration:none;}#yiv3515331525 dd.yiv3515331525last p a {font-family:Verdana;font-weight:700;}#yiv3515331525 dd.yiv3515331525last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv3515331525 dd.yiv3515331525last p span.yiv3515331525yshortcuts {margin-right:0;}#yiv3515331525 div.yiv3515331525attach-table div div a {text-decoration:none;}#yiv3515331525 div.yiv3515331525attach-table {width:400px;}#yiv3515331525 div.yiv3515331525file-title a, #yiv3515331525 div.yiv3515331525file-title a:active, #yiv3515331525 div.yiv3515331525file-title a:hover, #yiv3515331525 div.yiv3515331525file-title a:visited {text-decoration:none;}#yiv3515331525 div.yiv3515331525photo-title a, #yiv3515331525 div.yiv3515331525photo-title a:active, #yiv3515331525 div.yiv3515331525photo-title a:hover, #yiv3515331525 div.yiv3515331525photo-title a:visited {text-decoration:none;}#yiv3515331525 div#yiv3515331525ygrp-mlmsg #yiv3515331525ygrp-msg p a span.yiv3515331525yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv3515331525 .yiv3515331525green {color:#628c2a;}#yiv3515331525 .yiv3515331525MsoNormal {margin:0 0 0 0;}#yiv3515331525 o {font-size:0;}#yiv3515331525 #yiv3515331525photos div {float:left;width:72px;}#yiv3515331525 #yiv3515331525photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv3515331525 #yiv3515331525photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv3515331525 #yiv3515331525reco-category {font-size:77%;}#yiv3515331525 #yiv3515331525reco-desc {font-size:77%;}#yiv3515331525 .yiv3515331525replbq {margin:4px;}#yiv3515331525 #yiv3515331525ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv3515331525 #yiv3515331525ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv3515331525 #yiv3515331525ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv3515331525 #yiv3515331525ygrp-mlmsg select, #yiv3515331525 input, #yiv3515331525 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv3515331525 #yiv3515331525ygrp-mlmsg pre, #yiv3515331525 code {font:115% monospace;}#yiv3515331525 #yiv3515331525ygrp-mlmsg * {line-height:1..22em;}#yiv3515331525 #yiv3515331525ygrp-mlmsg #yiv3515331525logo {padding-bottom:10px;}#yiv3515331525 #yiv3515331525ygrp-msg p a {font-family:Verdana;}#yiv3515331525 #yiv3515331525ygrp-msg p#yiv3515331525attach-count span {color:#1E66AE;font-weight:700;}#yiv3515331525 #yiv3515331525ygrp-reco #yiv3515331525reco-head {color:#ff7900;font-weight:700;}#yiv3515331525 #yiv3515331525ygrp-reco {margin-bottom:20px;padding:0px;}#yiv3515331525 #yiv3515331525ygrp-sponsor #yiv3515331525ov li a {font-size:130%;text-decoration:none;}#yiv3515331525 #yiv3515331525ygrp-sponsor #yiv3515331525ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv3515331525 #yiv3515331525ygrp-sponsor #yiv3515331525ov ul {margin:0;padding:0 0 0 8px;}#yiv3515331525 #yiv3515331525ygrp-text {font-family:Georgia;}#yiv3515331525 #yiv3515331525ygrp-text p {margin:0 0 1em 0;}#yiv3515331525 #yiv3515331525ygrp-text tt {font-size:120%;}#yiv3515331525 #yiv3515331525ygrp-vital ul li:last-child {border-right:none !important;}#yiv3515331525
What I am looking for is probably a bit complex, but you always surprise me.
In the Price vs. Fair Value graphic, if you move the mouse, you will get a pop up bubble with Date, Fair Value, High, Low, Close and M*'s.
Can you retrieve that data with the SMF?
Regards, Jose L. Jacob
On Monday, February 26, 2018 3:05 PM, "Randy Harmelink rharmelink@gmail.com [smf_addin]" <smf_addin@yahoogroups.com> wrote:
Found it. Unfortunately, like I said, no data is within the source code of the web page and needs to be retrieved via an API that requires credentials. That means to grab the data, the EXCEL IE object needs to render the page first, and then the add-in can extract the data from the rendered web page. My attempts show that today it is taking 40 to 50 seconds for the EXCEL IE object to render the web page. A bit longer than my stand-alone browsers. But, this formula:
=RCHGetHTMLTable("http://www.morningstar.com/stocks/XNYS/MMM/quote.html","
....is returning this for me:
| 2008 | 2009 | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | YTD |
| 0.68 | 0.97 | 0.86 | 0.82 | 0.93 | 1.17 | 1.25 | 0.94 | 1.07 | 1.28 | 1.2 |
| -29.39 | 47.22 | 6.93 | -2.75 | 16.49 | 53.79 | 19..6 | -5.83 | 21.49 | 34.44 | 1.28 |
| 7.65 | 18.77 | -9.86 | -4.33 | 0.23 | 20.65 | 6.75 | -6.52 | 9.05 | 12.97 | -1.54 |
....after nearly 50 seconds of the IE object churning away to render the web page. It'
On Mon, Feb 26, 2018 at 12:23 PM, Jose Jacob pepecan47@... wrote:
I think it is the new Morningstar format
MCD McDonald'
#yiv3515331525 #yiv3515331525 -- #yiv3515331525ygrp-
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar