Jumat, 21 April 2017

[smf_addin] Digest Number 3976

15 Messages

Digest #3976
1a
Re: Yahoo JSON files by "Randy Harmelink" rharmelink
1b
1c
Re: Yahoo JSON files by "Randy Harmelink" rharmelink
1d
Re: Yahoo JSON files by "Randy Harmelink" rharmelink
1e
1f
4a
Automatically build data sheets by vlotchek@gmail.com
4b
Re: Automatically build data sheets by "Randy Harmelink" rharmelink
5b
Re: Yahoo: RCHGetElementNumber by "Randy Harmelink" rharmelink

Messages

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

"Randy Harmelink" rharmelink

You need to get the 2nd "word" (1st word is everything prior to the first
occurrence, so 2nd word is the first occurrence), and in this situation,
you want the "raw" value. So:

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

You could get the formatted value with (which requires a slightly different
string extraction because the data is enclosed within quotes):

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

With the new function, it will simply be:

=smfGetYahooJSONData(D6,"fundPerformance","morningStarReturnRating")

...which defaults to the first "raw" value of that field name within that
module.

On Thu, Apr 20, 2017 at 3:14 PM, bizman789@
​...wrote:

>
> 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"),0,"""morningStarReturnRating"":"),"""fmt"":",","))
>
>

Thu Apr 20, 2017 8:24 pm (PDT) . Posted by:

sirons1962

Hi Randy,

So what do you use to see this parsing? I have the latest version of FireFox.

Thanks.

Thu Apr 20, 2017 11:17 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

I just enter the URL and it comes up in FireFox 53.0 (32 bit). I used to
just see a raw text file, but now there is the tab that has all the
variables parsed out:

[image: Inline image 1]

On Thu, Apr 20, 2017 at 8:24 PM, sirons1962@
​...wrote:

>
> So what do you use to see this parsing? I have the latest version of
> FireFox.
>

Thu Apr 20, 2017 11:24 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

I see the inline image didn't show up on the Yahoo group, so I uploaded it
to the SMF website:

http://ogres-crypt.com/SMF/Misc/Print-Screen-FIreFox-JSON-Parsing.png

On Thu, Apr 20, 2017 at 8:24 PM, sirons1962@
​...wrote:

>
> So what do you use to see this parsing? I have the latest version of
> FireFox.
>
>

Fri Apr 21, 2017 6:23 am (PDT) . Posted by:

bizman789

Thank you! And it looks like the new JSON function will make it much easier!!!

Thu Apr 20, 2017 8:01 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

LOL.

I noticed the incorrect dates just after I sent the message, so I had some
dinner and then started working on it. The problem is that the URL for the
web page has changed. It now uses UNIX dates for the starting and ending
dates and uses different parameters. Try something like:

=RCHGetHTMLTable("https://finance.yahoo.com/quote/^DJI/history?period1="&86400*(EOMONTH(N3,-1)-DATE(1970,1,1))&"&period2="&86400*(N3+7/24-DATE(1970,1,1))&"&interval=1d",">Adj
Close")

Luckily, the URL only changes for the web page, and not the CSV file.

With politics the way they are these days, I've been favoring the ACLU:

https://www.charitynavigator.org/index.cfm?bay=search.summary&orgid=3247

On Thu, Apr 20, 2017 at 6:46 PM,
​Ernie@... wrote:

>
> 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?
>
>
>

Thu Apr 20, 2017 11:51 pm (PDT) . Posted by:

yolande_tulasse

Hi Randy,


Here is what I get:

=RCHGetElementNumber("MMM",944)
=> Stock Market Functions add-in, Version 2.1.2017.04.17 (C:\SMF Add-In; Windows (64-bit) NT :.00; 15.0; ; Local; 1)

=RCHGetElementNumber("MMM",944)
=> Error

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


The strange thing is that everything was working fine yesterday after I installed the new version of the add-in

Fri Apr 21, 2017 1:38 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

What happens if you use that link in your browser:

http://finance.yahoo.com/q/ks?s=MMM

I get rerouted to something like:

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

What do you get with:

=RCHGetTableCell("http://finance.yahoo.com/q/ks?s=MMM",0,">FORWARD P/E")
=RCHGetWebData("http://finance.yahoo.com/q/ks?s=MMM","Forward",30)
=RCHGetWebData("http://finance.yahoo.com/q/ks?s=MMM","forward",30)

​=RCHGetTableCell("https://finance.yahoo.com/quote/MMM/key-statistics",0,">FORWARD
P/E")​
=RCHGetTableCell("https://finance.yahoo.com/quote/MMM/key-statistics",1,">FORWARD
P/E")

On Thu, Apr 20, 2017 at 11:51 PM, yolande_tulasse@
​...wrote:

>
> Here is what I get:
>
> =RCHGetElementNumber("MMM",944)
> => Stock Market Functions add-in, Version 2.1.2017.04.17 (C:\SMF Add-In;
> Windows (64-bit) NT :.00; 15.0; ; Local; 1)
>
> =RCHGetElementNumber("MMM",944)
> => Error
>
> =
> ​​
> RCHGetTableCell("http://finance.yahoo.com/q/ks?s=MMM",1,"VALUATION
> MEASURES","FORWARD P/E")
> => Error
>
> The strange thing is that everything was working fine yesterday after I
> installed the new version of the add-in
>

Fri Apr 21, 2017 2:39 am (PDT) . Posted by:

yolande_tulasse

Hi Randy,

In "smf-elements-2.txt" I replaced all the http by https and everything is now working like a charm.

Thanks a lot for pointing me in the right direction

Fri Apr 21, 2017 1:19 am (PDT) . Posted by:

vlotchek@gmail.com

Hi Randy,



I would like to build data sheets, pulling out from the web balance sheets, income statements, and cash flow.
I looked into youtube, but my excel wants to pull the data for some reason.


do you know where can I find a good guide who can help me build data sheet when I put a ticker name and push the button it will download all the information from the web automatically?


Thanks
Koby



Fri Apr 21, 2017 1:55 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Take a look at some of the templates on the add-in web site. There are some
examples. In particular, check out AdvFn (in
http://ogres-crypt.com/SMF/Templates) and GuruFocus (in
http://ogres-crypt.com/SMF/Works-In-Progress).

By far, the quickest is just to use one or more of the MorningStar CSV
files that are available:

=smfGetCSVFile("
http://financials.morningstar.com/ajax/exportKR2CSV.html?t=MMM")

=smfGetCSVFile("
http://financials.morningstar.com/ajax/ReportProcess4CSV.html?t=MMM&reportType=is&period=12&dataType=A&order=asc&columnYear=10&rounding=3&denominatorView=raw
")

See the "Tips and FAQs" web page to see how the parameters can be varied on
these...

On Fri, Apr 21, 2017 at 1:16 AM, vlotchek@
​...wrote:

> I would like to build data sheets, pulling out from the web balance
> sheets, income statements, and cash flow.
>
> I looked into youtube, but my excel wants to pull the data for some reason.
>
> do you know where can I find a good guide who can help me build data sheet
> when I put a ticker name and push the button it will download all the
> information from the web automatically?
>
>

Fri Apr 21, 2017 2:30 am (PDT) . Posted by:

halstian1

Hi Randy

I've read the posts on the RCHGetElementNumber issue but cant seem to adapt the code for the elements I need. Could you please provide workarounds for these elements with the proper syntax?


13842;YahooMS;Market Stats -- NASDAQ -- Advancing Issues;=smfConvertData(smfstrExtr(smfGetTagContent("http://finance.yahoo.com/stock-center","td",3,">Advancers",">Advancing Issues"),"~","(")) 13843;YahooMS;Market Stats -- NASDAQ -- Declining Issues;=smfConvertData(smfstrExtr(smfGetTagContent("http://finance.yahoo.com/stock-center","td",3,">Advancers",">Declining Issues"),"~","(")) 13848;YahooMS;Market Stats -- NASDAQ -- Up Volume;=smfConvertData(smfstrExtr(smfGetTagContent("http://finance.yahoo.com/stock-center","td",3,">Advancers",">Up Volume"),"~","(")) 13849;YahooMS;Market Stats -- NASDAQ -- Down Volume;=smfConvertData(smfstrExtr(smfGetTagContent("http://finance.yahoo.com/stock-center","td",3,">Advancers",">Down Volume"),"~","("))

Thanks

Fri Apr 21, 2017 2:52 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

I can't find that information on Yahoo any more. Can you? If not, I'll
probably obsolete them.

A workaround would be to use the Barchart versions -- see the Templates
area on the add-in web site.

On Fri, Apr 21, 2017 at 1:59 AM, halstian1@
​...wrote:

>
> I've read the posts on the RCHGetElementNumber issue but cant seem to
> adapt the code for the elements I need. Could you please provide
> workarounds for these elements with the proper syntax?
>
> 13842;YahooMS;Market Stats -- NASDAQ -- Advancing Issues;=smfConvertData(
> smfstrExtr(smfGetTagContent("http://finance.yahoo.com/stock-center
> ","td",3,">Advancers",">Advancing Issues"),"~","("))
> 13843;YahooMS;Market Stats -- NASDAQ -- Declining Issues;=smfConvertData(
> smfstrExtr(smfGetTagContent("http://finance.yahoo.com/stock-center
> ","td",3,">Advancers",">Declining Issues"),"~","("))
> 13848;YahooMS;Market Stats -- NASDAQ -- Up Volume;=smfConvertData(
> smfstrExtr(smfGetTagContent("http://finance.yahoo.com/stock-center
> ","td",3,">Advancers",">Up Volume"),"~","("))
> 13849;YahooMS;Market Stats -- NASDAQ -- Down Volume;=smfConvertData(
> smfstrExtr(smfGetTagContent("http://finance.yahoo.com/stock-center
> ","td",3,">Advancers",">Down Volume"),"~","("))
>
>
>

Fri Apr 21, 2017 4:54 am (PDT) . Posted by:

halstian1

Hi Randy

I've found it here http://c.finance.a1.b.yahoo.com/advances http://c.finance.a1.b.yahoo.com/advances



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

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)