Jumat, 21 April 2017

[smf_addin] Digest Number 3977[3 Attachments]

15 Messages

Digest #3977
1a
1b
Re: SMFGETOPTIONQUOTES by "Randy Harmelink" rharmelink
2a
2b
Re: Yahoo JSON files by "Randy Harmelink" rharmelink
2c
2d
Re: Yahoo JSON files by "Randy Harmelink" rharmelink
2e
4a
Re: Yahoo: RCHGetElementNumber by "Randy Harmelink" rharmelink

Messages

Fri Apr 21, 2017 8:08 am (PDT) . Posted by:

nmalhotr

Anyone else having problems with this function. I just get Error in all the cells.


N

Fri Apr 21, 2017 10:00 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

This is working fine for me:

=smfGetOptionQuotes("MMM 4/21 2017 $190 Call","b")

Can you give an example of one that isn't working?

On Fri, Apr 21, 2017 at 8:08 AM, namit98@
​...wrote:

> Anyone else having problems with this function. I just get Error in all
> the cells.
>
>

Fri Apr 21, 2017 9:04 am (PDT) . Posted by:

norton1717

Randy,

Trying to pull the 12-b1 fee from the Yahoo JSON file.


In some cases there is a number or a zero and the formula works fine.
https://query1.finance.yahoo.com/v10/finance/quoteSummary/prwcx?modules=fundProfile https://query1.finance.yahoo.com/v10/finance/quoteSummary/prwcx?modules=fundProfile



While in others it is skipped or blank. In these cases it then picks up the next number (netExpRatio)
https://query1.finance.yahoo.com/v10/finance/quoteSummary/vwiax?modules=fundProfile https://query1.finance.yahoo.com/v10/finance/quoteSummary/vwiax?modules=fundProfile



Is there a way to prevent the formula from doing this?


=smfConvertData(smfStrExtr(smfWord(RCHGetWebData("https://query1.finance.yahoo.com/v10/finance/quoteSummary/"&F4&"?modules=fundProfile";),2,"""twelveBOne"":"),"""raw"":",","))



Thanks for all you do.







Fri Apr 21, 2017 10:15 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

That can be prevented. I ran into that when doing the smfGetYahooJSONData()
function, so it takes care of it. For the current extraction, you need to
add an smfStrExtr() function to limit the search item to the current
variable, by only keep what goes to the next ")". So:

=smfConvertData(smfStrExtr(smfStrExtr(smfWord(RCHGetWebData("
https://query1.finance.yahoo.com/v10/finance/quoteSummary/
"&D7&"?modules=fundProfile"),2,"""twelveBOne"":"),"~","}"),"""raw"":",","))

...returns nothing. The new function also looks for a "null" value, which
the above formula can't really adapt for.

=smfGetYahooJSONData(D7,"fundProfile","twelveBOne")

...returns "Not Found" when no value can be found.

Since you seem to be doing a lot of fields,would you like to try using the
new function?

On Fri, Apr 21, 2017 at 9:04 AM, rnorton@wrote:

>
> Trying to pull the 12-b1 fee from the Yahoo JSON file.
>
> In some cases there is a number or a zero and the formula works fine.
> https://query1.finance.yahoo.com/v10/finance/quoteSummary/
> prwcx?modules=fundProfile
>
> While in others it is skipped or blank. In these cases it then picks up
> the next number (netExpRatio)
> https://query1.finance.yahoo.com/v10/finance/quoteSummary/
> vwiax?modules=fundProfile
>
> Is there a way to prevent the formula from doing this?
>
> =smfConvertData(smfStrExtr(smfWord(RCHGetWebData("https:/
> /query1.finance.yahoo.com/v10/finance/quoteSummary/"&F4&"?
> modules=fundProfile"),2,"""twelveBOne"":"),"""raw"":",","))
>
> Thanks for all you do.
>

Fri Apr 21, 2017 11:05 am (PDT) . Posted by:

norton1717

I would love to give the new function a try.

Fri Apr 21, 2017 11:28 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

I don't know how familiar you are with VBA -- the .bas code is attached.
You can just import it into the VBA module. It's actually a very small
routine.

I've also attached a workbook with some of the fields I've already done.

You might want to create a "test" function that gets all of it's values
from worksheet cells. Then, it's easy to change them and see what happens
to the extraction.

Once I have that worksheet populated, creating element definitions should
be relatively easy. For example:

19905;Test2;Mean Target
Price;=smfGetYahooJSONData("~~~~~","financialData","targetMeanPrice")

On Fri, Apr 21, 2017 at 11:05 AM, rnorton@
​...​
wrote:

>
> I would love to give the new function a try.
>
>
Attachment(s) from Randy Harmelink
2 of 2 File(s)

Fri Apr 21, 2017 1:05 pm (PDT) . Posted by:

norton1717

I will take a look and report back

Fri Apr 21, 2017 9:37 am (PDT) . Posted by:

eburgos@comcast.net

Hi,

Tried the example above but unable to save the array due to syntax error.


I sent donation to ACLU ($25). Don't know your email otherwise I would have added a confirming email.


Regards,
Ernie








Fri Apr 21, 2017 11:16 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Thanks for the donation to charity.

As far as RCHGetHTMLTable(), I just cut and pasted the formula from the
message in the Yahoo group and it worked fine for me. My test is attached.
Does it work for you?

On Fri, Apr 21, 2017 at 9:37 AM,
​Ernie@... wrote:

>
> Tried the example above but unable to save the array due to syntax error.
>
> I sent donation to ACLU ($25). Don't know your email otherwise I would
> have added a confirming email.
>
>
Attachment(s) from Randy Harmelink
1 of 1 File(s)

Fri Apr 21, 2017 12:02 pm (PDT) . Posted by:

bungyman2000

Randy,

Thanks for the sample file. Do you have one that will enable you to modify the symbol, start date and end date parameters without having to modify the array? Basically, as in the old file that no longer works...


Thanks


Bungy

Fri Apr 21, 2017 12:51 pm (PDT) . Posted by:

eburgos@comcast.net

Hi,

Thanks very much for your help. Finally got it to work. I set up my 6 arrays next to each other and opened up the size of each array 8 columns by 35 rows. Originally my 6 arrays were setup stacked horizontally with only 6 columns by 2 rows each.




Best regards,
Ernie

Fri Apr 21, 2017 12:58 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

I just tried this old file from the web site, and it works for me, even for
^DJI:

RCHGetYahooHistory-Example-By-Parameters.xls

Which one is not working for you?

On Fri, Apr 21, 2017 at 12:02 PM, bungyman2000@
​...wrote:

>
> Thanks for the sample file. Do you have one that will enable you to
> modify the symbol, start date and end date parameters without having to
> modify the array? Basically, as in the old file that no longer works...
>
>

Fri Apr 21, 2017 1:03 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

From what you mentioned earlier, wouldn't smfPricesByDates() do what you
need more easily? Or am I misunderstanding why you are getting the six
arrays? For example, current, prior month-end, and prior year-end can be
achieved with:

=smfPricesByDates("^DJI","4/20/2017","03/31/2017","12/31/2016")

On Fri, Apr 21, 2017 at 12:51 PM,
​Ernie@... wrote:

>
> Thanks very much for your help. Finally got it to work. I set up my 6
> arrays next to each other and opened up the size of each array 8 columns by
> 35 rows. Originally my 6 arrays were setup stacked horizontally with only 6
> columns by 2 rows each.
>
>

Fri Apr 21, 2017 9:57 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

But that's not the normal web site, so it's probably just a matter of time
before it's obsoleted as well.

On Fri, Apr 21, 2017 at 4:54 AM, halstian1@
​...wrote:

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

Fri Apr 21, 2017 12:54 pm (PDT) . Posted by:

halstian1

Hi Randy

Would it be possible to provide a work around even for this site? I'll take as much time as I can get


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

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)