Kamis, 14 Maret 2013

[smf_addin] Digest Number 2544

9 New Messages

Digest #2544
1a
Re: Full Company Name by "Randy Harmelink" rharmelink
3a
Re: VBA and RCHGetYahooHistory by "kristian_dms" kristian_dms
3b
Re: VBA and RCHGetYahooHistory by "Randy Harmelink" rharmelink
4a
problem with yahoo ownership by "weekeewawa" weekeewawa
4b
Re: problem with yahoo ownership by "Randy Harmelink" rharmelink
4c
Re: problem with yahoo ownership by "weekeewawa" weekeewawa
4d
Re: problem with yahoo ownership by "Randy Harmelink" rharmelink
4e
Re: problem with yahoo ownership by "weekeewawa" weekeewawa

Messages

Wed Mar 13, 2013 7:39 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Sigh. Sorry, but this question is one of my pet peeves. I've answered it so
many times...and it is an EXCEL question.

Here's a real example (which could easily have been found by searching the
group for "concatenation"):

http://finance.groups.yahoo.com/group/smf_addin/message/21287

On Wed, Mar 13, 2013 at 7:04 PM, Michael Thomas <thomas91112@yahoo.com>wrote:

>
> What? Please extrapolate for us neophytes...maybe restating the formula
> would be clearer.
> Randy, you provide a tremendous service to us all.
>

Wed Mar 13, 2013 9:00 pm (PDT) . Posted by:

"andyha8" andyha8

Thanks Randy

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Thanks. I've fixed the element definition, from:
>
> Zacks;Zacks Recommendation;=smfStrExtr(smfGetTagContent("
> http://www.zacks.com/stock/quote/~~~~~/detailed-estimates","p",-1,"Zacks
> Rank:"),"-"," ")
>
> ....to:
>
> Zacks;Zacks Recommendation;=smfStrExtr(smfGetTagContent("
> http://www.zacks.com/stock/quote/~~~~~/detailed-estimates","p",-1,"Zacks
> Rank:"),"-","<")
>
> (Just changing that last string from " " to "<", to mark the end of the
> recommendation. When I did my testing, I must have had all single-word
> recommendations.
>
> On Wed, Mar 13, 2013 at 5:51 PM, andyha8 <andyha8@...> wrote:
>
> > Hey Randy, hope you're having a good day.
> >
> > When I punch in GMCR into the SMF-Template-Zacks-Data.xls, I just get
> > "strong", like not "strong buy" or "strong sell", just the one word
> > "strong".
> >
> > I guess I can figure out if it's a buy or sell from other info as in if
> > it's a rank 1 then it must be a buy but just thought I would bring it to
> > your attention.
> >
>

Wed Mar 13, 2013 11:51 pm (PDT) . Posted by:

"kristian_dms" kristian_dms

Im sorry i should have explained this better. I have a list of dates from which i want the opening price of the month of which that date belongs. I refrain from doing it as an array, since grabbing 50+ arrays takes quite some time, so i've made a sub that wil validate the data when i fetch it, and hard-code it to excel to make the workload lighter when i calculate the sheet.

I have done some troubleshooting, and excel does not seem pleased with me using "p" "m" and "O" so i tried removing the "", which let me move on. Not sure what the correct workaround is here.

Specifying the same beginning and end date enables me to get the monthly opening to any historical date - April 1st 2009 will give me the opening price of that month. This can possibly be done more elegant, but i havent found a simpler method yet.

I divide by 100 since the function for some reason returns 100.00 as 10000 on danish equity.

To the target array is only one cell. Ive thought about using dim1:=1 , dim2:=2 but im not that well acquainted with the dim1,dim2 statements.

Hope this all makes better sense. :)

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> I never use R1C processing, so that means nothing to me.
>
> Well, I doubt you will have defined range names of "p", "m", and "O" in
> your workbook, The variable Fetch will be undefined (or have the previous
> definition) if it the IF statement isn't satisfied. Specifying the same
> beginning and ending date to the function will NOT give you any type of
> "monthly" data. I'm not sure why you divide by 100?
>
> I'm not thrilled with seeing 50 potential data retrievals from Yahoo, when
> you should only be doing one to get the array in the first place.
>
> Can you give me an overview of what you're trying to do?
>
> On Wed, Mar 13, 2013 at 2:54 PM, kristian_dms <thorup.dk@...> wrote:
>
> >
> > So i've been setting up a VBA sheet trying to use SMF_addin to fetch
> > historical data for a quantitive analysis and this bit of code keeps
> > bugging me
> >
> > ' sorry - coulden't find the "codebox" option.
> >
> > Sub FetchPrice()
> >
> > dim Fetch as String
> > NumAssets = Worksheets("V").Cells(4, 1)
> >
> > For p = 1 To 50
> >
> > If Worksheets("V").Cells(5 + p, 1) <= DateSerial(Year(Now), Month(Now),
> > Day(Now)) Then
> > Fetch = "=RCHGetYahooHistory(R1C[(3*p)-1] , YEAR(R1C(5+p)) ,
> > MONTH(R1C(5+p)) , DAY(R1C(5+p)) , YEAR(R1C(5+p)) , MONTH(R1C(5+p)) ,
> > DAY(R1C(5+p)) , m , O , 0 , 0 , 1) / 100"
> > *Worksheets("V").Cells(5 + p, 1).FormulaR1C1 = Fetch*
> >
> > End If
> > Next p
> >
> > End Sub
> >
> > I get a "application-defined or object-defined error" on the line i put in
> > italics. I cant seem to figure out what i am doing wrong.
> >
> > Thank you
> >
>

Thu Mar 14, 2013 12:22 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

I don't know how using 50 Internet accesses makes the workload lighter...

I think you'd be better off cloning a copy of the smfPricesByDates()
function and modifying it to get adjusted opening prices instead of
adjusted closing prices when you retrieve your set of dates in one Internet
access...

I think you'd just need to change the parameters of the
RCHGetYahooHistory() function that it invokes.

On Wed, Mar 13, 2013 at 11:51 PM, kristian_dms <thorup.dk@gmail.com> wrote:

> Im sorry i should have explained this better. I have a list of dates from
> which i want the opening price of the month of which that date belongs. I
> refrain from doing it as an array, since grabbing 50+ arrays takes quite
> some time, so i've made a sub that wil validate the data when i fetch it,
> and hard-code it to excel to make the workload lighter when i calculate the
> sheet.
>
> I have done some troubleshooting, and excel does not seem pleased with me
> using "p" "m" and "O" so i tried removing the "", which let me move on. Not
> sure what the correct workaround is here.
>
> Specifying the same beginning and end date enables me to get the monthly
> opening to any historical date - April 1st 2009 will give me the opening
> price of that month. This can possibly be done more elegant, but i havent
> found a simpler method yet.
>
> I divide by 100 since the function for some reason returns 100.00 as 10000
> on danish equity.
>
> To the target array is only one cell. Ive thought about using dim1:=1 ,
> dim2:=2 but im not that well acquainted with the dim1,dim2 statements.
>
> Hope this all makes better sense. :)
>

Wed Mar 13, 2013 11:58 pm (PDT) . Posted by:

"weekeewawa" weekeewawa

hi,

i have encountered problems extracting data from yahoo ownership

how do i extract datas from

1)Top Institutional Holders
2)Top Mutual Fund Holders

thanks!

Thu Mar 14, 2013 12:30 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Try:

=RCHGetHTMLTable("http://finance.yahoo.com/q/mh?s=MMM",">Top
Institutional",-1,"",2)
=RCHGetHTMLTable("http://finance.yahoo.com/q/mh?s=MMM",">Top Mutual
Fund",-1,"",2)

On Wed, Mar 13, 2013 at 11:58 PM, weekeewawa <weekeewawa@yahoo.com> wrote:

>
> i have encountered problems extracting data from yahoo ownership
>
> how do i extract datas from
>
> 1)Top Institutional Holders
> 2)Top Mutual Fund Holders
>

Thu Mar 14, 2013 1:36 am (PDT) . Posted by:

"weekeewawa" weekeewawa

hi!

it seems that the codes that are given is not working as it shows a blank cell. thanks

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
> Try:
>
> =RCHGetHTMLTable("http://finance.yahoo.com/q/mh?s=MMM",">Top
> Institutional",-1,"",2)
> =RCHGetHTMLTable("http://finance.yahoo.com/q/mh?s=MMM",">Top Mutual
> Fund",-1,"",2)
>
> On Wed, Mar 13, 2013 at 11:58 PM, weekeewawa <weekeewawa@...> wrote:
>
> >
> > i have encountered problems extracting data from yahoo ownership
> >
> > how do i extract datas from
> >
> > 1)Top Institutional Holders
> > 2)Top Mutual Fund Holders
> >
>

Thu Mar 14, 2013 1:46 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

You need to array-enter them over a range, not a single cell. The first
cell *is* blank.

On Thu, Mar 14, 2013 at 1:36 AM, weekeewawa <weekeewawa@yahoo.com> wrote:

>
> it seems that the codes that are given is not working as it shows a blank
> cell. thanks
>
> --- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
> > Try:
> >
> > =RCHGetHTMLTable("http://finance.yahoo.com/q/mh?s=MMM",">Top
> > Institutional",-1,"",2)
> > =RCHGetHTMLTable("http://finance.yahoo.com/q/mh?s=MMM",">Top Mutual
> > Fund",-1,"",2)
>

Thu Mar 14, 2013 1:59 am (PDT) . Posted by:

"weekeewawa" weekeewawa

works well!
but there is a problem with %out

it seems that as this is an array, upon entering the % command to %out, it display for example 110% instead of 1.1%

is there a way to overcome that?
thanks

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> You need to array-enter them over a range, not a single cell. The first
> cell *is* blank.
>
> On Thu, Mar 14, 2013 at 1:36 AM, weekeewawa <weekeewawa@...> wrote:
>
> >
> > it seems that the codes that are given is not working as it shows a blank
> > cell. thanks
> >
> > --- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@> wrote:
> > > Try:
> > >
> > > =RCHGetHTMLTable("http://finance.yahoo.com/q/mh?s=MMM",">Top
> > > Institutional",-1,"",2)
> > > =RCHGetHTMLTable("http://finance.yahoo.com/q/mh?s=MMM",">Top Mutual
> > > Fund",-1,"",2)
> >
>

Tidak ada komentar:

Posting Komentar