Kamis, 01 Desember 2011

[smf_addin] Digest Number 2032

Messages In This Digest (19 Messages)

Messages

1a.

Where to get historical price for future

Posted by: "traderideahan" traderideahan@yahoo.com   traderideahan

Wed Nov 30, 2011 9:56 am (PST)



Hi All,

Do you know where we can get daily historical data for futures so that we can extract it to the excel(i.e. copper, oil, silver, etc)?

Thanks.
Denny.

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
1b.

Re: Where to get historical price for future

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Wed Nov 30, 2011 11:05 am (PST)



Take a look at barchart.com and the template in the files areas.

On Wed, Nov 30, 2011 at 10:40 AM, traderideahan <traderideahan@yahoo.com>wrote:

> Do you know where we can get daily historical data for futures so that we
> can extract it to the excel(i.e. copper, oil, silver, etc)?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
2a.

Silver spot price

Posted by: "Ralph" ralph.bagwell@gmail.com   ralphbag

Wed Nov 30, 2011 9:56 am (PST)



Randy ,

Some time ago you were kind enough to furnish this:

=VALUE(MID(RCHGetTableCell("http://www.bullionspotprice.com/silver",6,"1 Ounce Britannia"),5,9))

for me to get the silver spot price. For some reason it stopped working today and I've spent quite a while trying to "fix" it with no success .

Would you please do your magic again and show how .

OR (source)at this sight http://www.bullionspotprice.com/ says:

<p class="dtls">32.765 <span class="up">+0.815 (+2.55%)</span></p>

The "32.765" is what I want. Is this maybe a better place to get this ?

Thank you kind sir.

Ralph

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (10)
2b.

Re: Silver spot price

Posted by: "Mike McQuaid" mikemcq802@yahoo.com   mikemcq802

Wed Nov 30, 2011 10:22 am (PST)



Ralph, I didn't see the same html source as you described on that page.
But, this extracts the spot price from the top of that page:

=VALUE(LEFT(smfGetTagContent("http://www.bullionspotprice.com/silver","span"
,1,"spotPriceLarge"),5 <http://www.bullionspotprice.com/silver> ))

VALUE converts the text to a number. LEFT trims the " USD" off the price
shown.


The source on that page for this was:
<p class="spotPriceLarge"><span class="spotPriceLargeUp">32.90 USD</span><br
/><span class="spotPriceUpdated">United States Dollars (USD) spot price last
updated: 9 minutes ago.

_____

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf
Of Ralph
Sent: Wednesday, November 30, 2011 12:57 PM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Silver spot price

Randy ,

Some time ago you were kind enough to furnish this:

=VALUE(MID(RCHGetTableCell("http://www.bullionspotprice.com/silver",6,"1
Ounce Britannia"),5,9))

for me to get the silver spot price. For some reason it stopped working
today and I've spent quite a while trying to "fix" it with no success .

Would you please do your magic again and show how .

OR (source)at this sight http://www.bullionspotprice.com/ says:

<p class="dtls">32.765 <span class="up">+0.815 (+2.55%)</span></p>

The "32.765" is what I want. Is this maybe a better place to get this ?

Thank you kind sir.

Ralph

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (10)
2c.

Re: Silver spot price

Posted by: "Ralph" ralph.bagwell@gmail.com   ralphbag

Wed Nov 30, 2011 10:45 am (PST)



Works like a charm - thanks SO much.

--- In smf_addin@yahoogroups.com, "Mike McQuaid" <mikemcq802@...> wrote:
>
> Ralph, I didn't see the same html source as you described on that page.
> But, this extracts the spot price from the top of that page:

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (10)
3a.

MS Office 2011 (Apple) compatibility with SMF Add-In

Posted by: "addison.ingle" addison.ingle@gmail.com   addison.ingle

Wed Nov 30, 2011 11:41 am (PST)



We are new users of SMF Add-In, but love what it is giving us. However, we are trying to open excel files in the Apple MS Office 2011 version, and having trouble connecting to the add-in. Is this a case of user error, or is there a compatibility issue?

Thanks in advance......

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
3b.

Re: MS Office 2011 (Apple) compatibility with SMF Add-In

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Wed Nov 30, 2011 11:43 am (PST)



Can't be of help, sorry. No Mac experience here. But, from what I
understand, you do need to run it under a Windows compatible environment on
the Mac, because it uses Windows DLL libraries.

On Wed, Nov 30, 2011 at 12:37 PM, addison.ingle <addison.ingle@gmail.com>wrote:

> We are new users of SMF Add-In, but love what it is giving us. However, we
> are trying to open excel files in the Apple MS Office 2011 version, and
> having trouble connecting to the add-in. Is this a case of user error, or
> is there a compatibility issue?
>
> Thanks in advance......
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
4a.

Re: What url for HTMLGetTable for OX "All expirations"?

Posted by: "Mike McQuaid" mikemcq802@yahoo.com   mikemcq802

Wed Nov 30, 2011 12:42 pm (PST)



Very cool Randy - thanks. I liked your idea about Transpose and I didn't
realize Join would work across multiple rows - that was helpful.

Thanks also for OX3. Alas, there is a slight bug with it. When the Bid
price is zero for a strike, the returned strike price is a peculiar number.
The volume and open interest values are correct as well as the Ask, just the
Strike is wrong (of the data items I returned). I attached a spreadsheet to
facilitate your evaluation.

As an aside, I tried Google as a source but the first ticker I tried (INTC)
was missing the entire Mar12 chain - argh!. It also doesn't show the
weeklies, although that isn't crucial for my purposes. So far, OX is the
most reliable so I hope it stays available as it migrates to tighter
integration with Schwab.

_____

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf
Of Randy Harmelink
Sent: Wednesday, November 30, 2011 1:56 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] What url for HTMLGetTable for OX "All expirations"?

Interesting -- the problem is that if you give it a cell or range reference
for the TIcker List, my expectation was that each cell would contain only
one ticker symbol. An easy fix (will upload soon). But, in fact, if you use
a "+" as the delimiters instead of commas, it would work for you right now.
You don't need the individual Join cells either -- you could do something
like:

=smfGetOptionQuotes(smfJoin(D5:D16,"+")&"+"&smfJoin(E5:E16,"+")&"+"&smfJoin(
F5:F16,"+"),"xsba6",1,"OX2")

Another interesting item -- I see that you were only getting the first two
ITM and first two OTM strike prices for IBM even though you entered a range
of 12 cells, which should give you the first 6 of each. I was getting full
ranges in testing, but to get IBM's, I had to add a few more parameters to
the URL. Strange. Now I'm getting a full set of 12 strike prices for each
expiration date. When I upload a new beta version, it will include this
change.

Until your messages, I wasn't even aware of the web page with the open
interest and volume -- that it was available without a login. I'm
considering adding an "OX3" data source for that web page.

By the way, an easier way to do your process:

* Array-enter this formula in cells G5:R5:

IF(B5="","",TRANSPOSE(smfGetOptionStrikes($B$2,B5,"C","OX",1,12,1)))

* Now that the strike prices are all on a row with the expiration
date, you can just copy G5:R5 down into G6:R7 (or do more rows if you want).
No need to manually change any cell references for each range.

* Then you can use the smfJoin() with the entire range of transposed
ticker symbols:

=smfGetOptionQuotes(smfJoin(G5:R7,"+"),"xsba6",1,"OX2")

In fact, you could easily fill the entire G5:R16 range with that formula and
then use that entire range in the smfJoin() function, if you wanted to get
all available expiration dates.

Or, if you want to maintain the placement of all data -- have 12 rows for
each expiration date, you could simply do this in the final function:

=smfGetOptionQuotes(G5:R16,"xsba6",1,"OX2")

Since it processes by row and keeps a placeholder for blank ticker symbol
cells, everything should always be in the same place, grouped by expiration
date.

On Tue, Nov 29, 2011 at 10:38 PM, Mike McQuaid <mikemcq802@yahoo.com> wrote:

I downloaded the Nov 29 beta with the new OX option functions (and smfJoin)
but can only retrieve 1 option quote using smfGetOptionQuotes when I
provided a cell that contained multiple smf ticker symbols (joined by
smfJoin).

I attached a sample spreadsheet.

My idea was:

Get list of expirations using smfGetOptionExpirations
From this expiry list, use GetOptionStrikes to generate ticker symbols with
multiple strikes for several Expiry dates
Use smfJoin to concatenate these ticker symbols into one cell
Use smfGetOptionQuotes to retrieve quotes for all of these tickers

Every smf function is array entered except for the Join.

I'm trying to create a clean, contiguous list of option chains for the next
several months. I used the getHTMLtable for OX and that got me the data I
wanted - although was a little ugly (not crucial). Was hoping these new
functions would give me a solution that wasn't as tightly tied to a specific
url format.

Pls let me know what's wrong.
Thanks, Mike

PS: One big advantage of OX and getHTMLTable is that I can get the volume
and open interest columns. These don't work for OX in GetOptionQuotes - it
would be great if it would :)

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (11)
4b.

Re: What url for HTMLGetTable for OX "All expirations"?

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Wed Nov 30, 2011 1:33 pm (PST)



The OX3 web page had very strange coding on the strike prices, so I had to
extract it via HTML anchor links. Unfortunately, those zero bid prices
don't have the normal HTML anchor link. Darn. I'll have to go some other
route. Think I have a fix -- new version out tomorrow.

I've about given up on the MarketWatch changes. I took another look at it
last night, and I'm having a lot of trouble interpreting their ticker
symbols. And they seem to be missing quite a few things. I think I'll wait
a few weeks and see if they become more stable. Maybe they're in the middle
of some type of transition.

It was interesting when I first created the new smfGetOptionExpirations()
function, because I was getting different lists of dates from the different
sources. And then when I did the smfGetOptionStrikes(), I would get
different lists of strike prices from the different sources. It really made
me wonder about the reliability of the other data.

I just wish more of the pages of OX were available without needing to log
in.

If anyone has a reliable source of option quotes other than those I have or
have tried (MorningStar, Zacks, Schaeffer's, CBOE, NASDAQ) -- speak up.

It was so nice before the option symbols change, when RCHGetYahooQuotes()
worked just fine with Yahoo.

One thing I have noticed with Yahoo -- just because the options can't be
found on the web page of the underlying equity doesn't mean they don't
exist. If you create the option symbol and look for the page, they are
often there. But that means going one page at a time -- very slow.

On Wed, Nov 30, 2011 at 1:42 PM, Mike McQuaid <mikemcq802@yahoo.com> wrote:

> **
>
> Very cool Randy - thanks. I liked your idea about Transpose and I didn't
> realize Join would work across multiple rows - that was helpful.
>
> Thanks also for OX3. Alas, there is a slight bug with it. When the Bid
> price is zero for a strike, the returned strike price is a peculiar
> number. The volume and open interest values are correct as well as the
> Ask, just the Strike is wrong (of the data items I returned). I attached a
> spreadsheet to facilitate your evaluation.
>
> As an aside, I tried Google as a source but the first ticker I tried
> (INTC) was missing the entire Mar12 chain - argh!. It also doesn't show
> the weeklies, although that isn't crucial for my purposes. So far, OX is
> the most reliable so I hope it stays available as it migrates to tighter
> integration with Schwab.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (11)
4c.

Re: What url for HTMLGetTable for OX "All expirations"?

Posted by: "Mike McQuaid" mikemcq802@yahoo.com   mikemcq802

Wed Nov 30, 2011 2:01 pm (PST)



Thanks - will watch for update tomorrow (or so).

I can't vouch for it much, or know if you can even make use of it, but, I
ran across this option site yesterday. The few I've tried look solid and
its very fast. Quotes are free but they offer more services for
subscribers. Perhaps that means their free data will be better quality -
given its what their paid services are built on?

http://www.optionistics.com/f/stock_option_chains

_____

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf
Of Randy Harmelink
Sent: Wednesday, November 30, 2011 4:34 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] What url for HTMLGetTable for OX "All expirations"?

The OX3 web page had very strange coding on the strike prices, so I had to
extract it via HTML anchor links. Unfortunately, those zero bid prices don't
have the normal HTML anchor link. Darn. I'll have to go some other route.
Think I have a fix -- new version out tomorrow.

I've about given up on the MarketWatch changes. I took another look at it
last night, and I'm having a lot of trouble interpreting their ticker
symbols. And they seem to be missing quite a few things. I think I'll wait a
few weeks and see if they become more stable. Maybe they're in the middle of
some type of transition.

It was interesting when I first created the new smfGetOptionExpirations()
function, because I was getting different lists of dates from the different
sources. And then when I did the smfGetOptionStrikes(), I would get
different lists of strike prices from the different sources. It really made
me wonder about the reliability of the other data.

I just wish more of the pages of OX were available without needing to log
in.

If anyone has a reliable source of option quotes other than those I have or
have tried (MorningStar, Zacks, Schaeffer's, CBOE, NASDAQ) -- speak up.

It was so nice before the option symbols change, when RCHGetYahooQuotes()
worked just fine with Yahoo.

One thing I have noticed with Yahoo -- just because the options can't be
found on the web page of the underlying equity doesn't mean they don't
exist. If you create the option symbol and look for the page, they are often
there. But that means going one page at a time -- very slow.

On Wed, Nov 30, 2011 at 1:42 PM, Mike McQuaid <mikemcq802@yahoo.com> wrote:

Very cool Randy - thanks. I liked your idea about Transpose and I didn't
realize Join would work across multiple rows - that was helpful.

Thanks also for OX3. Alas, there is a slight bug with it. When the Bid
price is zero for a strike, the returned strike price is a peculiar number.
The volume and open interest values are correct as well as the Ask, just the
Strike is wrong (of the data items I returned). I attached a spreadsheet to
facilitate your evaluation.

As an aside, I tried Google as a source but the first ticker I tried (INTC)
was missing the entire Mar12 chain - argh!. It also doesn't show the
weeklies, although that isn't crucial for my purposes. So far, OX is the
most reliable so I hope it stays available as it migrates to tighter
integration with Schwab.

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (11)
4d.

Re: What url for HTMLGetTable for OX "All expirations"?

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Wed Nov 30, 2011 2:14 pm (PST)



Their "new" pages are interactive, so I'm not sure the data can be
retrieved with the add-in. I need a bookmarkable URL for the add-in to go
after the data. It looks like the "old" pages are accessible -- but I
wonder how long they will be around. For example:

http://www.optionistics.com/f/option_chains?date=20111129&symbol=MMM&range=-1&putcall=C&expiry=-1&strike=&nonstd=-1&greeks=1&form=0&sub=0&lock=0&v=1&parm=

On Wed, Nov 30, 2011 at 3:01 PM, Mike McQuaid <mikemcq802@yahoo.com> wrote:

> **
>
> I can't vouch for it much, or know if you can even make use of it, but, I
> ran across this option site yesterday. The few I've tried look solid and
> its very fast. Quotes are free but they offer more services for
> subscribers. Perhaps that means their free data will be better quality -
> given its what their paid services are built on?
>
> http://www.optionistics.com/f/stock_option_chains
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (11)
4e.

Re: What url for HTMLGetTable for OX "All expirations"?

Posted by: "Mike McQuaid" mikemcq802@yahoo.com   mikemcq802

Wed Nov 30, 2011 3:43 pm (PST)



I thought so but wasn't certain. Tough to rely on their "old" ones though
knowing they might disappear "tomorrow". too bad, they have a lot of good
data easily grabbed.

_____

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf
Of Randy Harmelink
Sent: Wednesday, November 30, 2011 5:15 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] What url for HTMLGetTable for OX "All expirations"?

Their "new" pages are interactive, so I'm not sure the data can be retrieved
with the add-in. I need a bookmarkable URL for the add-in to go after the
data. It looks like the "old" pages are accessible -- but I wonder how long
they will be around. For example:

http://www.optionistics.com/f/option_chains?date=20111129
<http://www.optionistics.com/f/option_chains?date=20111129&symbol=MMM&range=
-1&putcall=C&expiry=-1&strike=&nonstd=-1&greeks=1&form=0&sub=0&lock=0&v=1&pa
rm=>
&symbol=MMM&range=-1&putcall=C&expiry=-1&strike=&nonstd=-1&greeks=1&form=0&s
ub=0&lock=0&v=1&parm=

On Wed, Nov 30, 2011 at 3:01 PM, Mike McQuaid <mikemcq802@yahoo.com> wrote:

I can't vouch for it much, or know if you can even make use of it, but, I
ran across this option site yesterday. The few I've tried look solid and
its very fast. Quotes are free but they offer more services for
subscribers. Perhaps that means their free data will be better quality -
given its what their paid services are built on?

http://www.optionistics.com/f/stock_option_chains

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (11)
5a.

Re: Why do negative P/E ratios from morningstar show as 0?

Posted by: "Les S" dolce7ss@yahoo.com   dolce7ss

Wed Nov 30, 2011 2:14 pm (PST)



Excellent, thank you!

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Unfortunately that page is dynamically generated, so there is nothing in
> the source code of the web page for the add-in to extract. However, the
> data from that page you're interested in actually comes from here:
>
> http://financials.morningstar.com/valuation/valuation-history.action?t=C&type=price-earnings
>
> So, this appears to work, but the page is not a real HTML page, but one to
> be consumed by JavaScript:
>
> =RCHGetTableCell("
> http://financials.morningstar.com/valuation/valuation-history.action?t=C&type=price-earnings
> ",9,"abbr=""Price/Earnings")
>
> On Tue, Nov 29, 2011 at 10:34 PM, Les S <dolce7ss@...> wrote:
>
> > I see, thanks. Any way to tap into the javascript of the main morningstar
> > page? http://financials.morningstar.com/valuation/price-ratio.html?t=C
> >
> > Or is that the limit for now?
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)
6a.

SMFForcerecalculation

Posted by: "newengwongpeggy" tong82@gmail.com   newengwongpeggy

Wed Nov 30, 2011 6:26 pm (PST)



Hi Randy,

When I run this in the VBA by pressing the play button

Sub SMFForceRecalculation()
iMorningStar = 0
iInit = 0
For i1 = 1 To kPages
aData(i1, 1) = "" ' Reset stored ticker array
Next i1
If Val(Application.Version) < 10 Then
Application.CalculateFull
Else
Application.CalculateFullRebuild
End If
End Sub

I got an error message " Compile error: sub or function not defined " in the row Data (i1,1) = ""
http://www.traderant.com/images/traderant%20blog/Image%20114.png

but if i type SMFForceRecalculation manually in VBA Macro (Alt +F8), it works fine.

I have macro to autorun the smfforcerecalculation, but i cant do it as it only works if i type the macro manually.

any idea to fix it

thx
Ant

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
6b.

Re: SMFForcerecalculation

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Wed Nov 30, 2011 6:39 pm (PST)



It looks like you're trying to run a copy of the smfForceRecalculation
macro that you've copied to your own VBA project. That won't work, since
the aData() array is defined in the add-in's VBA project. Your copy of the
macro won't have access to that project's data.

Why can't you run the smfForceRecalculation macro from your project? It's
just a matter of setting up the add-in as a reference library for YOUR
project.

On Wed, Nov 30, 2011 at 7:26 PM, newengwongpeggy <tong82@gmail.com> wrote:

>
> When I run this in the VBA by pressing the play button
>
> Sub SMFForceRecalculation()
> iMorningStar = 0
> iInit = 0
> For i1 = 1 To kPages
> aData(i1, 1) = "" ' Reset stored ticker array
> Next i1
> If Val(Application.Version) < 10 Then
> Application.CalculateFull
> Else
> Application.CalculateFullRebuild
> End If
> End Sub
>
> I got an error message " Compile error: sub or function not defined " in
> the row Data (i1,1) = ""
> http://www.traderant.com/images/traderant%20blog/Image%20114.png
>
> but if i type SMFForceRecalculation manually in VBA Macro (Alt +F8), it
> works fine.
>
> I have macro to autorun the smfforcerecalculation, but i cant do it as it
> only works if i type the macro manually.
>
> any idea to fix it
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
6c.

Re: SMFForcerecalculation

Posted by: "newengwongpeggy" tong82@gmail.com   newengwongpeggy

Wed Nov 30, 2011 7:24 pm (PST)



so where should i put the smfForceRecalculation macro?
I am currently putting in my personal macro.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> It looks like you're trying to run a copy of the smfForceRecalculation
> macro that you've copied to your own VBA project. That won't work, since
> the aData() array is defined in the add-in's VBA project. Your copy of the
> macro won't have access to that project's data.
>
> Why can't you run the smfForceRecalculation macro from your project? It's
> just a matter of setting up the add-in as a reference library for YOUR
> project.
>
> On Wed, Nov 30, 2011 at 7:26 PM, newengwongpeggy <tong82@...> wrote:
>
> >
> > When I run this in the VBA by pressing the play button
> >
> > Sub SMFForceRecalculation()
> > iMorningStar = 0
> > iInit = 0
> > For i1 = 1 To kPages
> > aData(i1, 1) = "" ' Reset stored ticker array
> > Next i1
> > If Val(Application.Version) < 10 Then
> > Application.CalculateFull
> > Else
> > Application.CalculateFullRebuild
> > End If
> > End Sub
> >
> > I got an error message " Compile error: sub or function not defined " in
> > the row Data (i1,1) = ""
> > http://www.traderant.com/images/traderant%20blog/Image%20114.png
> >
> > but if i type SMFForceRecalculation manually in VBA Macro (Alt +F8), it
> > works fine.
> >
> > I have macro to autorun the smfforcerecalculation, but i cant do it as it
> > only works if i type the macro manually.
> >
> > any idea to fix it
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
6d.

Re: SMFForcerecalculation

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Wed Nov 30, 2011 7:40 pm (PST)



You can't simply copy the smfForceRecalculation macro to your own VBA
project -- not unless you copy EVERYTHING you need from the add-in to your
personal macro as well. Which modules you need will vary depending on which
functions you are trying to use.

That's why you should set up the add-in as a reference, so that it's
functions and macros are available to your personal macros, without needing
to move or copy anything.

There is a VBA entry in the "Links" area of the group, on how to use the
add-in functions from your own VBA project.

On Wed, Nov 30, 2011 at 8:24 PM, newengwongpeggy <tong82@gmail.com> wrote:

> so where should i put the smfForceRecalculation macro?
> I am currently putting in my personal macro.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
7a.

excel takes a long time to recalc - ideas to do things differently?

Posted by: "mff2805" mff3429@comcast.net   mff2805

Thu Dec 1, 2011 1:24 am (PST)



hi,
i'm new to this group, having seen a mention of it in the thinkscript group's discussions. i downloaded the addin to the std directory and downloaded a bunch of files to see how it works.
this prompted me to put together a spreadsheet similar to what i've seen is several Van Tharp books and his web site weekly nesletter (at iitm dot com). the ss is an overall view of markets around the world and the different sectors here in the US. as measured by me as a relative perfomnace of a slew of ETFs versus a selected benchmark (either sly, qqq, gld or bil).
at first i got etf return data from a yahoo screen, but it turns out that info is only for the month end and sometimes i saw the returns for most etfs were the end of oct, but some were out-of-date and were for the end of sept. so pulling that return data for comparisons wouldn;t work.
so, i then saw the smfPricesByDates function and populated several sheets in a ss with a bunch of calculations such as
= smfPricesByDates(F6,$B$20)/smfPricesByDates(F6,$B$21)/(1+$B$4) -1
where b20 is today and b21 is the chosen date in the past. in all my ss has maybe 200 or so of these calcs, as well as 200 or so of these function calls, =RCHGetTableCell("http://finance.yahoo.com/etf/browser/mkt?cs=1&ce=9999",-9,">"&R20&"<"), to get the ETF name.
so, here the question. the ss takes one to two minutes to cycle thru all the calcs every time i make a change to any cell, even if it's a formatting change. is there a better way to grab the info i want in one fell swoop, such that i don;t have to endure the long recalc time?

i can send the xls file somewhere, if it would be helpful.
thanks,
mike f.

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
7b.

Re: excel takes a long time to recalc - ideas to do things different

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Thu Dec 1, 2011 1:41 am (PST)



I believe the problem is that you are using "today" as a date in the
smfPricesByDates() functions. I'll assume that means either the NOW() or
the TODAY() function? Both of those functions are volatile, meaning that
every time an event occurs in the spreadsheet, they recalculate. As a
result, they will also trigger every one of the smfPricesByDates()
functions to recalculate. Yes, that can take a while.

Instead, try something like this to get the most current trading date
available:

=RCHGetYahooQuotes("SPY","d1")

That's a non-volatile function and will not be triggering all of those
recalculations every time something changes in the workbook.

You should use the smfForceRecalculation if you want to trigger everything
to update -- see the "Links" area of the group for more info on that.

Also, since you are using the smfPricesByDates() function twice in the same
formula, you'll be going out to the Internet twice for each formula. You'd
be better off retrieving both dates at the same time with an array-entered
version of the function, and then calculating the ratio based on those two
columns of data. That should nearly cut your recalculation time in half,
because you only need one Internet access per pair of numbers.

The RCHGetTableCell() function is not an issue at all, since it only
retrieves the web page once, but then extracts numerous data items from a
saved copy of the web page.

On Wed, Nov 30, 2011 at 11:47 PM, mff2805 <mff3429@comcast.net> wrote:

> i'm new to this group, having seen a mention of it in the thinkscript
> group's discussions. i downloaded the addin to the std directory and
> downloaded a bunch of files to see how it works.
> this prompted me to put together a spreadsheet similar to what i've seen
> is several Van Tharp books and his web site weekly nesletter (at iitm dot
> com). the ss is an overall view of markets around the world and the
> different sectors here in the US. as measured by me as a relative
> perfomnace of a slew of ETFs versus a selected benchmark (either sly, qqq,
> gld or bil).
> at first i got etf return data from a yahoo screen, but it turns out that
> info is only for the month end and sometimes i saw the returns for most
> etfs were the end of oct, but some were out-of-date and were for the end of
> sept. so pulling that return data for comparisons wouldn;t work.
> so, i then saw the smfPricesByDates function and populated several sheets
> in a ss with a bunch of calculations such as
> = smfPricesByDates(F6,$B$20)/smfPricesByDates(F6,$B$21)/(1+$B$4) -1
> where b20 is today and b21 is the chosen date in the past. in all my ss
> has maybe 200 or so of these calcs, as well as 200 or so of these function
> calls, =RCHGetTableCell("
> http://finance.yahoo.com/etf/browser/mkt?cs=1&ce=9999",-9,">"&R20&"<"),
> to get the ETF name.
> so, here the question. the ss takes one to two minutes to cycle thru all
> the calcs every time i make a change to any cell, even if it's a formatting
> change. is there a better way to grab the info i want in one fell swoop,
> such that i don;t have to endure the long recalc time?
>
> i can send the xls file somewhere, if it would be helpful.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
Recent Activity
Visit Your Group
Yahoo! News

Get it all here

Breaking news to

entertainment news

Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Search Ads

Get new customers.

List your web site

in Yahoo! Search.

Need to Reply?

Click one of the "Reply" links to respond to a specific message in the Daily Digest.

Create New Topic | Visit Your Group on the Web
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

Diposting oleh EXCEL FEED LEARNING di 02.16

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)