Minggu, 12 Januari 2014

[smf_addin] Digest Number 2924

13 New Messages

Digest #2924
4a
5b

Messages

Sat Jan 11, 2014 5:08 am (PST) . Posted by:

mrothaus

I modified my spreadsheet in pretty simple fashion after all. The simplified example is as follows:


Column A = stock symbols
Column B = "=IF(<cell in A>=0,0,<cell in Z>)"


Column Y = "=IF(<cell in A>=0,"DUMMY",<cell in A>)"
Column Z = array-entered formula of symbols in Y


if you don't want to hard-code "DUMMY" in column Y, you can have it reference a cell instead. Column Y and Column Z could be anywhere. Best if the columns line up in the same rows to make the formulas easier to read and understand.


Now I can sort, insert, delete columns A and B without any problems, and I can leave a symbol blank in A if I want.


Nice!

Sat Jan 11, 2014 8:39 am (PST) . Posted by:

altaclip

Happy New Year everyone. May 2014 be rewarding in every way and bring you health and happiness.


I've been using smf for over 6 months now. It is an amazing tool and I am very grateful for everything that Randy has done to help individual investors.


Many new users of smf ask the same questions and the current yahoo groups format does not work well for organizing information.


With that said I thought I'd post some ideas on how I use smf in case they help others.Feel free to offer your thoughts on how you use smf and your current challenges/suggestions.


I will post a seperate reply to this topic for each tip/idea.
Happy investing!
Alberta Clipper

Sat Jan 11, 2014 10:13 am (PST) . Posted by:

altaclip

CLIP TIP #1 - VLOOKUP and MULTIPLE WORKBOOKS. Build a workbook or series of workbooks/tabs for your system.


Don't try and put everything into one massive spreadsheet to track quotes, your portfolio, research, quote lookup, ratings, etc.


I tried this initially and the workbook just gets too bogged down, freezes or worse.


I have various tabs or separate workbooks to integrate data from varoius sources. The excel function VLOOKUP is your best friend for this. If you are not familiar with it do some research and start creating some test workbooks to get profiecient.


The pros of this are YOU can control what to update and when. For example I like to pull Zacks information but many of the ratings and info is only updated once per day in the middle of the night. Anyone who has tried refreshing Zacks elements and received many errors is likely trying to do so while they are updating.


Worse if you use your system during market hours and try to refresh everything you will freeze up your system for many mins.


I visit the Zacks site late every night and check their #1 Zack Rank list. There is no point doing a daily update of your Zacks elements until Zacks updates them after hours. Also if you're receiving a lot of Zacks errors it's most likely due to being in the middle of an update and/or website mtce. Wait and return. Note for Zack rankings to work and some of the elements you might need to be a premium subscriber.


Other components of my system include:
Main stocks database
Finviz data including real time quotes (finviz elite subscriber) via smfupdatedownloadtable ("UDT") Updatedownloadtable is one of your best smf friends! Search the yahoo group and test to get used to using it if you are not familiar. Canadian TSX listed UDT. This is a work in progress because I can never find an all in once good source of earnings estimates, broker ratings, fundamental data, dividend data, etc. for TSX listed stocks. (Please reply if you have a UDT for TSX that works well). Yahoo quotes via RCHGETYAHOOQUOTES. Again search the yahoo group if you are not familiar Zacks UDT as discussed above Other tabs to calculate IRR, summarize performance, portfolio breakdown by sector, size, etc. The main lessons from CLIP TIP#1 don't try and put everything into one worksheet and become familiar with VLOOKUP to find/update data. This allows you to control when certain components are updated and provideds more options for filtering/sorting your main database of stocks.


Happy investing!
Alberta Clipper

Sat Jan 11, 2014 9:34 am (PST) . Posted by:

"devropr" devropr

Randy how can I retrieve Yahoo's Analyst Opinion? Example of ARCC:

Recommendation Summary*

Mean Recommendation (this week): 1.9

Sat Jan 11, 2014 9:54 am (PST) . Posted by:

mikemcq802

=smfGetTagContent("http://finance.yahoo.com/q/ao?s=ARCC+Analyst+Opinion","td",1,">Mean Recommendation (this week):")

The value (in this case 1.9) is in a "td" tag on that page. That's where the "td" comes from in the function

It is the first td tag after the search term - hence the "1" as the 3rd parameter.

The url can be constructed using concatenation to allow substitution of the ticker symbol.

Sat Jan 11, 2014 10:03 am (PST) . Posted by:

"devropr" devropr

I tried the concatenation, but syntax is wrong?:

"http://finance.yahoo.com/q/ao?s="& A2+Analyst+Opinion,"td

--- In smf_addin@yahoogroups.com, <mikemcq802@...> wrote:
>
> =smfGetTagContent("http://finance.yahoo.com/q/ao?s=ARCC+Analyst+Opinion","td",1,">Mean Recommendation (this week):")
>
> The value (in this case 1.9) is in a "td" tag on that page. That's where the "td" comes from in the function
>
> It is the first td tag after the search term - hence the "1" as the 3rd parameter.
>
> The url can be constructed using concatenation to allow substitution of the ticker symbol.
>

Sat Jan 11, 2014 10:21 am (PST) . Posted by:

"Randy Harmelink" rharmelink

You'd have three strings to concatenate:

"http://finance.yahoo.com/q/ao?s=" & A2 & "+Analyst+Opinion"

You really don't need the third part. You could just go to:

"http://finance.yahoo.com/q/ao?s=" & A2

On Sat, Jan 11, 2014 at 11:03 AM, devropr <devropr@cox.net> wrote:

> I tried the concatenation, but syntax is wrong?:
>
> "http://finance.yahoo.com/q/ao?s="& A2+Analyst+Opinion,"td
>
> --- In smf_addin@yahoogroups.com, <mikemcq802@...> wrote:
> >
> > =smfGetTagContent("http://finance.yahoo.com/q/ao?s=ARCC+Analyst+Opinion","td",1,">Mean
> Recommendation (this week):")
> >
> > The value (in this case 1.9) is in a "td" tag on that page. That's
> where the "td" comes from in the function
> >
> > It is the first td tag after the search term - hence the "1" as the 3rd
> parameter.
> >
> > The url can be constructed using concatenation to allow substitution of
> the ticker symbol.
>

Sat Jan 11, 2014 10:21 am (PST) . Posted by:

mikemcq802

Not sure why my previous response to concatenation hasn't appeared.

But, see Randy's FAQ about this to discover your error.

http://groups.yahoo.com/neo/groups/smf_addin/conversations/topics/22530

Sat Jan 11, 2014 10:22 am (PST) . Posted by:

mikemcq802

Yes, your syntax is wrong.

It should be something like

"http://finance..." & A2 & "Analyst ....","td",...

To do what you are intending means you are concatenating 3 different pieces of info together. The first is a literal of the first part of the "http". The second is the cell (A2). The third is the final part of the http string.

Each piece must have a concatenation operator between - you were missing one (and missing a quote). You can also use the CONCATENATE function.

Sat Jan 11, 2014 10:40 am (PST) . Posted by:

"devropr" devropr

bingo Thank you Mike & Randy.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> You'd have three strings to concatenate:
>
> "http://finance.yahoo.com/q/ao?s=" & A2 & "+Analyst+Opinion"
>
> You really don't need the third part. You could just go to:
>
> "http://finance.yahoo.com/q/ao?s=" & A2
>
> On Sat, Jan 11, 2014 at 11:03 AM, devropr <devropr@...> wrote:
>
> > I tried the concatenation, but syntax is wrong?:
> >
> > "http://finance.yahoo.com/q/ao?s="& A2+Analyst+Opinion,"td
> >
> > --- In smf_addin@yahoogroups.com, <mikemcq802@> wrote:
> > >
> > > =smfGetTagContent("http://finance.yahoo.com/q/ao?s=ARCC+Analyst+Opinion","td",1,">Mean
> > Recommendation (this week):")
> > >
> > > The value (in this case 1.9) is in a "td" tag on that page. That's
> > where the "td" comes from in the function
> > >
> > > It is the first td tag after the search term - hence the "1" as the 3rd
> > parameter.
> > >
> > > The url can be constructed using concatenation to allow substitution of
> > the ticker symbol.
> >
>

Sat Jan 11, 2014 1:32 pm (PST) . Posted by:

lawrence.leesh

Thanks Mike. I thought I saw FY (12/13) from http://investing.money.msn.com/investments/financial-results/?symbol=tmk http://investing.money.msn.com/investments/financial-results/?symbol=tmk


Sat Jan 11, 2014 2:30 pm (PST) . Posted by:

irebian

Great site,Randy.
I'm trying to access the price on data like
"http://www.ariva.de/mfs_meridian_funds_-_global_growth_fund-fonds"
using RCHGetTableCell but haven't succeeded, possibly due to the irregularity of the page.
Help would be appreciated.
thanks

Sat Jan 11, 2014 4:10 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

This appears to work:

=smfStrExtr(smfGetTagContent("
http://www.ariva.de/mfs_meridian_funds_-_global_growth_fund-fonds","td",1,"""Forumsuche"""),"~","
")

On Sat, Jan 11, 2014 at 3:30 PM, <irebian@yahoo.com.au> wrote:

>
> I'm trying to access the price on data like
> "http://www.ariva.de/mfs_meridian_funds_-_global_growth_fund-fonds"
> using RCHGetTableCell but haven't succeeded, possibly due to the
> irregularity of the page.
> Help would be appreciated.
> thanks
>
Diposting oleh EXCEL FEED LEARNING di 00.44

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)