Selasa, 14 Agustus 2012

[smf_addin] Digest Number 2313

15 New Messages

Digest #2313
1a
Slow data feed from yahoo? by "Heath" thunderchckn
1c
Re: Slow data feed from yahoo? by "Randy Harmelink" rharmelink
1d
Re: Slow data feed from yahoo? by "friedman_steve" friedman_steve
1e
Re: Slow data feed from yahoo? by "meta1946" meta1946
1f
Re: Slow data feed from yahoo? by "zarathustra_winced@yahoo.com" zarathustra_winced
3a
Re: Error using element code 995 by "Randy Harmelink" rharmelink
3b
Re: Error using element code 995 by "David Nicholas" davidnicholas738
3c
Re: Error using element code 995 by "Randy Harmelink" rharmelink
4a
newbie by "ema" ema_joon
4b
Re: newbie by "Randy Harmelink" rharmelink

Messages

Tue Aug 14, 2012 7:08 am (PDT) . Posted by:

"Heath" thunderchckn

Hi,

I looked for information on this error in previous posts, but didn't see anything. An excel spreadsheet is freezing intermittently. Windows Resource Monitor shows that Excel is waiting for a thread to finish network I/O. The thread is a connection to q1.finance.vip.ac4.yahoo.com.

I disabled the RCHGETYAHOOQUOTES function from the spreadsheet, as a test, but the error persists. Is there another function that could be trying to send or receive data from q1.finance.vip.ac4.yahoo.com?

Thanks!

Tue Aug 14, 2012 8:36 am (PDT) . Posted by:

"Bob" rkdmon

Having the same problem since this morning European time. Looks like maybe a slow feed from Yahoo using RCHGET as the same isn't evident on another application that I use which has a Yahoo API feed. Am just going to wait and see if it gets cured from the yahoo end.

But any other input appreciated if someone has found a workround/fix.

Thanks,

Bob

--- In smf_addin@yahoogroups.com, "Heath" <heath.winter@...> wrote:
>
> Hi,
>
> I looked for information on this error in previous posts, but didn't see anything. An excel spreadsheet is freezing intermittently. Windows Resource Monitor shows that Excel is waiting for a thread to finish network I/O. The thread is a connection to q1.finance.vip.ac4.yahoo.com.
>
> I disabled the RCHGETYAHOOQUOTES function from the spreadsheet, as a test, but the error persists. Is there another function that could be trying to send or receive data from q1.finance.vip.ac4.yahoo.com?
>
> Thanks!
>

Tue Aug 14, 2012 8:52 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

I don't know of anything in the add-in that uses the
http://q1.finance.vip.ac4.yahoo.com address, so it must be a redirect on
Yahoo's part.

The only thing I can suggest is to make the workbook use as few
RCHGetYahooQuotes() as possible, by array-entering it/them over a range.

On Tue, Aug 14, 2012 at 7:08 AM, Heath <heath.winter@gmail.com> wrote:

>
> I looked for information on this error in previous posts, but didn't see
> anything. An excel spreadsheet is freezing intermittently. Windows
> Resource Monitor shows that Excel is waiting for a thread to finish network
> I/O. The thread is a connection to q1.finance.vip.ac4.yahoo.com.
>
> I disabled the RCHGETYAHOOQUOTES function from the spreadsheet, as a test,
> but the error persists. Is there another function that could be trying to
> send or receive data from q1.finance.vip.ac4.yahoo.com?
>

Tue Aug 14, 2012 11:01 am (PDT) . Posted by:

"friedman_steve" friedman_steve

for what it's worth...my spreadsheets that I've used daily for a longtime (thanks Randy), aren't working as of this morning either [it has many formulas and just gets stuck calculating. if I set excel calculation on manual, and update just one cell, it sometimes work, and sometimes get frozen.

Happening on 2 different computers at different locations (one Vista, one Windows 7... both have Verizon Fios. Looked to me like a Yahoo site issue. I've also noticed going directly to finance.yahoo.com site with my browser is getting stuck a lot too.

--- In smf_addin@yahoogroups.com, "Bob" <rkd@...> wrote:
>
> Having the same problem since this morning European time. Looks like maybe a slow feed from Yahoo using RCHGET as the same isn't evident on another application that I use which has a Yahoo API feed. Am just going to wait and see if it gets cured from the yahoo end.
>
> But any other input appreciated if someone has found a workround/fix.
>
> Thanks,
>
> Bob
>
> --- In smf_addin@yahoogroups.com, "Heath" <heath.winter@> wrote:
> >
> > Hi,
> >
> > I looked for information on this error in previous posts, but didn't see anything. An excel spreadsheet is freezing intermittently. Windows Resource Monitor shows that Excel is waiting for a thread to finish network I/O. The thread is a connection to q1.finance.vip.ac4.yahoo.com.
> >
> > I disabled the RCHGETYAHOOQUOTES function from the spreadsheet, as a test, but the error persists. Is there another function that could be trying to send or receive data from q1.finance.vip.ac4.yahoo.com?
> >
> > Thanks!
> >
>

Tue Aug 14, 2012 1:08 pm (PDT) . Posted by:

"meta1946" meta1946

I'm really hurting without the add-in working. It use it every today for a critical spreadsheet. Today it takes forever to get one current quote from Yahoo. Yet getting the same quote from their Yahoo finance web page works fine. Help!

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> I don't know of anything in the add-in that uses the
> http://q1.finance.vip.ac4.yahoo.com address, so it must be a redirect on
> Yahoo's part.
>
> The only thing I can suggest is to make the workbook use as few
> RCHGetYahooQuotes() as possible, by array-entering it/them over a range.
>
> On Tue, Aug 14, 2012 at 7:08 AM, Heath <heath.winter@...> wrote:
>
> >
> > I looked for information on this error in previous posts, but didn't see
> > anything. An excel spreadsheet is freezing intermittently. Windows
> > Resource Monitor shows that Excel is waiting for a thread to finish network
> > I/O. The thread is a connection to q1.finance.vip.ac4.yahoo.com.
> >
> > I disabled the RCHGETYAHOOQUOTES function from the spreadsheet, as a test,
> > but the error persists. Is there another function that could be trying to
> > send or receive data from q1.finance.vip.ac4.yahoo.com?
> >
>

Tue Aug 14, 2012 2:09 pm (PDT) . Posted by:

"zarathustra_winced@yahoo.com" zarathustra_winced

Its probably my fault, my simulation algo had has yahoo historical quotes on lockdown today. :) Twin xeons and 5000 tickers...

Seriously though, when in doubt reboot. Everything's working fine for me.
Sent via BlackBerry by AT&T

-----Original Message-----
From: "meta1946" <meta1946@yahoo.com>
Sender: smf_addin@yahoogroups.com
Date: Tue, 14 Aug 2012 17:38:38
To: <smf_addin@yahoogroups.com>
Reply-To: smf_addin@yahoogroups.com
Subject: [smf_addin] Re: Slow data feed from yahoo?

I'm really hurting without the add-in working. It use it every today for a critical spreadsheet. Today it takes forever to get one current quote from Yahoo. Yet getting the same quote from their Yahoo finance web page works fine. Help!

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> I don't know of anything in the add-in that uses the
> http://q1.finance.vip.ac4.yahoo.com address, so it must be a redirect on
> Yahoo's part.
>
> The only thing I can suggest is to make the workbook use as few
> RCHGetYahooQuotes() as possible, by array-entering it/them over a range.
>
> On Tue, Aug 14, 2012 at 7:08 AM, Heath <heath.winter@...> wrote:
>
> >
> > I looked for information on this error in previous posts, but didn't see
> > anything. An excel spreadsheet is freezing intermittently. Windows
> > Resource Monitor shows that Excel is waiting for a thread to finish network
> > I/O. The thread is a connection to q1.finance.vip.ac4.yahoo.com.
> >
> > I disabled the RCHGETYAHOOQUOTES function from the spreadsheet, as a test,
> > but the error persists. Is there another function that could be trying to
> > send or receive data from q1.finance.vip.ac4.yahoo.com?
> >
>




------------------------------------

Yahoo! Groups Links



Tue Aug 14, 2012 7:56 am (PDT) . Posted by:

"zellabettic" zellabettic

Hello,

I am trying to extract data on the upside & downside capture ratio for 1 year, 3 years, etc. from Morningstar via the webpage:

http://performance.morningstar.com/fund/ratings-risk.action?t=VWINX&region=USA&culture=en-US

I think I have found that this is not a table so I cannot use the RCHGET TABLECELL function. This may be some kind of dynamically generated webpage, but I am not sure how to tell. Is there another function or way to extract the data from this type of page that anyone can think of?

Thank you,

Clete

Tue Aug 14, 2012 8:49 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

It is a dynamically generated web page, so the add-in has nothing it can
extract. I also don't see the data items you want on the print report.

You may be able to extract it using a VBA routine that Interfaces with the
IE object, as it can access the web page AFTER it's been rendered and
dynamically generated. However, the exact coding may vary depending on
which versions of EXCEL and IE you're using, which is why I don't go that
route with the add-in.

On Tue, Aug 14, 2012 at 7:56 AM, zellabettic <zellabettic@yahoo.com> wrote:

>
> I am trying to extract data on the upside & downside capture ratio for 1
> year, 3 years, etc. from Morningstar via the webpage:
>
>
> http://performance.morningstar.com/fund/ratings-risk.action?t=VWINX&region=USA&culture=en-US
>
> I think I have found that this is not a table so I cannot use the RCHGET
> TABLECELL function. This may be some kind of dynamically generated
> webpage, but I am not sure how to tell. Is there another function or way
> to extract the data from this type of page that anyone can think of?
>

Tue Aug 14, 2012 9:18 am (PDT) . Posted by:

"zellabettic" zellabettic

Thank you Randy. I'll see if I can try to extract the data that way.

Clete

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> It is a dynamically generated web page, so the add-in has nothing it can
> extract. I also don't see the data items you want on the print report.
>
> You may be able to extract it using a VBA routine that Interfaces with the
> IE object, as it can access the web page AFTER it's been rendered and
> dynamically generated. However, the exact coding may vary depending on
> which versions of EXCEL and IE you're using, which is why I don't go that
> route with the add-in.
>
> On Tue, Aug 14, 2012 at 7:56 AM, zellabettic <zellabettic@...> wrote:
>
> >
> > I am trying to extract data on the upside & downside capture ratio for 1
> > year, 3 years, etc. from Morningstar via the webpage:
> >
> >
> > http://performance.morningstar.com/fund/ratings-risk.action?t=VWINX&region=USA&culture=en-US
> >
> > I think I have found that this is not a table so I cannot use the RCHGET
> > TABLECELL function. This may be some kind of dynamically generated
> > webpage, but I am not sure how to tell. Is there another function or way
> > to extract the data from this type of page that anyone can think of?
> >
>

Tue Aug 14, 2012 8:09 pm (PDT) . Posted by:

"Peter Urbani" purbani1

You could use one of these

Function captureratio(updown As String, rng1 As Range, rng2 As Range) As
Double
Dim l As Long, interim As Double, product1 As Double, product2 As Double

If rng1.Count <> rng2.Count Then upcaptureratio = xlErrValue
product1 = 1
For l = 1 To rng1.End(xlDown).Row - rng1.Row + 1

If updown = "up" Then
interim = 1 + IIf(rng2.Cells(l) >= 0, rng1.Cells(l), 0)
Else
interim = 1 + IIf(rng2.Cells(l) < 0, rng1.Cells(l), 0)
End If

product1 = product1 * interim
Next
product1 = product1 ^ (12 / rng1.Count) - 1

product2 = 1
For l = 1 To rng1.End(xlDown).Row - rng1.Row + 1

If updown = "up" Then
interim = 1 + IIf(rng2.Cells(l) >= 0, rng2.Cells(l), 0)
Else
interim = 1 + IIf(rng2.Cells(l) < 0, rng2.Cells(l), 0)
End If

product2 = product2 * interim
Next
product2 = product2 ^ (12 / rng1.Count) - 1

captureratio = product1 / product2
End Function

Function upc(Fnd, bmk)
x = Fnd.Count
Dim Exfndarr() As Variant
Dim Exbmkarr() As Variant
ReDim Exfndarr(x) As Variant
ReDim Exbmkarr(x) As Variant
Dim Exfndarr2() As Variant
Dim Exbmkarr2() As Variant
ReDim Exfndarr2(x + 1) As Variant
ReDim Exbmkarr2(x + 1) As Variant

For i = 1 To x
If bmk(i) >= 0 Then Exfndarr(i) = Fnd(i) Else Exfndarr(i) = 0
If bmk(i) >= 0 Then Exbmkarr(i) = bmk(i) Else Exbmkarr(i) = 0
Next i

Exfndarr2(0) = 100
Exbmkarr2(0) = 100

For j = 1 To x + 1
If j = 1 Then Exfndarr2(j) = 100 Else Exfndarr2(j) = Exfndarr2(j - 1) * (1
+ Exfndarr(j - 1))
If j = 1 Then Exbmkarr2(j) = 100 Else Exbmkarr2(j) = Exbmkarr2(j - 1) * (1
+ Exbmkarr(j - 1))
Next j

ub = (1 + Exfndarr2(x + 1) / 100 - 1) ^ (12 / x) - 1
Db = (1 + Exbmkarr2(x + 1) / 100 - 1) ^ (12 / x) - 1

upc = (ub / Db) * 100
End Function

Function dnc(Fnd, bmk)
x = Fnd.Count
Dim Exfndarr() As Variant
Dim Exbmkarr() As Variant
ReDim Exfndarr(x) As Variant
ReDim Exbmkarr(x) As Variant
Dim Exfndarr2() As Variant
Dim Exbmkarr2() As Variant
ReDim Exfndarr2(x + 1) As Variant
ReDim Exbmkarr2(x + 1) As Variant

For i = 1 To x
If bmk(i) < 0 Then Exfndarr(i) = Fnd(i) Else Exfndarr(i) = 0
If bmk(i) < 0 Then Exbmkarr(i) = bmk(i) Else Exbmkarr(i) = 0
Next i

Exfndarr2(0) = 100
Exbmkarr2(0) = 100

For j = 1 To x + 1
If j = 1 Then Exfndarr2(j) = 100 Else Exfndarr2(j) = Exfndarr2(j - 1) * (1
+ Exfndarr(j - 1))
If j = 1 Then Exbmkarr2(j) = 100 Else Exbmkarr2(j) = Exbmkarr2(j - 1) * (1
+ Exbmkarr(j - 1))
Next j

ub = (1 + Exfndarr2(x + 1) / 100 - 1) ^ (12 / x) - 1
Db = (1 + Exbmkarr2(x + 1) / 100 - 1) ^ (12 / x) - 1

dnc = (ub / Db) * 100
End Function

On Wed, Aug 15, 2012 at 4:18 AM, zellabettic <zellabettic@yahoo.com> wrote:

> **
>
>
> Thank you Randy. I'll see if I can try to extract the data that way.
>
> Clete
>
> --- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
> >
> > It is a dynamically generated web page, so the add-in has nothing it can
> > extract. I also don't see the data items you want on the print report.
> >
> > You may be able to extract it using a VBA routine that Interfaces with
> the
> > IE object, as it can access the web page AFTER it's been rendered and
> > dynamically generated. However, the exact coding may vary depending on
> > which versions of EXCEL and IE you're using, which is why I don't go that
> > route with the add-in.
> >
> > On Tue, Aug 14, 2012 at 7:56 AM, zellabettic <zellabettic@...> wrote:
> >
> > >
> > > I am trying to extract data on the upside & downside capture ratio for
> 1
> > > year, 3 years, etc. from Morningstar via the webpage:
> > >
> > >
> > >
> http://performance.morningstar.com/fund/ratings-risk.action?t=VWINX&region=USA&culture=en-US
> > >
> > > I think I have found that this is not a table so I cannot use the
> RCHGET
> > > TABLECELL function. This may be some kind of dynamically generated
> > > webpage, but I am not sure how to tell. Is there another function or
> way
> > > to extract the data from this type of page that anyone can think of?
> > >
> >
>
>
>

--

*Peter Urbani*

+64 21 0255 2816

LinkedIn Profile <http://nz.linkedin.com/in/peterurbani>

Skype peterurbani

Tue Aug 14, 2012 8:39 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Element 995 is for ETFs only, "High %" from ETF outlook. It's working fine
here.

Your comment on dividend yield confuses me.

On Tue, Aug 14, 2012 at 1:43 AM, ufdn16 <ufdn16@yahoo.com> wrote:

> Tried using RCHgetelementnumber 995. Results in error.
> Tried ctrl-alt-F9 to force recalculation.
> I still don't have my holdings dividend yield.
>
> Please help. David
>

Tue Aug 14, 2012 10:06 am (PDT) . Posted by:

"David Nicholas" davidnicholas738

what is the number for "dividend yield"

From: Randy Harmelink
Sent: Tuesday, August 14, 2012 10:39 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Error using element code 995

Element 995 is for ETFs only, "High %" from ETF outlook. It's working fine here.

Your comment on dividend yield confuses me.

On Tue, Aug 14, 2012 at 1:43 AM, ufdn16 <ufdn16@yahoo.com> wrote:

Tried using RCHgetelementnumber 995. Results in error.
Tried ctrl-alt-F9 to force recalculation.
I still don't have my holdings dividend yield.

Please help. David

Tue Aug 14, 2012 2:08 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Your two Yahoo choices, from the
RCHGetElementNumber-Element-Definitions.xls workbook:

*#* *Source* *Element* 4925 YahooKS Forward Annual Dividend Yield 4927
YahooKS Trailing Annual Dividend Yield

On Tue, Aug 14, 2012 at 10:06 AM, David Nicholas
<nicholasdavid@comcast.net>wrote:

>
> what is the number for "dividend yield"
>

Tue Aug 14, 2012 1:08 pm (PDT) . Posted by:

"ema" ema_joon

Hi I am new here and I am lost, I am not sure where to start. I want to make an Excel file that can update itself for few hundred companies for price, time and volume. Any help is really appreciated.

Tue Aug 14, 2012 2:10 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Check out the documentation for the RCHGetYahooQuotes() function. It even
has a link to a set of step-by-step illustrated instructions on setting up
a quotes grid.

On Tue, Aug 14, 2012 at 10:12 AM, ema <ema_joon@yahoo.com> wrote:

> Hi I am new here and I am lost, I am not sure where to start. I want to
> make an Excel file that can update itself for few hundred companies for
> price, time and volume. Any help is really appreciated.
>
Diposting oleh EXCEL FEED LEARNING di 20.09

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)