Sabtu, 11 Januari 2014

[smf_addin] Digest Number 2923

11 New Messages

Digest #2923

Messages

Fri Jan 10, 2014 1:54 pm (PST) . Posted by:

option2z

I agree with Randy et al that it is best to use array entered formulas to get ALL the current prices in one YAHOO request. My post was to illustrate that there are LOTS of YAHOO failures going on these days. But I WILL change my code to get all the "l1" (last price) data in a single request; faster and a single point of failure.

In my server side code, I access historical data for each stock, and there seems to be no way to bet multiple histories for multiple stocks in a single access.

Pete A

RE:
No question that YAHOO is failing a lot lately. Basically all I use is this formula to get the latest stock price:


=RCHGetYahooQuotes( <datacell> , "l1" )


a few of the cells don't update when I open the spreadsheet, and it takes longer than usual for the spreadsheet to open.


How do I implement a retry like you suggest?

Fri Jan 10, 2014 7:55 pm (PST) . Posted by:

mrothaus

I used to use the array-entered formula, but it messes up if I have blank tickers. For example, I have stock symbols in column A rows 1 - 30. I want the last stock price in column B rows 1 - 30. If I sell a stock in row 25 and delete the symbol in cell A25, the array-entered formula gets screwed up in the subsequent rows.


Up until recently, I couldn't tell the performance difference. But I'll give it another try, and use a dummy stock symbol in column A when I sell a stock so the array-entered formula stays in tact.

Fri Jan 10, 2014 8:04 pm (PST) . Posted by:

mikemcq802

I use a lower case "z" as a placeholder ticker. Luckily I don't have any other tickers that even begin with z so it stands out.

Further, I create a single sheet (tab) with all my Yahoo quote lookups in an array.

For my "real" sheet (tab), I use VLookup to retrieve the corresponding items from the Yahoo sheet.

This way I don't have placeholders in my real sheet. And, it's easier to manipulate since it isn't in an array itself. I can sort the columns, insert and delete rows, etc easily.

Fri Jan 10, 2014 8:56 pm (PST) . Posted by:

mrothaus

nice idea, but it doesn't work easily in my scenario.


I took a quick look at the RCHGetYahooQuotes, and I think I found the problem in the add-in.


Case Is >= 8192
sTickers = ""
For Each oCell In pTickers
If oCell.Value > " " Then sTickers = sTickers & oCell.Value & "+"
Next oCell
sTickers = Left(sTickers, Len(sTickers) - 1)




It is attempting to eliminate blank cell values by not adding it to the URL (list of tickers). But you can see that it will mess up in a range (array-entered formula). Randy should be able to implement a place-holder technique internally (probably has to return 0 for blank stock symbols in the range).

Fri Jan 10, 2014 9:17 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

You can use "DUMMY" as a placeholder. :)

Or "XXXXXX", which can never be a valid ticker symbol.

I think it best to have the data retrieval sheet like you are using. As you
say, then you can sort, insert, delete, etc without problems.

But the VOOKUP() should be based on the ticker symbol RETURNED by
RCHGetYahooQuotes(), not on the ticker PASSED to RCHGetYahooQuotes(). Just
to be safe.

On Fri, Jan 10, 2014 at 9:04 PM, <mikemcq802@yahoo.com> wrote:

>
> I use a lower case "z" as a placeholder ticker. Luckily I don't have any
> other tickers that even begin with z so it stands out.
>
> Further, I create a single sheet (tab) with all my Yahoo quote lookups in
> an array.
>
> For my "real" sheet (tab), I use VLookup to retrieve the corresponding
> items from the Yahoo sheet.
>
> This way I don't have placeholders in my real sheet. And, it's easier to
> manipulate since it isn't in an array itself. I can sort the columns,
> insert and delete rows, etc easily.
>

Fri Jan 10, 2014 9:25 pm (PST) . Posted by:

mikemcq802

"But the VOOKUP() should be based on the ticker symbol RETURNED by RCHGetYahooQuotes(), not on the ticker PASSED to RCHGetYahooQuotes(). Just to be safe."

Good tip. I've not had problems with that but safer approach.

Fri Jan 10, 2014 2:44 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

I'm trying it here and it's working fine. Select the range first, type or
copy in the formula, and Ctrl+Shift+Enter to array-enter it into that range.

=RCHGetHTMLTable("
http://emma.msrb.org/SecurityView/SecurityDetailsTrades.aspx?cusip=251129R61","Trade
Date/",-1,"",1)

On Fri, Jan 10, 2014 at 2:21 PM, <buck_69_69@yahoo.com> wrote:

>
> if i can even get the first column of data returned that'll be a start..
> but all i get back is tradedate
>

Fri Jan 10, 2014 5:23 pm (PST) . Posted by:

63b31a65c9c738e4af34541b0710492f

Many thanks for the hint. I did not make my VBA function volatile - but I was using TODAY() to calculate expiry. As all other cells were using this expiry data they were all volatile. Now I'm grabbing today's date from a website by using smf :-) Thanks!

Fri Jan 10, 2014 5:36 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

I usually use this as a substitute for TODAY(), since it will get the most
recent trading day AND is non-volatile:

=DATEVALUE(RCHGetYahooQuotes("SPY","d1"))

On Fri, Jan 10, 2014 at 6:23 PM, <m.heritsch@gmail.com> wrote:

> Many thanks for the hint. I did not make my VBA function volatile - but
> I was using TODAY() to calculate expiry. As all other cells were using this
> expiry data they were all volatile. Now I'm grabbing today's date from a
> website by using smf :-) Thanks!
>

Fri Jan 10, 2014 7:43 pm (PST) . Posted by:

lawrence.leesh

Hi Randy


Would you know why =RCHGetElementNumber("tmk";,408) returns "NA" when other stocks are fine? I could find the date on the MSN page.


thanks


LL

Fri Jan 10, 2014 7:58 pm (PST) . Posted by:

mikemcq802

The MSN page has all NA when I look at it:

http://investing.money.msn.com/investments/earnings-estimates?symbol=tmk

Where are you seeing the date?
Diposting oleh EXCEL FEED LEARNING di 00.49

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)