Senin, 12 Februari 2018

[smf_addin] Digest Number 4304

15 Messages

Digest #4304
1a
1c
1e
1g
4a
Dividends by peter.oliver
4b
Re: Dividends by "Randy Harmelink" rharmelink
4c
Re: Dividends by peter.oliver

Messages

Tue Feb 6, 2018 4:59 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

So far, most of those that have been having problems have had older
operating systems and older versions of IE. I was starting to have problems
on a number of websites before I got my new computer last year, as they
stopped supported older versions of IE.

What do you get with:

=RCHGetElementNumber("Version")
=smfGetYahooPortfolioView("MMM","15",,0)
=RCHGetWebData("
https://query1.finance.yahoo.com/v7/finance/quote?symbols=MMM")

On Tue, Feb 6, 2018 at 5:14 PM, gerald747@
​...
wrote:

>
> I have the latest version: 2018.01.24.
>
> My array formula is =smfGetYahooPortfolioView(B23:B25,C21:D21,,1)
>
> The results look like this:
> 01 15 Ticker
> Symbol Last Price MMM -- -- SPY -- -- -- --
>
> I have used "Fix Links" and "Recalculate Selection" and "Recalculate
> Worksheet". Nothing seems to work. What am I doing wrong?
>
>
>

Thu Feb 8, 2018 2:50 pm (PST) . Posted by:

gerald747

Randy, thank you for your response.

=RCHGetElementNumber("Version") returns:
Stock Market Functions add-in, Version 2.1.2018.01.24 (D:\SMF Add-In; Windows(32-bit NT 5.01; 10.0; ; ; 1)
=smfGetYahooPortfolioView("MMM","15",,0) returns:
--
=RCHGetWebData("https://query1.finance.yahoo.com/v7/finance/quote?symbols=MMM") returns:
Error

Everything was OK up until about Jan. 12.
I put in the latest RCH version sometime aftere Jan. 12.
My system is (and has been for many years) Windows XT SP3, Excel 2002.

Mike

Thu Feb 8, 2018 4:37 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

If you get an "Error" returned on this web page:

https://query1.finance.yahoo.com/v7/finance/quote?symbols=MMM

....the function is not going to work. That's a basic form of the URL that
returns the data from Yahoo. What happens if you enter that URL into the
EXCEL Web Query dialog (alt+d+d+w)?

What do you get when you use this URL in the EXCEL Web Query dialog:

http://www.pinlady.net/PluginDetect/IE/

I get:
------------------------------
Internet Explorer (IE) Detection
*Internet Explorer Browser Detector*

Your browser is IE: true
Your IE browser version: 11.15
----------------------------------------------------------
True IE browser version (independent of userAgent, document mode, or
browser mode): 11.15.16299.0
IE browser version (derived from navigator.userAgent): 7
IE browser document mode: 7
------------------------------

My IE browser returns this:

------------------------------
Internet Explorer (IE) Detection
*Internet Explorer Browser Detector*

Your browser is IE: true
Your IE browser version: 11
----------------------------------------------------------
True IE browser version (independent of userAgent, document mode, or
browser mode): null
IE browser version (derived from navigator.userAgent): 11
IE browser document mode: 11
------------------------------

On Thu, Feb 8, 2018 at 3:50 PM, gerald747@
​...
wrote:

>
> Randy, thank you for your response.
>
> =RCHGetElementNumber("Version") returns:
> Stock Market Functions add-in, Version 2.1.2018.01.24 (D:\SMF Add-In;
> Windows(32-bit NT 5.01; 10.0; ; ; 1)
> =smfGetYahooPortfolioView("MMM","15",,0) returns:
> --
> =RCHGetWebData("https://query1.finance.yahoo.com/v7/
> finance/quote?symbols=MMM";) returns:
> Error
>
> Everything was OK up until about Jan. 12.
> I put in the latest RCH version sometime aftere Jan. 12.
> My system is (and has been for many years) Windows XT SP3, Excel 2002.
>
>

Fri Feb 9, 2018 11:54 am (PST) . Posted by:

gerald747

Randy,

When I enter
https://query1.finance.yahoo.com/v7/finance/quote?symbols=MMM into the EXCEL Web Query dialog, I get:
This program cannot display the webpage

When I enter
http://www.pinlady.net/PluginDetect/IE/ into the EXCEL Web Query dialog, I get:
Internet Explorer Browser Detector
Your browser is IE: true
Your IE browser version: 8
-----
True IE browser version (independent of userAgent, document mode, or browser mode): 8.0.6001.18702
IE browser version (derived from navigator.userAgent): 7
IE browser document mode: 7

When I enter
http://www.pinlady.net/PluginDetect/IE/ into Internet Explorer, I get:
Internet Explorer Browser Detector
Your browser is IE: true
Your IE browser version: 8
-----
True IE browser version (independent of userAgent, document mode, or browser mode): 8.0.6001.18702
IE browser version (derived from navigator.userAgent): 8
IE browser document mode: 8

As a reminder, everything was working fine until about mid-January. I have been using my operating system (WinXP SP3) and Excel (2002) and Internet Explorer (8) for years. I almost always use Firefox to browse. I upgraded Firefox to 52.5.3 ESR about two months ago.

Thanks for your help,
Mike

Fri Feb 9, 2018 1:40 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

If the EXCEL Web Query dialog can't access the web page, the add-in won't
be able to either. They both use the same Microsoft software to pull the
data from the web site. Does the web page work in your IE browser?

https://query1.finance.yahoo.com/v7/finance/quote?symbols=MMM

I'm wondering if Yahoo recently dropped support of older versions of IE? I
think I mentioned I was having issues with that on my old computer, before
I replaced it last May. I had several web sites that would no longer work
in IE on that old machine (it used Vista).

Hmmm. Can you access other Yahoo pages in your IE browser, or in the EXCEL
Web Query dialog? For example:

https://finance.yahoo.com/quotes/MMM,IBM/view/v1
https://finance.yahoo.com/quote/MMM

On Fri, Feb 9, 2018 at 6:39 AM, gerald747@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> When I enter
> https://query1.finance.yahoo.com/v7/finance/quote?symbols=MMM into the
> EXCEL Web Query dialog, I get:
> This program cannot display the webpage
>
> When I enter
> http://www.pinlady.net/PluginDetect/IE/ into the EXCEL Web Query dialog,
> I get:
> Internet Explorer Browser Detector
> Your browser is IE: true
> Your IE browser version: 8
> -----
> True IE browser version (independent of userAgent, document mode, or
> browser mode): 8.0.6001.18702
> IE browser version (derived from navigator.userAgent): 7
> IE browser document mode: 7
>
> When I enter
> http://www.pinlady.net/PluginDetect/IE/ into Internet Explorer, I get:
> Internet Explorer Browser Detector
> Your browser is IE: true
> Your IE browser version: 8
> -----
> True IE browser version (independent of userAgent, document mode, or
> browser mode): 8.0.6001.18702
> IE browser version (derived from navigator.userAgent): 8
> IE browser document mode: 8
>
> As a reminder, everything was working fine until about mid-January. I
> have been using my operating system (WinXP SP3) and Excel (2002) and
> Internet Explorer (8) for years. I almost always use Firefox to browse.. I
> upgraded Firefox to 52.5.3 ESR about two months ago.
>
>

Sat Feb 10, 2018 2:08 pm (PST) . Posted by:

gerald747

Randy,

https://query1.finance.yahoo.com/v7/finance/quote?symbols=MMM returns:
"Internet Explorer cannot display the webpage" in IE
bunch of code in Firefox
"This program cannot display the webpage" in EXCEL Web Query

https://finance.yahoo.com/quotes/MMM,IBM/view/v1 returns:
"Internet Explorer cannot display the webpage" in IE
Internet Explorer cannot display the webpage in IE
good webpage in Firefox
"This program cannot display the webpage" in EXCEL Web Query

https://finance.yahoo.com/quote/MMM returns:
"Internet Explorer cannot display the webpage" in IE
good webpage in Firefox
"This program cannot display the webpage" in EXCEL Web Query

Thanks for your help,
Mike

Sat Feb 10, 2018 2:59 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

Looks like Yahoo no longer supports older versions of IE, so you would need
to upgrade your IE to a more recent version.

Firefox is irrelevant in this situation, because the add-in uses the same
Microsoft Internet engines that are used by EXCEL and IE.

On Sat, Feb 10, 2018 at 3:08 PM, gerald747@
​...
wrote:

>
> https://query1.finance.yahoo.com/v7/finance/quote?symbols=MMM returns:
> "Internet Explorer cannot display the webpage" in IE
> bunch of code in Firefox
> "This program cannot display the webpage" in EXCEL Web Query
>
> https://finance.yahoo.com/quotes/MMM,IBM/view/v1 returns:
> "Internet Explorer cannot display the webpage" in IE
> Internet Explorer cannot display the webpage in IE
> good webpage in Firefox
> "This program cannot display the webpage" in EXCEL Web Query
>
> https://finance.yahoo.com/quote/MMM returns:
> "Internet Explorer cannot display the webpage" in IE
> good webpage in Firefox
> "This program cannot display the webpage" in EXCEL Web Query
>
> Thanks for your help,
> Mike
>

Tue Feb 6, 2018 6:05 pm (PST) . Posted by:

ghart21

Randy
Curiously a couple of hours later the problem disappeared
completely.
Sorry about the false alarm.

-----Original Message-----
From: "Randy Harmelink rharmelink@gmail.com [smf_addin]"
Sent: Feb 7, 2018 12:40 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Last Earnings Date - from Yahoo Finance



They are working here. But both of those come from Zacks, not
Yahoo. I just tried these:
=RCHGetElementNumber("AAPL",1290)
=RCHGetElementNumber("AAPL",1295)

....and got these results:
2/1/2018
11/2/2017

Temporary Zacks issue, maybe? You didn't say what you were
getting back (blanks, "Error", etc.). What do you get with:
=RCHGetElementNumber("Version")=RCHGetElementNumber("Definition",\
1290)
=SUBSTITUTE(smfWord(smfWord(smfStrExtr(RCHGetWebData("https://www\
..zacks.com/stock/research/AAPL/earnings-announcements
<https://www.zacks.com/stock/research/AAPL/earnings-announcements\
> ","document.obj_data = {"),": ",":"),3,"["),1,","),"""","")
=RCHGetWebData("https://www.zacks.com/stock/research/AAPL/earning\
s-announcements
<https://www.zacks.com/stock/research/AAPL/earnings-announcements\
> ","document.obj_data = {",150)


On Tue, Feb 6, 2018 at 5:07 PM, ghart23@earthlink.net
<mailto:ghart23@earthlink.net> [smf_addin]
<smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com> >
wrote:

In the ongoing Yahoo Finance "changes" as of this morning
RCHGetElementNumber($B2,1290) and RCHGetElementNumber($B2,1295),
the next earnings date and the last earnings date, no longer work
where $B2 would be the symbol such as AAPL Any suggestions?
Thanks so much.





Fri Feb 9, 2018 8:54 pm (PST) . Posted by:

joshi_mandar

I have a somewhat complicated stock and option portfolio tracker spreadsheet built that uses this add-in. Wondering if anyone has tried to use this add-in or any other method to create stock portfolio with MS Access database.

Sun Feb 11, 2018 6:50 pm (PST) . Posted by:

peter.oliver

Hi Randy,


Is there a way to calculate the total amount of dividends paid during a period of time in a single Excell cell like it was possible with this example concerning closing prices, please?

=AVERAGE(smfGetYahooHistory("MMM",,,,"c",0,,50,1))


...would get you the 50-day average of the adjusted close.


Using "v" as the period parameter returns a table with dates and corresponding dividends paid and the rows and cols parameters are irrelevant. So using:


=SUM(smfGetYahooHistory("MMM",start_date,end_date,"v",,0,,rows,cols)


...always returns the initial date regardless of the other parameters.


I would like to use just one cell for a SUM type of formula instead of individual tables of different sizes each one with its SUM, for each stock if possible.


Have a great day!
Peter



Sun Feb 11, 2018 7:10 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

Try something like:

=SUM(INDEX(smfGetYahooHistory("MMM","1/1/2016","12/31/2016","v",,0,,50,2),,2))

On Sun, Feb 11, 2018 at 7:50 PM, peter.oliver@
​...
wrote:

> Is there a way to calculate the total amount of dividends paid during a
> period of time in a single Excell cell like it was possible with this
> example concerning closing prices, please?
> =AVERAGE(smfGetYahooHistory("MMM",,,,"c",0,,50,1))
>
> ...would get you the 50-day average of the adjusted close.
>
> Using "v" as the period parameter returns a table with dates and
> corresponding dividends paid and the rows and cols parameters are
> irrelevant. So using:
>
> =SUM(smfGetYahooHistory("MMM",start_date,end_date,"v",,0,,rows,cols)
>
> ...always returns the initial date regardless of the other parameters.
>
> I would like to use just one cell for a SUM type of formula instead of
> individual tables of different sizes each one with its SUM, for each stock
> if possible.
>
>
>

Sun Feb 11, 2018 7:49 pm (PST) . Posted by:

peter.oliver

It worked great! Thanks a lot.

Sun Feb 11, 2018 8:24 pm (PST) . Posted by:

ajtalukdar

Hi Randy,


I am aware that Yahoo has changed their data display from HTML to JSon. Also aware that you were working on making the add-in work with JSon. Any updates in this regard? Will help tremendously if you can put together a template for fetching Yahoo QIS and QBS data.


Best,
Abhijit Talukdar

Sun Feb 11, 2018 10:41 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

Sorry, but the templates for Yahoo Financial statements are a low priority
right now. Partially because Yahoo has such a limited amount of quarterly
and annual periods.

Can you get what you need from GuruFocus?

On Sun, Feb 11, 2018 at 9:24 PM, ajtalukdar@
​...
wrote:

> I am aware that Yahoo has changed their data display from HTML to JSon..
> Also aware that you were working on making the add-in work with JSon. Any
> updates in this regard? Will help tremendously if you can put together a
> template for fetching Yahoo QIS and QBS data.
>
>
>

Mon Feb 12, 2018 11:12 am (PST) . Posted by:

rr76012

Hello Randy,


I have been using your "smfGetYahooPortfolioView - Example.xls"


but I am not sure if I am missing if you put the Yahoo - 5 Year Average Dividend Yield.


You have used this field on your previous spreadsheet.


If you would Pls consider putting this field on your future upgrades on your


"smfGetYahooPortfolioView - Example.xls".


Thank you.


I have placed a link and copy to the yahoo website.


Randy, again thank you.


rr76012




https://finance.yahoo.com/quote/EXC/key-statistics https://finance.yahoo.com/quote/EXC/key-statistics


Dividends & Splits Forward Annual Dividend Rate 4 1.38 Forward Annual Dividend Yield 4 3.74% Trailing Annual Dividend Rate 3 1.30 Trailing Annual Dividend Yield 3 3.52% 5 Year Average Dividend Yield 4 4.07



https://finance.yahoo.com/quote/EXC/key-statistics

For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Diposting oleh EXCEL FEED LEARNING di 11.12

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)