Senin, 24 April 2017

[smf_addin] Digest Number 3980

15 Messages

Digest #3980
2b
Re: =RCHGetYahooHistory is not working by "Randy Harmelink" rharmelink
4b
Re: Yahoo: RCHGetElementNumber by "Randy Harmelink" rharmelink
7b
Re: RCHGetTableCell funtion not working by "Randy Harmelink" rharmelink

Messages

Sat Apr 22, 2017 8:53 pm (PDT) . Posted by:

wyerud

RCHGetElementNumbers 5064, 5093, 5079, 5061 don't work.



Any solutions?


Sat Apr 22, 2017 9:59 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Yahoo moved all the fund data to JSON feeds and is building the web pages
dynamically, so it's not easy to grab. Working on it, but it will take some
time. Especially since mutual funds are a low priority for me.

Several messages have already discussed the extractions of different data
items. According to my crib notes:

5064:

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

5093:

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

5079:

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

5061:

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

On Sat, Apr 22, 2017 at 8:53 PM, russelking@
​...wrote:

> RCHGetElementNumbers 5064, 5093, 5079, 5061 don't work.
>
> Any solutions?
>
>

Sun Apr 23, 2017 7:21 am (PDT) . Posted by:

wyerud

Thank you, Thank you.

You are amazingly generous with you time.





Mon Apr 24, 2017 1:14 am (PDT) . Posted by:

chinathunder

I'm having the same problem.....=RCHGetYahooHistory(B21,2017,3,31,2017,3,31,,"A",0)



just reinstalled the add-in Stock Market Functions add-in, Version 2.1.2014.01.30 (C:\Program Files\SMF Add-In; ; 1)

Mon Apr 24, 2017 1:16 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Your version is not the most current one available. You need the most
current version to get the fix for RCHGetYahooHistory(). Did you install
the new version in the wrong folder? Or not exit EXCEL when you updated the
files?

On Mon, Apr 24, 2017 at 12:04 AM, rlarryfoley@
​...wrote:

>
> I'm having the same problem.....=RCHGetYahooHistory(B21,2017,3,
> 31,2017,3,31,,"A",0)
>
> just reinstalled the add-in
> Stock Market Functions add-in, Version 2.1.2014.01.30 (C:\Program
> Files\SMF Add-In; ; 1)
>
>

Mon Apr 24, 2017 8:34 am (PDT) . Posted by:

"Matthew Crowe" mpcrowe2

Hi Randy, thanks for your outstanding work, we appreciate it!
I wanted to let you know that since the Yahoo change a section of code in your multisymbol template appears to be broken.  I discovered this because I had adopted some of the code in one of my applications.  Using the most recent version of RCH_Stock_Market_Functions with Excel 2007 restarted, and the most recent version of SMF-Template-Yahoo-Historical-Quotes-MultiSymbol from your web share, the multi-symbol macro appears to error out at the section below.  I wanted to let you know before exploring further in the event that you or another member had already resolved.  My apologies in advance if I've missed something simple here.  Thank you.

1. Open SMF-Template-Yahoo-Historical-Quotes-MultiSymbol2. Click "GetMultiQuotes"3. Runtime error 1004 generated at:
' blow it out to the starting column / row specified resized to accomodate the array
 
  RngTargetOutput.Resize(lFinal_Rows, UBound(vQuotes, 2)).Value2 = vQuotes ' Moves the block to the starting location
  ' change the formats - ASSUME DATE in first column
  With Range(RngTargetOutput.Cells(1, 1), RngTargetOutput.Cells(lFinal_Rows, 1))
    .NumberFormat = "yyyy-mm-dd"
  End With

Mon Apr 24, 2017 11:09 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

That's not one of my workbooks. It's one uploaded by another member. I
would just use multiple RCHGetYahooHistory() functions in the worksheet.

On Mon, Apr 24, 2017 at 7:50 AM, Matthew Crowe <mpcrowe2@yahoo.com> wrote:

> Hi Randy, thanks for your outstanding work, we appreciate it!
>
> I wanted to let you know that since the Yahoo change a section of code in
> your multisymbol template appears to be broken. I discovered this because
> I had adopted some of the code in one of my applications. Using the most
> recent version of RCH_Stock_Market_Functions with Excel 2007 restarted, and
> the most recent version of SMF-Template-Yahoo-Historical-Quotes-MultiSymbol
> from your web share, the multi-symbol macro appears to error out at the
> section below. I wanted to let you know before exploring further in the
> event that you or another member had already resolved. My apologies in
> advance if I've missed something simple here. Thank you.
>
> 1. Open SMF-Template-Yahoo-Historical-Quotes-MultiSymbol
> 2. Click "GetMultiQuotes"
> 3. Runtime error 1004 generated at:
>
> ' blow it out to the starting column / row specified resized to accomodate
> the array
>
> RngTargetOutput.Resize(lFinal_Rows, UBound(vQuotes, 2)).Value2 =
> vQuotes ' Moves the block to the starting location
> ' change the formats - ASSUME DATE in first column
> With Range(RngTargetOutput.Cells(1, 1), RngTargetOutput.Cells(lFinal_Rows,
> 1))
> .NumberFormat = "yyyy-mm-dd"
> End With
>

Mon Apr 24, 2017 9:59 am (PDT) . Posted by:

lezm001

I'm been trying to decipher all of the formulas being discussed and it is time to just ask for some help. I have many of the "RCHGetElementNumber" elements that no longer work because of all that has already been discussed.

Could you help me figure out a solution for 4 elements so I can then use what I learn to figure out solutions to the rest that are no longer working?


The elements are:


Element 5064, Morningstar rating


Element 5042, 1 year category rank


Element 4937, best 3 year total return


Element 5106, 5 year beta


Thanks, appreciate any help you can provide.

Mon Apr 24, 2017 11:48 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Workarounds for now:

#5064:

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

​#5042:

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

​#4937:

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

​#5106:

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

On Mon, Apr 24, 2017 at 9:59 AM, dlezman@
​...wrote:

>
> I'm been trying to decipher all of the formulas being discussed and it is
> time to just ask for some help. I have many of the "RCHGetElementNumber"
> elements that no longer work because of all that has already been
> discussed.
>
> Could you help me figure out a solution for 4 elements so I can then use
> what I learn to figure out solutions to the rest that are no longer working?
>
> The elements are:
>
> Element 5064, Morningstar rating
>
> Element 5042, 1 year category rank
>
> Element 4937, best 3 year total return
>
> Element 5106, 5 year beta
>
> Thanks, appreciate any help you can provide.
>
>

Mon Apr 24, 2017 10:32 am (PDT) . Posted by:

rst66

Randy,
I am having problems retrieving some options quotes. I have the latest AddIn download installed.
When I try to retrieve the Ask price for a particular strike (which shows a valid quote on the Yahoo Finance options page) I receive an error message. If I change the strike I will get a quote.

Example: =smfGetOptionQuotes("OTEX 05/19 2017 $30 CALL","a") returns a quote.

=smfGetOptionQuotes("OTEX 05/19 2017 $35 CALL","a") returns ERROR

Example: =smfGetOptionQuotes("CHUY 05/19 2017 $30 PUT","a") returns a quote.

=smfGetOptionQuotes("CHUY 05/19 2017 $2750 PUT","a") returns ERROR

I have tried both $2750 and $27.50 for the strike. Neither seems to work.

I have this problem with other ticker symbols as well.

Thanks for any help.




Mon Apr 24, 2017 2:18 pm (PDT) . Posted by:

jbr863

For the past few days, I've been getting mixed results (sometimes working, sometimes not) from the smfGetOptionExpirations function.


As of about 10min ago, neither my spreadsheet nor the smfGetYahooPortfolioView-Multiple-Expirations-By-Strike-Prices.xls spreadsheet (in the website's template folder) was successfully pulling expiration dates for HAS, NKE, CTSH or SBUX (for example) - but both worked fine for F, BWLD and JWN.


I have reinstalled the add-in but still get the same issues. Am I missing something?


- Jonathan

Mon Apr 24, 2017 4:51 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Most likely, it's an intermittent problem with Yahoo.

I just tried HAS with
​smfGetYahooPortfolioView-Multiple-Expirations-By-Strike-Prices.xls
and it returned data for me.

The function extract data from a Yahoo JSON file:

https://query2.finance.yahoo.com/v7/finance/options/HAS

On Mon, Apr 24, 2017 at 2:18 PM, jbr863@
​...wrote:

> For the past few days, I've been getting mixed results (sometimes working,
> sometimes not) from the smfGetOptionExpirations function.
>
> As of about 10min ago, neither my spreadsheet nor the
> ​​
> smfGetYahooPortfolioView-Multiple-Expirations-By-Strike-Prices.xls
> spreadsheet (in the website's template folder) was successfully pulling
> expiration dates for HAS, NKE, CTSH or SBUX (for example) - but both worked
> fine for F, BWLD and JWN.
>
> I have reinstalled the add-in but still get the same issues. Am I missing
> something?
>
>

Mon Apr 24, 2017 5:27 pm (PDT) . Posted by:

"Jonathan" jbr863

Oh!  I should have mentioned before but I'm using OX.  Yahoo and Google have become unreliable of late, as you have noted frequently over the recent past.
I just tried HAS again on "Y" and "OX" in the array formula for expiration dates (in smfGetYahooPortfolioView- Multiple-Expirations-By- Strike-Prices.xls) and while I get dividend, price and volume data, I still get no options expiration dates.
Any ideas of what may be going on if "OX" is the source?
- Jonathan

Mon Apr 24, 2017 3:02 pm (PDT) . Posted by:

philgiz_2000

For the past week the function "RCHGetTableCell" is generating "ERROR" results. This seems to have happened when Yahoo changed their financial tables. I've loaded the latest SMF addin but it is still an issue. Any idea how to fix??
Thanks for your help;
Phil


Mon Apr 24, 2017 5:29 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

​In some cases, Yahoo is no longer sending tables in the source code of the
web pages,​ but building the tables dynamically from JSON data instead.
That means you would have to extract the data from the JSON feed instead of
tables on the web page.

For example, annual balance sheet data is now here:

https://query1.finance.yahoo.com/v10/finance/quoteSummary/MMM?modules=balanceSheetHistory

On Mon, Apr 24, 2017 at 3:02 PM, philgiz@
​...wrote:

> For the past week the function "RCHGetTableCell" is generating "ERROR"
> results. This seems to have happened when Yahoo changed their financial
> tables. I've loaded the latest SMF addin but it is still an issue. Any
> idea how to fix??
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Diposting oleh EXCEL FEED LEARNING di 17.29

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)