Messages In This Digest (7 Messages)
- 1a.
- Re: using results of RCHGetHTMLTable in Excel formula -> feature req From: sharilady@rocketmail.com
- 1b.
- Re: using results of RCHGetHTMLTable in Excel formula -> feature req From: Randy H
- 2a.
- Re: RCHGetWebData-Template-BarChart-Quotes-Extract.xls From: draz2121
- 3a.
- Re: Morningstar Stock Type & Stock Style From: V. Shankar
- 4a.
- local database From: leif_85
- 4b.
- Re: local database From: Randy Harmelink
- 5.
- A step-by-step example of using some of the new option functions From: Randy H
Messages
- 1a.
-
Re: using results of RCHGetHTMLTable in Excel formula -> feature req
Posted by: "sharilady@rocketmail.com" sharilady@rocketmail.com sharilady@rocketmail.com
Mon Jan 30, 2012 11:47 pm (PST)
Great stuff Randy, great stuff! I just grabbed yesterday's official release and it seems to work. Still misssing the workaround for the US thousand separator in modConvertData, but I tweaked that like in the previous versions :-)
One thing still drove me crazy last night. Any idea why this one works
=INDEX(RCHGetHTMLTable("http://finance. yahoo.com/ ";"PEG Ratio";-1;""q/ks?s=MMM ;1;TRUE;1; 2);2)
but this one not?
=OFFSET(RCHGetHTMLTable("http://finance. yahoo.com/ ";"PEG Ratio";-1;""q/ks?s=MMM ;1;TRUE;1; 2);0;1;1; 1)
The example is silly, but OFFSET gives me more flexibility in general. Any hint?
--- In smf_addin@yahoogroups.com , "Randy H" <rharmelink@...> wrote:
>
> Just pick up the beta version of the add-in from the "Works in Progress"
> folder. The change you want, if it's working right, should be the first
> change I made after the last official release. From the change log:
>
> Date Routine Description 2010-08-12 RCHGetHTMLTable
> Modified pDim1/pDim2 processing so the array size can be overridden
> from either VBA or from the worksheet. This allows other functions like
> TRANSPOSE() to manipulate the returned results correctly.
> I think all of the functions that return an array of data now operate
> the same way in terms of the pDim1 and pDim2 parameters.
>
> On Mon, Jan 30, 2012 at 12:21 AM, sharilady@...
> <sharilady@...> wrote:
>
> I'm currently working on a sheet where I would like to use the results
> of RCHGetHTMLTable (among others) in a formula. One can think about
> pulling one row of data and directly calculating the average of the
> values without displaying them.
>
> Although the function provides parameters pDim1 and pDim2, if called
> from within a workbook, these values will be reset by
> Application.Caller.Rows. Count. I'm also quite curious about the first
> couple of lines in the code, because for example bType will always(!) be
> set to 1. The intended error handling might not properly work here.
>
> Did you ever think about passing the intended dimensions for the results
> of the function and forcing them to be used? An additional boolean
> parameter which handles the useage of Application.Caller... might
> already do the trick. This could also be used for some other functions
> which seem to be working in a similar way.
>
> Could this be an idea for the next version of this wonderful tool?
>
- 1b.
-
Re: using results of RCHGetHTMLTable in Excel formula -> feature req
Posted by: "Randy H" rharmelink@gmail.com rharmelink
Mon Jan 30, 2012 11:56 pm (PST)
I also wish OFFSET() would work with the add-in functions.
Unfortunately, the first parameter in OFFSET() *must* be a range type of
item. It can't be an array. I've not found a way around that.
On Tue, Jan 31, 2012 at 12:47 AM, sharilady@rocketmail.com
<sharilady@rocketmail.com > wrote:
Great stuff Randy, great stuff! I just grabbed yesterday's official
release and it seems to work. Still misssing the workaround for the US
thousand separator in modConvertData, but I tweaked that like in the
previous versions :-)
One thing still drove me crazy last night. Any idea why this one works
=INDEX(RCHGetHTMLTable("http://finance. yahoo.com/ q/ks?s=MMM
<http://finance.yahoo.com/ > ";"PEG Ratio";-1;""q/ks?s=MMM ;1;TRUE;1; 2);2)
but this one not?
=OFFSET(RCHGetHTMLTable("http://finance. yahoo.com/ q/ks?s=MMM
<http://finance.yahoo.com/ > ";"PEGq/ks?s=MMM
Ratio";-1;"";1;TRUE;1; 2);0;1;1; 1)
The example is silly, but OFFSET gives me more flexibility in general.
Any hint?
- 2a.
-
Re: RCHGetWebData-Template-BarChart-Quotes-Extract.xls
Posted by: "draz2121" bolony21@gmail.com draz2121
Tue Jan 31, 2012 12:21 am (PST)
All working this morning, thank you.
--- In smf_addin@yahoogroups.com , "Randy H" <rharmelink@...> wrote:
>
> Yes, you do need the latest version of the add-in -- I'm using the
> smfWord() function. If you don't have that function available, it would
> cause the formula to error out and display "--".
>
> On Mon, Jan 30, 2012 at 5:38 PM, Shaun <bolony21@...> wrote:
>
> Oops I see you did use that ticker :)
> I will double check in the morning but find it strange that the volume
> is coming through.
> Do I need to use the latest version of your add in perhaps?
>
- 3a.
-
Re: Morningstar Stock Type & Stock Style
Posted by: "V. Shankar" tarakayan@yahoo.com tarakayan
Tue Jan 31, 2012 12:43 am (PST)
It seems to be working...I am able to get results using elements: 878,877,879,880?
>____________________ _________ ___
> From: Randy H <rharmelink@gmail.com >
>To: smf_addin@yahoogroups.com
>Sent: Monday, January 30, 2012 9:15 PM
>Subject: [smf_addin] Re: Morningstar Stock Type & Stock Style
>
>
>
>Unfortunately, the add-in can't extract data from that page, because the data isn't within the source code of the web page. MorningStar is
starting to dynamically generate a lot of their data web pages.
>
>
>On Mon, Jan 30, 2012 at 3:44 AM, V. Shankar <tarakayan@yahoo.com > wrote:
>
>
>>
>>http://quote.morningstar. com/stock/ s.aspx?t= AAPL
>>
>>
>>
>>I was looking at some of the morningstar examples for MF style %s.
>>
>>
>>How do we extract the stock profile details like sector, industry, stock type, stock style from morningstar?
>>
>>
>>Sector
>>Technology
>>Industry
>>Computer Systems
>>Stock Type
>>Cyclical
>>Employees
>>63,300
>>Fiscal Year Ends
>>September
>>Stock Style
>>Large Growth
>>
>>
>>
>
>
>
>
- 4a.
-
local database
Posted by: "leif_85" gondoliere2@web.de leif_85
Tue Jan 31, 2012 1:00 am (PST)
Randy,
I do not know yet, if there are stored fundamental time series by Yahoo/on the web ready for download anyhow. There is a prog "QuantShare", which collects fundamental data from Yahoo primarily but only a snapshot per day/update interval and the time series has to be accumulated locally, as to allow full functionality after a long time only. Quotes Plus delivered complete time series of some 5 years at any moment.
regards,
Leif
- 4b.
-
Re: local database
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Tue Jan 31, 2012 2:06 am (PST)
I'm not aware of downloadable "point in time" data from Yahoo. That's one
reason I use the StockScreen123.com screening product.
On Tue, Jan 31, 2012 at 2:00 AM, leif_85 <gondoliere2@web.de > wrote:
>
> I do not know yet, if there are stored fundamental time series by Yahoo/on
> the web ready for download anyhow. There is a prog "QuantShare", which
> collects fundamental data from Yahoo primarily but only a snapshot per
> day/update interval and the time series has to be accumulated locally, as
> to allow full functionality after a long time only. Quotes Plus delivered
> complete time series of some 5 years at any moment.
>
- 5.
-
A step-by-step example of using some of the new option functions
Posted by: "Randy H" rharmelink@gmail.com rharmelink
Tue Jan 31, 2012 2:00 am (PST)
The purpose of this exercise is not necessarily to create a useful
template, but more to acquaint you with the various option functions
available in the latest version of the add-in. I've uploaded a copy of
the final workbook, after additional formatting was added. It can be
found here:
smfGetOptionQuotes-Example-Table- Building- Exercise. xls
<http://finance.groups.yahoo. >com/group/ smf_addin/ files/Requested% 20Sampl\
es/smfGetOptionQuotes-Example- Table-Building- Exercise. xls
Step 1
Open up a new workbook. The first think we're going to do is create two
basic input fields -- ticker symbol and data source. So, put these
values in the designated cells:
B2: IWM
C2: <=== Ticker Symbol of Underlying Equity
B3: OX
C3: <=== Data Source to use for Retrieving Data
Note that there are two spaces in front of the greater than sign in cell
C2 and C3. These days, I typically use OX (i.e. OptionsXPress) as my
data source. It is more comprehensive than the other data sources (i.e.
Yahoo, Google, MSN), probably because their customers are actually
trading the options, while the other data sources are just reporting
quotes and data. However, of the data sources, OptionsXPress is the
slowest one to retrieve data from. That's why I like to have this as an
input field -- so I can easily toggle between data sources if I want to.
Step 2
Now we're going to create a table of five ITM and five OTM options for
the underlying equity in cell B2. However, we need another input first
-- an expiration date to use for the table. So, another input field is
necessary:
B5: =smfGetOptionExpiry()
C5: <=== Option expiration to use for table below
C8: Tickers
The formula in cell B5 should return the next monthly expiration date.
However, you can manually override that to be any valid expiration date.
I'm just using the formula both to let you know it exists and to get a
default value in the input field.for the rest of this exercise. Cell C8
is just a a column heading.
Step 3
Now let's create some option ticker symbols for the strike prices --
highlight cells C9:C18 and type in this formula:
=smfGetOptionStrikes($B$2,$B$ 5,"Call", $B$3,1)
...and press Ctrl+Shift+Enter to array-enter that formula over the
10-row by 1-column range.
The first parameter is our input ticker symbol. The second parameter is
our input expiration date. The third parameter indicates whether we want
strike prices for puts or calls, although they should be the same for a
given expiration date. In this case, I chose to hard-code a value of
"Call" instead of making it another input field. The fourth parameter is
our input data source. The fifth parameter is telling the function to
generate option symbols for smfGetOptionQuotes() functions for each
strike price. If you leave this parameter off or set it to zero, the
returned values would simply be the numeric strike prices.
Because the formula was entered over a 10-row range, the function will
return five ITM strikes and five OTM strike prices, or however many are
available. If there are only three ITM strike prices, the first two
cells will be left empty. The center of the range should always contain
the first ITM and OTM strike prices.
Step 4
Now we're just going to put in a few item codes, just so the
smfGetOptionQuotes() will actually retrieve something from that list of
ticker symbols we created:
D7: l
E7: b
F7: a
Those correspond with Last price (it's a lower case L, in case you were
wondering), Bid price, and Ask price.
Now, in cell D8, put this formula:
=smfGetOptionQuotes("",D$7,1)
...and copy right-ward as needed. This will create column headings based
on the items codes we entered in row 7.
Step 5
Now let's get some option quotes. In cell D9, enter this formula:
=smfGetOptionQuotes($C9,D$7, ,$B$3)
That says to get the option quote for the ticker symbol in cell C9, the
item code in cell D7, from the input data source listed in cell B3. The
"$" prefixes on row and column designation mean those are absolute
references to rows or columns and will not change when you copy the
formula to other cells. Because we know the option ticker symbols will
always be in column "C" and the item codes will always be in row 7.
Now, simply copy that formula to fill out the table. And we're done with
that table!
Step 6
Now we're going to create a table of eight expiration dates for a given
strike price for the underlying equity in cell B2. However, we need
another input first -- a strike price to use for the table. So, another
input field is necessary:
B20: =smfGetOptionStrikes($B$2,$B$ 5,"Call", $B$3,,2,1)
C20: <=== Strike price to use for table below
C23: Tickers
The formula in cell B20 should return the first ITM strike price.
However, you can manually override that to be any valid strike price.
Again, I'm just using the formula both to let you know it exists and to
get a default value in the input field.for the rest of this exercise.
Cell C23 is just a a column heading. Note that it is the same function
we used in step (3) above. However, this time I left off the parameter
that says to make option ticker symbols out of the strike prices. I also
added the "2,1" for the pDim1 and pDim2 parameters. This tells the
function to return two rows and one column of data, which would be the
first ITM and first OTM strike prices. However, by entering the function
normally into a cell instead of array-entering it over a range, it will
just display the first item of the array. In this case, the first ITM
strike price. But, again, just so I had a good default value to go from.
Step 7
Now let's create some option ticker symbols for the various expiration
dates -- highlight cells C24:C31 and type in this formula:
=smfGetOptionExpirations($B$ 2,$B$3,"Call" ,$B$20)
...and press Ctrl+Shift+Enter to array-enter that formula over the 8-row
by 1-column range.
The first parameter is our input ticker symbol. The second parameter is
our input data source. The third parameter indicates whether we want
option ticker symbols with expiration dates for puts or calls. Again, I
chose to hard-code a value of "Call" instead of making it another input
field. The fourth parameter is telling the function which strike price
to use to generate option symbols for smfGetOptionQuotes() functions for
each expiration date. If you leave the 3rd and 4th parameters off, the
returned values would simply be the expiration dates. They are only
needed when you want the function to generate option ticker symbols.
Step 8
Now we're basically going to repeat step (4) for this table -- just put
in a few item codes, just so the smfGetOptionQuotes() will actually
retrieve something from that list of ticker symbols we created (or you
can just copy them from row 7 down to row 22):
D22: l
E22: b
F22: a
Again, those correspond with Last price, Bid price, and Ask price.
In cell D23, put this formula (very similar to what we put in cell D8,
except that we're referring to a different row of item codes):
=smfGetOptionQuotes("",D$22, 1)
And, again, copy right-ward as needed. This will create column headings
based on the items codes we entered in row 22.
Step 9
Now we're just going to repeat step (5) for this table. In cell D24,
enter this formula:
=smfGetOptionQuotes($C24,D$22, ,$B$3)
...and copy that formula to fill out the table.
All done!
Need to Reply?
Click one of the "Reply" links to respond to a specific message in the Daily Digest.
Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Individual | Switch format to Traditional
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe
Tidak ada komentar:
Posting Komentar