Kamis, 20 April 2017

[smf_addin] Digest Number 3975

15 Messages

Digest #3975
1b
Re: Elements for identification by "Randy Harmelink" rharmelink
2a
2b
Re: Company's Description by "Randy Harmelink" rharmelink
3d
Re: Yahoo: RCHGetElementNumber by "Randy Harmelink" rharmelink
4a
Yahoo JSON files by "Randy Harmelink" rharmelink
4b

Messages

Thu Apr 20, 2017 5:07 am (PDT) . Posted by:

JCHyjun

Hi
Does anybody know how to collect info about company's country from Yahoo new pages (see e.g. http://finance.yahoo.com/quote/vod/profile?ltr=1 http://finance.yahoo.com/quote/vod/profile?ltr=1) ?
Thanks.
JCH

Thu Apr 20, 2017 10:23 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

That appears to be in the JSON feed used to build the page you cited:

=smfStrExtr(RCHGetWebData("
https://query2.finance.yahoo.com/v10/finance/quoteSummary/VOD?modules=assetProfile
","""country"":"""),":""","""")

On Thu, Apr 20, 2017 at 5:07 AM, jchyjun@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> Does anybody know how to collect info about company's country from Yahoo
> new pages (see e.g. http://finance.yahoo.com/quote/vod/profile?ltr=1) ?
>

Thu Apr 20, 2017 5:16 am (PDT) . Posted by:

mrpatmcginnis

Randy,
Regarding - long business summary:


How do I write the formula to pull the long description of the company into my spreadsheet by using "Ticker" ? I tried replacing the cell reference with ticker but no data comes into my spreadsheet.


http://finance.yahoo.com/quote/X/profile?p=X http://finance.yahoo.com/quote/X/profile?p=X takes me to the page for US Steel where I can see the description of the company, but I cannot figure out how to use "ticker" in place of the company symbol in the two places above, plus how do I pull that description out of the web page?


Is there a short description also available?


Thank you
Pat

Thu Apr 20, 2017 10:38 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

As long as "Ticker" is a valid named range, it should be usable as a cell
reference in the formula that was provided:

=smfstrExtr(RCHGetWebData("https://query1.finance.yahoo.com/
v10/finance/quoteSummary/"&C20&"?modules=assetProfile"),"
""longBusinessSummary"":""",""",""")

I don't know of a short description, unless you want to go with the
Industry group? Probably too short. Maybe from barchart.com?

=smfGetTagContent("https://www.barchart.com/stocks/quotes/
"&E7&"/profile","p",1,">Description")

On Thu, Apr 20, 2017 at 5:16 AM, mrpatmcginnis@
​...wrote:

> Regarding - long business summary:
>
> How do I write the formula to pull the long description of the company
> into my spreadsheet by using "Ticker" ? I tried replacing the cell
> reference with ticker but no data comes into my spreadsheet.
>
> http://finance.yahoo.com/quote/X/profile?p=X takes me to the page for US
> Steel where I can see the description of the company, but I cannot figure
> out how to use "ticker" in place of the company symbol in the two places
> above, plus how do I pull that description out of the web page?
>
> Is there a short description also available?
>
>

Thu Apr 20, 2017 10:47 am (PDT) . Posted by:

bizman789

Hi Randy - I know you haven't had time to look at the changes in Yahoo for mutual funds, but what would I need to do in order to make these work? Thank you-Dave

=RCHGetTableCell("http://finance.yahoo.com/q/pr?s="&$D7,1,"Annual Report Expense Ratio (net):")

=RCHGetTableCell("http://finance.yahoo.com/q/hl?s="&$D9,1,"Duration&quot;)

Thu Apr 20, 2017 10:50 am (PDT) . Posted by:

bizman789

And I am also not able to use. Sorry for bombarding you with these!

=RCHGetElementNumber($D7,4930)

Thu Apr 20, 2017 11:36 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Try:

=smfConvertData(smfStrExtr(smfWord(RCHGetWebData("
https://query1.finance.yahoo.com/v10/finance/quoteSummary/
"&F$3&"?modules=fundProfile"),2,"""annualReportExpenseRatio"":"),"""raw"":",","))

=smfConvertData(smfStrExtr(smfStrExtr(smfWord(RCHGetWebData("
https://query1.finance.yahoo.com/v10/finance/quoteSummary/
"&F$3&"?modules=topHoldings"),2,"""duration"":"),"~","}"),"""raw"":",","))

=smfConvertData(smfStrExtr(smfWord(RCHGetWebData("
https://query1.finance.yahoo.com/v10/finance/quoteSummary/
"&F$3&"?modules=fundPerformance"),2,"""lastBearMkt"":"),"""raw"":",","))

I'm working on a new function, for simple JSON extraction. Then, that last
extraction might look like:

=smfGetYahooJSONData("VFINX","fundPerformance","lastBearMkt")

...just passing ticker symbol, JSON module name, and field name. There are
two additional parameters that are optional, but defaulted to values for
the normal extraction processing. The initial formulas up above can't be
used for element definitions, because of the length of data being returned.
But the new function should be able to be used in element definitions.

On Thu, Apr 20, 2017 at 10:47 AM, bizman789@
​...wrote:

>
> Hi Randy - I know you haven't had time to look at the changes in Yahoo for
> mutual funds, but what would I need to do in order to make these work?
> Thank you-Dave
>
> =RCHGetTableCell("http://finance.yahoo.com/q/pr?s="&$D7,1,"Annual Report
> Expense Ratio (net):")
>
> =RCHGetTableCell("http://finance.yahoo.com/q/hl?s="&$D9,1,"Duration&quot;)
>

On Thu, Apr 20, 2017 at 10:50 AM, bizman789@
​...wrote:

>
> And I am also not able to use. Sorry for bombarding you with these!
>
> =RCHGetElementNumber($D7,4930)
>
>

Thu Apr 20, 2017 11:37 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

​For those of you that may need to easily explore the data in Yahoo's JSON
files, I'd strongly suggest getting the latest version of FireFox. After I
did the last update, it is now parsing the JSON file data. I get tabs for
both the parsed data and the raw data. The parsed data lists all of the
field names and their values, each on their own line in the display. It
makes it much easier to see what data is there.

I'm working on an add-in function to do simple extractions from a JSON
file, just passing a few parameters that typically change with each
extraction.​

An example of a JSON file:

https://query2.finance.yahoo.com/v10/finance/quoteSummary/SPY?modules=defaultKeyStatistics,assetProfile,fundProfile,topHoldings

Thu Apr 20, 2017 3:14 pm (PDT) . Posted by:

bizman789

Thanks Randy -

What am I doing wrong to get the Morningstar Return Rating?

=smfConvertData(smfstrExtr(smfWord(RCHGetWebData("https://query1.finance.yahoo.com/v10/finance/quoteSummary/"&D6&"?modules=fundPerformance&quot;),0,"""morningStarReturnRating&quot;":"),"""fmt"":",","))

Thu Apr 20, 2017 5:44 pm (PDT) . Posted by:

eburgos@comcast.net

I have a Excel 2016 spreadsheet with array that normally would get historical data for DOW and S&P500 for 3 different dates. My spreadsheet start date, beginning of the year, and 30 days ago.
I updated Stock Market Function file yesterday in attempt to fix problem.
SMF Version: Version 2.1.2017.04.17 (C:\SMF Add-In; Windows (32-bit) NT 10.00; 16.0; ; ; 1
Even after I updated the function file I was not able to get the historical data. I then manually updated the array to add the "s" to HTTP protocol. Still did not get the data.


This the array:
=RCHGetHTMLTable(CONCATENATE(
"https://finance.yahoo.com/q/hp?s=^DJI&a=",
MONTH(Details!N3)-1,"&b=",DAY(Details!N3),"&c=",YEAR(Details!N3),"&d=",
MONTH(Details!N3)-1,"&e=",DAY(Details!N3),"&f=",YEAR(Details!N3),"&g=d"),

"yfnc_datamodoutline1",-1,"",1)


Cell Details!N3 has this: 12/31/2015


Any help would be most appreciated.


Regards,
Ernie

Thu Apr 20, 2017 6:06 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Your search term no longer exists on the web page. Instead, try something
like this:

=RCHGetHTMLTable(CONCATENATE("https://finance.yahoo.com/q/hp?s=^DJI&a=",MONTH(N3)-1,"&b=",DAY(N3),"&c=",YEAR(N3),"&d=",MONTH(N3)-1,"&e=",DAY(N3),"&f=",YEAR(N3),"&g=d"),">Adj
Close")

On Thu, Apr 20, 2017 at 5:28 PM,
​Ernie@... wrote:

> I have a Excel 2016 spreadsheet with array that normally would get
> historical data for DOW and S&P500 for 3 different dates. My spreadsheet
> start date, beginning of the year, and 30 days ago.
>
> I updated Stock Market Function file yesterday in attempt to fix problem.
> SMF Version: Version 2.1.2017.04.17 (C:\SMF Add-In; Windows (32-bit) NT
> 10.00; 16.0; ; ; 1
>
> Even after I updated the function file I was not able to get the
> historical data. I then manually updated the array to add the "s" to HTTP
> protocol. Still did not get the data.
>
> This the array:
>
> =RCHGetHTMLTable(CONCATENATE(
>
> "https://finance.yahoo.com/q/hp?s=^DJI&a=",
>
> MONTH(Details!N3)-1,"&b=",DAY(Details!N3),"&c=",YEAR(Details!N3),"&d=",
>
> MONTH(Details!N3)-1,"&e=",DAY(Details!N3),"&f=",YEAR(Details!N3),"&g=d"),
>
> "yfnc_datamodoutline1",-1,"",1)
>
> Cell Details!N3 has this: 12/31/2015
>
> Any help would be most appreciated.
>
>

Thu Apr 20, 2017 6:46 pm (PDT) . Posted by:

eburgos@comcast.net

Thank You Randy.

The correction provides data that is now visible in my array. Only issue is that the data is for today and not for my historical dates. I will see if I can figure out what is wrong.


I really appreciate your help. Do you have favorite charity that I could contribute to in your name?


Best Regards,
Ernie

Thu Apr 20, 2017 6:03 pm (PDT) . Posted by:

yolande_tulasse

I updated to the last version of the add-in yesterday and everything was working perfectly.



Today though, RCHGetElementNumber elements are just returning "Error" (I use from 940ish to 990ish)


Thu Apr 20, 2017 6:47 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

That range of elements are from the Yahoo Key Statistics web page. I opened
up the:

RCHGetElementNumber-Template-Yahoo-Key-Statistics.xls

...file from the web site and all looks fine to me when using ticker "MMM".

What do you get with:

=RCHGetElementNumber("Version")
=RCHGetElementNumber("MMM",944)
=RCHGetTableCell("http://finance.yahoo.com/q/ks?s=MMM",1,"VALUATION
MEASURES","FORWARD P/E")

On Thu, Apr 20, 2017 at 6:03 PM, yolande_tulasse@
​...wrote:

> I updated to the last version of the add-in yesterday and everything was
> working perfectly.
>
> Today though, RCHGetElementNumber elements are just returning "Error" (I
> use from 940ish to 990ish)
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Diposting oleh EXCEL FEED LEARNING di 18.47

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)