Jumat, 12 Juli 2013

[smf_addin] Digest Number 2690

5 New Messages

Digest #2690

Messages

Thu Jul 11, 2013 9:09 am (PDT) . Posted by:

"racecar00" racecar00

Hi Randy,

Could you help me correct the syntax for concatenating cell references using Morningstar. Here is the formula I am using:

=RCHGetTableCell("http://performance.morningstar.com/Performance/stock/trailing-\
total-returns.action?t="&$B97,3,"Total Return %","ticker")

The unique part of this link is that the row header is dynamic and not static,
that is, it changes from a fixed name like "price" to the ticker. I know I can
concatenate the last expression with a cell reference (e.g. $B97 in place of
"ticker") but so far I have been unsuccessful. Here is an Example of one of the formuala I tried, unsuccessfully:

=RCHGetTableCell("http://performance.morningstar.com/Performance/stock/trailing-\
total-returns.action?t="&$B97,3,"Total Return %"&$B97)

Thanks in advance

Thu Jul 11, 2013 10:37 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

If we can rely on that web page always having just the one table, you could
do something as simple as:

=RCHGetTableCell("
http://performance.morningstar.com/Performance/stock/trailing-total-returns.action?t=
"&$B97,4,,,,,3)

...which says to get the 4th column of the 3rd row of that table.

By the way, try that URL on a few worksheets of the
smfGetTagContent-Quick-Webpage-Examination.xls workbook. It can give you a
good idea of how things are laid out in the coding of the web page.

For example, in the "By Cascading Value" sheet, you can just put in the URL
and a "String" of "<tr" to show each table row. Or you could do the same
thing by using the "By HTML Tag" worksheet, putting in that URL and an HTML
Tag of "tr". Or, just go over to the "Table Extract" worksheet, plug in the
URL, delete the search strings and set the directions to "1" (to get the
1st table on the web page).

On Thu, Jul 11, 2013 at 9:09 AM, racecar00 <agelhausen@gmail.com> wrote:

>
> Could you help me correct the syntax for concatenating cell references
> using Morningstar. Here is the formula I am using:
>
> =RCHGetTableCell("
> http://performance.morningstar.com/Performance/stock/trailing-\
> total-returns.action?t="&$B97,3,"Total Return %","ticker")
>
> The unique part of this link is that the row header is dynamic and not
> static,
> that is, it changes from a fixed name like "price" to the ticker. I know I
> can
> concatenate the last expression with a cell reference (e.g. $B97 in place
> of
> "ticker") but so far I have been unsuccessful. Here is an Example of one
> of the formuala I tried, unsuccessfully:
>
> =RCHGetTableCell("
> http://performance.morningstar.com/Performance/stock/trailing-\
> total-returns.action?t="&$B97,3,"Total Return %"&$B97)
>

Thu Jul 11, 2013 11:37 am (PDT) . Posted by:

"Trent" glasshoppa

I only gather end of day data for this. So I can't help with the historical data. Below are the formulas that work for me (Win XP, Excel 07)

For Index Calls:
=RCHGetTableCell("http://www.cboe.com/data/mktstat.aspx",1,"Index Options","Index Options","Volume&quot;)

For Index Puts:
=RCHGetTableCell("http://www.cboe.com/data/mktstat.aspx",2,"Index Options","Index Options","Volume&quot;)

For Exchange Traded Products Calls:
=RCHGetTableCell("http://www.cboe.com/data/mktstat.aspx",1,"Exchange Traded Products","Exchange Traded Products","Exchange Traded Products","Volume&quot;)

For Exchange Traded Products Puts:
=RCHGetTableCell("http://www.cboe.com/data/mktstat.aspx",2,"Exchange Traded Products","Exchange Traded Products","Exchange Traded Products","Volume&quot;)

For Equity Calls:
=RCHGetTableCell("http://www.cboe.com/data/mktstat.aspx",1,"Equity Options","Equity Options","Volume&quot;)

For Equity Puts:
=RCHGetTableCell("http://www.cboe.com/data/mktstat.aspx",2,"Equity Options","Equity Options","Volume&quot;)

Hope this works for you as well.

Trent

Thu Jul 11, 2013 9:39 pm (PDT) . Posted by:

"happenedby" happenedby

Randy,

First time here. Great tools!

Any way to EXCLUDE the minis? i.e. Obtain only the regular monthlies.

Thanks!

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> I just uploaded RCH_Stock_Market_Functions-2.1.2013.06.28.zip to the "Works
> in Progress" folder. I just expanded the "mini" option coding I added at
> the start of the month. It was specific for a "7" instead of any numeric.
>
> Let me know if that works for you...
>
> On Fri, Jun 28, 2013 at 12:37 PM, Don <don@...> wrote:
>
> > Would it be possible for you to add something along the lines of ****
> > below, or add another variable, to be able to get Yahoo quotes for stocks
> > like CX which has non-standard options such as CX4 in 2014? Unfortunately,
> > the sURL and the Find1 are different.
> >
> > 'modGetOptionQuotes
> >
> > 'Function smfGetYahooOptionQuote
> >
> > '------------------> Do primary search
> > Dim nLast As Variant
> > If sTicker = "VIX" Then sTicker = "^VIX"
> >
> > **** If IsNumeric(Right(sTicker, 1)) Then sTicker = Left(sTicker,
> > Len(sTicker) - 1)
> >
> > sURL = "http://finance.yahoo.com/q/op?s=" & sTicker & "&m=" &
> > Format(pExpiry, "yyyy-mm")
> >
> >
>

Thu Jul 11, 2013 9:48 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Which minis are you getting when requesting monthlies?

On Thu, Jul 11, 2013 at 9:04 PM, happenedby <happenedby@yahoo.com> wrote:

>
> Any way to EXCLUDE the minis? i.e. Obtain only the regular monthlies.
>
Diposting oleh EXCEL FEED LEARNING di 01.17

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)