9 New Messages
Digest #2544
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.
>
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.
On Wed, Mar 13, 2013 at 7:04 PM, Michael Thomas <thomas91112@
>
> What? Please extrapolate for us neophytes...
> 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.
> >
>
--- In smf_addin@yahoogrou
>
> Thanks. I've fixed the element definition, from:
>
> Zacks;Zacks Recommendation;
> http://www.zacks.
> Rank:")
>
> ....to:
>
> Zacks;Zacks Recommendation;
> http://www.zacks.
> Rank:")
>
> (Just changing that last string from " " to "<"
> 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@
>
> > Hey Randy, hope you're having a good day.
> >
> > When I punch in GMCR into the SMF-Template-
> > "strong"
> > "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
> >
>
I have done some troubleshooting, and excel does not seem pleased with me using "p" "m" and "O" so i tried removing the ""
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@yahoogrou
>
> I never use R1C processing, so that means nothing to me.
>
> Well, I doubt you will have defined range names of "p"
> 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@
>
> >
> > 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'
> >
> > Sub FetchPrice()
> >
> > dim Fetch as String
> > NumAssets = Worksheets("
> >
> > For p = 1 To 50
> >
> > If Worksheets("
> > Day(Now)) Then
> > Fetch = "=RCHGetYahooH
> > MONTH(R1C(5+
> > DAY(R1C(5+p)
> > *Worksheets(
> >
> > End If
> > Next p
> >
> > End Sub
> >
> > I get a "application-
> > 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. :)
>
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(
On Wed, Mar 13, 2013 at 11:51 PM, kristian_dms <thorup.dk@gmail.
> 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 ""
> 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!
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
>
=RCHGetHTMLTable(
Institutional"
=RCHGetHTMLTable(
Fund",-
On Wed, Mar 13, 2013 at 11:58 PM, weekeewawa <weekeewawa@yahoo.
>
> 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
> >
>
it seems that the codes that are given is not working as it shows a blank cell. thanks
--- In smf_addin@yahoogrou
> Try:
>
> =RCHGetHTMLTable(
> Institutional"
> =RCHGetHTMLTable(
> Fund",-
>
> On Wed, Mar 13, 2013 at 11:58 PM, weekeewawa <weekeewawa@
>
> >
> > 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)
>
cell *is* blank.
On Thu, Mar 14, 2013 at 1:36 AM, weekeewawa <weekeewawa@yahoo.
>
> it seems that the codes that are given is not working as it shows a blank
> cell. thanks
>
> --- In smf_addin@yahoogrou
> > Try:
> >
> > =RCHGetHTMLTable(
> > Institutional"
> > =RCHGetHTMLTable(
> > Fund",-
>
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)
> >
>
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@yahoogrou
>
> 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@
>
> >
> > it seems that the codes that are given is not working as it shows a blank
> > cell. thanks
> >
> > --- In smf_addin@yahoogrou
> > > Try:
> > >
> > > =RCHGetHTMLTable(
> > > Institutional"
> > > =RCHGetHTMLTable(
> > > Fund",-
> >
>
Tidak ada komentar:
Posting Komentar