Senin, 29 Juli 2013

[smf_addin] Digest Number 2714

9 New Messages

Digest #2714

Messages

Sun Jul 28, 2013 7:51 am (PDT) . Posted by:

"Paulo" palminha

Sorry Randy to ask you about the same issue but I have the same kind of problem but I have the late version instaled as you can see bellow:

Stock Market Functions add-in, Version 2.1.2013.06.28 (C:\Program Files\SMF Add-In; 1)
(Dollars in millions, except per share data) Stock Performance
Stock Market 52-Week Price Change
Ticker Company Price Cap. TEV Low High 3 Mo. 6 Mo. 12 Mo.

UNH UnitedHealth Group Inc $72.62 $74,799 $80,620 $50.32 $73.15 Error Error Error
BDX Becton Dickinson & Co 103.52 20,109 21,810 74.10 104.98 Error Error Error
BMY Bristol-Myers Squibb Co 43.93 72,045 77,990 30.64 49.57 Error Error Error

Is there any more I can do to solve this problem?

Thank you very much for your kind help!

Best Regards
Paulo

--- In smf_addin@yahoogroups.com, NicholasDavid <nicholasdavid@...> wrote:
>
> Randy,
>
> All's well. My problem was that I had two locations on my computer for SMF Add-In.
> One under C:\programs&#92;smf add-in and one under c:\documents&#92;smf add-in.
>
> When I downloaded the new file, it went into c:\documents&#92;smf add-in.
>
> Thanks for your patience. Everything works now. David
>
> ----- Original Message -----
> From: "Randy Harmelink" <rharmelink@...>
> To: "smf addin" <smf_addin@yahoogroups.com>
> Sent: Monday, July 30, 2012 11:29:00 AM
> Subject: Re: [smf_addin] =RCHGetElementNumber()
>
>
>
>
>
>
> You still have the old element definition. Did you put the replacement file in the correct folder -- C:\Program Files\SMF Add-In?
>
> On Mon, Jul 30, 2012 at 9:26 AM, NicholasDavid < nicholasdavid@... > wrote:
> > I get the following:
> >
> > =RCHGetElementNumber("Version&quot;)
> >
> > Stock Market Functions add-in, Version 2.1.2012.04.07 (C:\Program Files\SMF Add-In; 1)
> >
> > =RCHGetElementNumber("MMM";,27)
> >
> > Error
> >
> > =smfGetAParms(27)
> >
> > MSN;3 Month % Price Change; http://moneycentral.msn.com/investor/research/sreport.asp?CR=1&AF=1&IH=1&AIE=1&AIR=1&FRH=1&FRK=1&ISA=1&ISQ=1&BSA=1&BSQ=1&CFA=1&CFQ=1&TYS=1&ITT=1&ITP=1&Type=Equity&Symbol=~~~~~;1;STOCK PRICE HISTORY;LAST 3 MONTHS; ; ;0;0;0;0
>

Sun Jul 28, 2013 12:09 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

I'm assuming the problem you have is the 3-month, 6-month, and 12-month
performance amounts from MSN? If so, they are now obsolete. When MSN
redesigned their stock print page earlier this year, they dropped the
performance section, so those data items are no longer available. FinViz
does have them -- see the item in the LINKS area about how to pick them up
efficiently.

On Sun, Jul 28, 2013 at 7:51 AM, Paulo <palminha@yahoo.com> wrote:

> Sorry Randy to ask you about the same issue but I have the same kind of
> problem but I have the late version instaled as you can see bellow:
>
> Stock Market Functions add-in, Version 2.1.2013.06.28
> (C:\Program Files\SMF Add-In; 1)
> (Dollars in millions, except per share data)
> Stock Performance
> Stock Market
> 52-Week Price Change
> Ticker Company Price Cap. TEV
> Low High 3 Mo. 6 Mo. 12 Mo.
>
> UNH UnitedHealth Group Inc $72.62 $74,799
> $80,620 $50.32 $73.15 Error
> Error Error
> BDX Becton Dickinson & Co 103.52 20,109
> 21,810 74.10 104.98 Error Error
> Error
> BMY Bristol-Myers Squibb Co 43.93 72,045
> 77,990 30.64 49.57 Error Error
> Error
>
> Is there any more I can do to solve this problem?
>

Sun Jul 28, 2013 12:46 pm (PDT) . Posted by:

"P Palminha" palminha

Yes Randy you are right, OK I will try to find out how it work with FinViz.
 
But also the following formulas are not working:
=RCHGetElementNumber(Ticker, 288) to =RCHGetElementNumber(Ticker, 292)
 
and many formulas from MSN Financial Statements using =RCHGetTableCell like the one bellow are also giving me error
 
=RCHGetTableCell("http://investing.money.msn.com/investments/stock-income-statement?stmtView=Ann&Symbol="&Ticker, 5,"Selling/General/Administrative Expenses, Total")
 
are they also obsolete? can I find other alternatives because I have many sheets using this kind of sheet to get IS, BS & CF from last 5Years and last 5 Quarters to value each company.
 
Thank you and best regards
Paulo 

________________________________
DE: Randy Harmelink <rharmelink@gmail.com>
Para: smf_addin@yahoogroups.com
Enviadas: Domingo, 28, Julho 2013 20:09:15
Assunto: Re: [smf_addin] Re: =RCHGetElementNumber()


 

I'm assuming the problem you have is the 3-month, 6-month, and 12-month performance amounts from MSN? If so, they are now obsolete. When MSN redesigned their stock print page earlier this year, they dropped the performance section, so those data items are no longer available. FinViz does have them -- see the item in the LINKS area about how to pick them up efficiently.

On Sun, Jul 28, 2013 at 7:51 AM, Paulo <palminha@yahoo.com> wrote:
Sorry Randy to ask you about the same issue but I have the same kind of problem but I have the late version instaled as you can see bellow:
>
>                Stock Market Functions add-in, Version 2.1.2013.06.28 (C:\Program Files\SMF Add-In; 1)
>(Dollars in millions, except per share data)                                                                            Stock Performance
>                                Stock           Market                          52-Week                         Price Change
>Ticker          Company         Price           Cap.            TEV             Low             High            3 Mo.           6 Mo.           12 Mo.
>
>UNH             UnitedHealth Group Inc          $72.62          $74,799                 $80,620                 $50.32          $73.15          Error           Error           Error
>BDX             Becton Dickinson & Co           103.52          20,109          21,810          74.10           104.98          Error           Error           Error
>BMY             Bristol-Myers Squibb Co         43.93           72,045          77,990          30.64           49.57           Error           Error           Error
>
>Is there any more I can do to solve this problem?
>
 

Sun Jul 28, 2013 1:03 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

On Sun, Jul 28, 2013 at 12:46 PM, P Palminha <palminha@yahoo.com> wrote:

>
> But also the following formulas are not working:
> =RCHGetElementNumber(Ticker, 288) to =RCHGetElementNumber(Ticker, 292)
>

Remember -- "Not working" is not a good description of a problem. It gives
me no clue as to what to look for.

However, I just tried them with MMM and IBM, and they worked fine for me. I
was getting values of "NA", but that is what MSN had posted on the web
pages.

> and many formulas from MSN Financial Statements using =RCHGetTableCell
> like the one bellow are also giving me error
>
> =RCHGetTableCell("
> http://investing.money.msn.com/investments/stock-income-statement?stmtView=Ann&Symbol="&Ticker,
> 5,"Selling/General/Administrative Expenses, Total")
>
> are they also obsolete? can I find other alternatives because I have many
> sheets using this kind of sheet to get IS, BS & CF from last 5Years and
> last 5 Quarters to value each company.
>

Not obsolete, but in their redesign of the web site earlier this year, they
changed the financial statements web pages. For example, that line item now
has a new label on it:

=RCHGetTableCell("
http://investing.money.msn.com/investments/stock-income-statement?stmtView=Ann&Symbol="&B3,
5,"Selling,General and Administrative";)

MSN does this type of "update" about once a year. You'd think they'd have
better things to do. Rarely have I seen the changes be for the better.

Sun Jul 28, 2013 10:52 am (PDT) . Posted by:

"PeteA" option2z

2013 Excel has a much better pivot table, and quite a few enhancements for
formatting, as well as much better graphics. But since nearly everything I
have was developed with 2010, and I still want it to run in 2010, I use them
sparingly. My perceptions (not test results) is that 2013 is a bit faster.
It's been long enough that I'm already forgetting; but good enough that I
use 2013 over 2010 most of the time-and each is the same click away.

I think the MDI removal was caused by a bunch of younger programmers at
Microsoft finding the coding difficult, and THEY didn't see the value of it.
But ANYONE who does much complex excel work (i.e., professionals) would very
likely. They could have asked before removing a key feature, and then
trying to sell it as an advantage.

Some reasons for me upgrading:
1. I try to keep up with the latest code base, and it has ALWAYS been an
improved environment, but often with the cost of a mild (for me, anyway)
learning curve.
2. All sorts of old bugs tend to disappear (otoh, there are some new
ones-good to keep the old version installed too)
3. It has gotten progressively more reliable (screens of death; lockups;
broken files, etc.)
4. New, very useful, features appear (max size of spreadsheet, filtering
and conditional formatting come to mind from previous editions).
5. Available help from MS on newer tools
6. I got a third computer and licensing is only $98 per year for up to 5
computers. Much cheaper than single licenses. Also get the rest of the
office suite which I use too.
7. Installation is trivial (10 minutes on the Internet)
8. I'm a tech weenie. Can hardly stand the thought of operating in
antique space. SO many people are stuck in the old .xls versions, with XP.
I feel for them. Did it WAY too long, and love the newer Windows, although
it was hard to adapt to Win 8 (blended interface for tablets does NOT belong
on a large monitor machine. But it is EASY to turn off, and the search part
pays for it alone. MHO of course.

Thanks!
Pete Asprey
<http://www.sundognorth.com/PeteAJustSayin/?page_id=229>


Sun Jul 28, 2013 2:56 pm (PDT) . Posted by:

"okse26_26" okse26_26

Hello,
Since the next day my created SMF file didn't update, I found the following advice regarding the IE: http://finance.groups.yahoo.com/group/smf_addin/message/13523 . However, this is what I get (BTW, this is my Home computer, no corp. environment apart from the Remote desktop when I need to work from home).
For curiosity, I went back to Control Panel->Internet Options->Under Browsing History & click Settings.
> For some reason, the radio button keeps reverting to "Automatically". It doesn't stay on "Every time I visit website". How to make it stay there?

Would anyone have a clue what's going on?
Should I try to call HP (PC manufacturer) or the ATT (the internet provider) about this issue? Or maybe try posting my question on some forum about Win7 (any advice what forum; I'm not a techie)

When responding to my random questions, Randy wrote this: "Not updating the NEXT day sounds very funky. I don't think that would be an IE cache problem. But it could be the result of the add-in having saved the page the prior day, if you didn't exit and restart EXCEL (then it would still be saved in the VBA environment)."

Could anyone interpret his 3rd sentence? Maybe that's my problem and it would work with the "Automatically" button on, I talk about above. When I created the spreadsheet after downloading the SMF file and unzipping it, I played around with formulas and then closed down the "RCH...Definitions"; file, my newly created spreadsheet and shut down the PC. I opened the spreadsheet the next day to see that Last Price wouldn't update. At what point of the process was I supposed to exit the spreadsheet and restart Excel? Does the restarting Excel mean restarting the PC?

Speaking of the Last Price, Randy said that I'm using MSN which is not a good source anymore and instead I should use RCHGetYahooQuotes(). Can you tell me the # of this element on the "RCH...Definitions"; file? I cannot locate it.

That's all for now. Thank you

Sun Jul 28, 2013 4:55 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

On Sun, Jul 28, 2013 at 2:56 PM, okse26_26 <okse26_26@yahoo.com> wrote:

> Hello,
> Since the next day my created SMF file didn't update, I found the
> following advice regarding the IE:
> http://finance.groups.yahoo.com/group/smf_addin/message/13523 . However,
> this is what I get (BTW, this is my Home computer, no corp. environment
> apart from the Remote desktop when I need to work from home).
> For curiosity, I went back to Control Panel->Internet Options->Under
> Browsing History & click Settings.
> > For some reason, the radio button keeps reverting to "Automatically". It
> doesn't stay on "Every time I visit website". How to make it stay there?
>
> Would anyone have a clue what's going on?
> Should I try to call HP (PC manufacturer) or the ATT (the internet
> provider) about this issue? Or maybe try posting my question on some forum
> about Win7 (any advice what forum; I'm not a techie)
>

Hopefully, someone will be able offer advice there, because I haven't a
clue. Have you tried doing a Google search on the issue? It might lend some
insight.

> When responding to my random questions, Randy wrote this: "Not updating
> the NEXT day sounds very funky. I don't think that would be an IE cache
> problem. But it could be the result of the add-in having saved the page the
> prior day, if you didn't exit and restart EXCEL (then it would still be
> saved in the VBA environment)."
>
> Could anyone interpret his 3rd sentence? Maybe that's my problem and it
> would work with the "Automatically" button on, I talk about above. When I
> created the spreadsheet after downloading the SMF file and unzipping it, I
> played around with formulas and then closed down the "RCH...Definitions";
> file, my newly created spreadsheet and shut down the PC. I opened the
> spreadsheet the next day to see that Last Price wouldn't update. At what
> point of the process was I supposed to exit the spreadsheet and restart
> Excel? Does the restarting Excel mean restarting the PC?
>

Restarting EXCEL does NOT mean restarting the PC. It just means closing
down the EXCEL program. When you start EXCEL, it creates two environments
-- one for workbooks, and one for VBA. The VBA environment is where macros
and add-ins reside. So that VBA environment is reset/initialized whenever
EXCEL is restarted.

The definitions file is just a workbook. It has nothing to do with the
add-in itself other than documenting what each element number is. You could
delete it off your computer and the add-in wouldn't even know it was gone.

The add-in has a an array in the VBA environment where it can hold up to
1000 web pages. When you extract data from a web page, it is first stored
in that array, and then the data extraction is pulled from that saved web
page. That's why F9 will never update a data element -- because you would
always be extracting the data from that saved web page, which can't change.
That's what the smfForceRecalculation macro is for -- it empties out that
array of 1000 save web pages. So, the next time a data element is needed,
it needs to get it from the Internet, save it, and then extract the data
element from the fresh copy of the web page.

The other way to purge that array of 1000 web pages would be to exit EXCEL
and start it up again. Because, as I said, when EXCEL starts up, it creates
a new VBA environment, reloading all add-ins from scratch. The
smfForceRecalculation just allows you to be able to do the reset of the
1000 web page storage area without needing to do that.

> Speaking of the Last Price, Randy said that I'm using MSN which is not a
> good source anymore and instead I should use RCHGetYahooQuotes(). Can you
> tell me the # of this element on the "RCH...Definitions"; file? I cannot
> locate it.
>

Take a look at the "Documentation" folder of the FILES area of the group.
It has the documentation on all (well, most) of the functions of the add-in.

For example, to get the current price of ticker "MMM":

=RCHGetYahooQuotes("MMM","l1")

One advantage of the RCHGetYahooQuotes() function is that you can
array-enter it over a range and retrieve numerous data items on up to 200
stocks, with a single Internet access. So it would be MUCH faster than
using RCHGetElementNumber() to get the individual items on the 200 stocks,
because you would need to access AT LEAST 200 different web pages. Maybe
more. And actually retrieving web pages from the Internet is the real
bottleneck on the speed of the add-in. I've had people report going from
5-10 minutes to get their data to only a few seconds, simply by changing
over to this function, array-entered.

However, if you don't array-enter it over a range, and just use the formula
cell by cell, each invocation of the function would require its own
Internet access.

Sun Jul 28, 2013 5:24 pm (PDT) . Posted by:

"okse26_26" okse26_26

> For example, to get the current price of ticker "MMM":
>
> =RCHGetYahooQuotes("MMM","l1")
>
> One advantage of the RCHGetYahooQuotes() function is that you can
> array-enter it over a range and retrieve numerous data items on up to 200
> stocks, with a single Internet access. So it would be MUCH faster than
> using RCHGetElementNumber() to get the individual items on the 200 stocks,
> because you would need to access AT LEAST 200 different web pages.

Thank you, Randy, for a quick reply.
No, I didn't Google for the IE part yet, I'll wait for tomorrow.

What I did is I started over: Deleted my spreadsheet and SMF file and started over again. I did notice that I missed to close the Excel completely the 1st after adding Smf via add-ins menu (author of the article on the SA didn't mention it either). Anyway, this time I did. I'll wait until tomorrow and see if it will update the last price. If not, then I've definitely have a problem with the IE radio button :-(.

BTW, these kind of instructions here http://finance.groups.yahoo.com/group/smf_addin/message/124 are super helpful for me. Here's the question though. So, if I find an element that's available via Yahoo vs. the same element found on the "RCH...Definitions"; file, would you advise to use Yahoo data instead e.g.MSN, because extractions will be much easier? Also, wouldn't M* data be more dependable than Yahoo or is it just a user's preference?

Thank you again.

Sun Jul 28, 2013 5:43 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

On Sun, Jul 28, 2013 at 5:24 PM, okse26_26 <okse26_26@yahoo.com> wrote:

> BTW, these kind of instructions here
> http://finance.groups.yahoo.com/group/smf_addin/message/124 are super
> helpful for me.

That's why I do 'em. :)

If you use options, there's a similar one for options. It goes over all the
various option functions (i.e. retrieve available expiration dates,
available strike prices, quotes, etc,). Check the LINKS area of the group
for that one.

Here's the question though. So, if I find an element that's available via
> Yahoo vs. the same element found on the "RCH...Definitions"; file, would you
> advise to use Yahoo data instead e.g.MSN, because extractions will be much
> easier? Also, wouldn't M* data be more dependable than Yahoo or is it just
> a user's preference?
>

M* is actually very difficult these days, because they've been changing
over to dynamically generated web pages. Since the add-in extracts data
from the source code of a web page, a dynamic web page contains none of
that data to extract, because it's displayed AFTER the web page is loaded.

However, I did discover the "source" web pages they use to load the dynamic
web pages, but the data is all over the place. Which means you may need to
access 20 or 30 web pages (and thus 20 or 30 Internet accesses) to get the
data for a single ticker symbol. VERY slow.

So, speedwise, RCHGetYahooQuotes() is definitely the best. But if you
prefer MSN or M* because the data may be more accurate, or more up-to-date,
you just have to realize you're sacrificing speed for that accuracy. In the
end, a mix may be best. For example, the dividend information returned by
RCHGetYahooQuotes() are from the trailing 12 months. Most people prefer the
forward dividend yield (expected to be paid out over the NEXT 12 months).
That can only be achieved by scraping it off a web page.

You may want to look at the FinViz items in the LINKS area of that group.
That is another service where a number of data items can be retrieved on a
number of stocks with a single Internet access.
Diposting oleh EXCEL FEED LEARNING di 01.03

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)