Minggu, 05 Juni 2011

[smf_addin] Digest Number 1847

Messages In This Digest (8 Messages)

Messages

1a.

Re: Incomplete Update and Missing Values

Posted by: "Bob" bbockjr@yahoo.com   bbockjr

Sat Jun 4, 2011 9:35 am (PDT)



Thanks for the array suggestion - it speeds up the opening of the workbook tremendously. I still get the link error message, though. I tried to do the "replace the hard-coded path...", but it doesn't seem to be present. I've searched for parts of the path, such as "c:\Program Files\" or "SMF Add-In" or even ".xla" and I get a "Text Not Found" error. I am not sharing this workbook with anyone else and all of the formulas are using the same .xla file, so I don't understand why it can find some, but not all. I don't even seem to be getting the blank cells anymore, but I still get the message every time. When I go to "Edit Links" and click Update Values, it always seem to work. It's a pain every time when I open the spreadsheet, but it's also not much of a problem to just click "Continue". A small price to pay for all the effort it saves me.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> See this item from the "Links" area of the group, which has usage tips and
> FAQ:
>
> http://finance.groups.yahoo.com/group/smf_addin/message/11346
>
> Also, you should array-enter the RCHGetYahooQuotes() function instead of
> doing it cell by cell. If you do it cell by cell, each cell requires an
> Internet access -- very slow.
>
> On Wed, Jun 1, 2011 at 11:31 AM, Bob <bbockjr@...> wrote:
>
> > I just began using this SMF Add-In yesterday and it seems to be just what I
> > need. However, when I first load my workbook, which includes about 350 cells
> > spread over 3 worksheets with the formula =RCHGetYahooQuotes(An,"l1") where
> > An contains the stock symbol, it updates for a while, then I get a box which
> > reads "This workbook contains one or more links that cannot be updated. To
> > change the source of the links or attempt to update the links again, click
> > 'Edit Links'. To leave the links as is, click 'Continue'." No matter which I
> > choose, when I get to the spreadsheet, the majority of the cells are fine.
> > There are a few cells, though, that are completely blank. If I double-click
> > on them one at a time to get into edit mode, then press 'Enter', the correct
> > value appears in the cell. The cells that this happens to are different each
> > time I load the same workbook and are scattered throughout the sheet, so it
> > seems that something is causing the updating to terminate early. If I click
> > on Edit Link, the .xla file path is correct and it's in the suggested
> > c:\Program Files\SMF Add-In folder. I'm using Windows 7 Home Premium 32-bit
> > and Excel 2007 on a Dell Inspiron E1505 laptop. The add-in version is 2.1
> > dated 2010.08.02 Any ideas on what to try next?
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (10)
1b.

Re: Incomplete Update and Missing Values

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Sat Jun 4, 2011 9:42 am (PDT)



You need to replace the path that YOU have in the cells, not the one in the
previously mentioned link. And once you've removed the links and saved the
workbook, you shouldn't have the same problem again -- unless you move the
add-in. Or unless you don't have the add-in properly installed and activated
via the add-in manager?

I would avoid the "Edit Links" option altogether. If you need to go there,
you have other problems that need to be resolved.

On Sat, Jun 4, 2011 at 9:35 AM, Bob <bbockjr@yahoo.com> wrote:

> Thanks for the array suggestion - it speeds up the opening of the workbook
> tremendously. I still get the link error message, though. I tried to do the
> "replace the hard-coded path...", but it doesn't seem to be present. I've
> searched for parts of the path, such as "c:\Program Files\" or "SMF Add-In"
> or even ".xla" and I get a "Text Not Found" error. I am not sharing this
> workbook with anyone else and all of the formulas are using the same .xla
> file, so I don't understand why it can find some, but not all. I don't even
> seem to be getting the blank cells anymore, but I still get the message
> every time. When I go to "Edit Links" and click Update Values, it always
> seem to work. It's a pain every time when I open the spreadsheet, but it's
> also not much of a problem to just click "Continue". A small price to pay
> for all the effort it saves me.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (10)
2a.

Re: Sector for ETF?

Posted by: "Market Monk" marketmonk777@yahoo.com   marketmonk777

Sat Jun 4, 2011 10:00 am (PDT)



Hi,

That is true. Yahoo is using the older Morningstar Industry Classification system. It's a 3 level system. I discuss this in a new Yahoo group I created in this message:
http://finance.groups.yahoo.com/group/YSym/message/2

Morningstar has since joined in the classification wars and designed a newer 4 level system.

Dow Jones has their Industry Classification Benchmark (ICB) system which is a 4 level system consisting of 10 Industries, 19 Super Sectors, 41 Sectors, and 114 sub Sectors. You can learn more at their website: http://www.icbenchmark.com/

Standard and Poors along with MSCI Barra created the Global Industry Classification System (GICS) which consists of 10 Sectors, 24 Industry Groups, 68 Industries, and 154 sub-industries. You can learn more at: http://www.mscibarra.com/products/indices/gics/ GICS is aimed at institutional clients as you have to pay a hefty fee to get access to any of the data.

Like I mentioned Yahoo is using the old Morningstar Classification system. I believe it might be the old Hemscott system (Morningstar acquired that division of Hemscott a few years back). Finviz is also using the same L1 and L3 classifications and provide some unique visualizations via charts and heat maps. As well as the ability to drill down into the components for the various groups.

TC2007 is also using this old Morningstar classification system but L2 and L3.

I am not sure what Barchart.com, Stockfetcher.com, Stockcharts.com are using as I can't easily identify which system they are using. And it might be possible they have devised their own but I doubt it.

I prefer the ICB. IMHO it breaks out the various industries in a manner that makes sense. Plus Yahoo covers the DJ US Indexes which map 1 for 1 to the ICB categories. And Dow Jones is doing a great job of getting folks to license and create ETFs based on the various sectors. Here is a screen shot of my spreadsheet I created a while back http://chart.ly/k7cuc5j and have since made significant progress on it.

Now ETFs (1208 of them) have been categorized in their own manner at Yahoo: http://finance.yahoo.com/etf/browser/mkt?ce=1208
I like the system they are using.

Monk

--- In smf_addin@yahoogroups.com, Ron Spruell <hashky@...> wrote:
>
> Just be aware that Yahoo Sectors, Morningstar Sectors, and S&P Sectors are all different.
>
>
>
> ________________________________
> From: KonfusedDesi <mohandesai@...>
> To: smf_addin@yahoogroups.com
> Sent: Monday, May 23, 2011 7:30 PM
> Subject: [smf_addin] Sector for ETF?
>
>
>  
> Is there any of retrieving Sector information for an ETF?
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
3.

Standalone SMF Addin

Posted by: "alexander" alex.life2539@gmail.com   life2539

Sat Jun 4, 2011 9:35 pm (PDT)



Hi,

I've noticed that there are "lite" versions of specific addins. I am interested in a consolidated one if that were possible. I was thinking that if an online copy of the .txt files can be stored online, it could also be looked up as well.

Probably someone has done this before so I am just curious if anybody knows where I can start my search.

Thanks.

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (1)
4a.

AdvFn and S&P 500

Posted by: "iradave" web@iradave.com   iradave

Sat Jun 4, 2011 10:50 pm (PDT)



Hi All,

New user of SMF here using version 2.1.2010.08.02. Most of what I've tried to do has worked great. (Thanks Randy!) I didn't see this in the messages - my apologies if I overlooked this one.

I attempted to do a retrieval of data for each of the S&P 500 stocks using elements 5196, 5296, 5356 & 5616 in the RCHGetElementNumber function. For about 400 of the stocks - no problems retrieving data for the last ten years. But for about 100 of them the 5196-5206 come back with dates but the other fields all come back with errors. Tickers that return errors include ACE, AET, AFL, ALL, AXP, AIG, AMP, and so on.

I can go to the pages on AdvFN and it shows the proper yearly data. For example here is the page for ACE http://www.advfn.com/p.php?pid=financials&btn=annual_reports&mode=&symbol=NYSE%3AACE

Any thoughts on how to figure out what is wrong?

Randy, once again for the great Excel Add-In!

Thanks,

Ira

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
4b.

Re: AdvFn and S&P 500

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Sat Jun 4, 2011 11:02 pm (PDT)



I would quibble with there being "proper yearly data" -- go back and look at
the URL you give for ACE. You'll see that because it's a financial
institution, it has different line items than the statements of a normal
company. For example, there is no line item for ACE called "Total Revenue".

On Sat, Jun 4, 2011 at 9:55 PM, iradave <web@iradave.com> wrote:

>
> I attempted to do a retrieval of data for each of the S&P 500 stocks using
> elements 5196, 5296, 5356 & 5616 in the RCHGetElementNumber function. For
> about 400 of the stocks - no problems retrieving data for the last ten
> years. But for about 100 of them the 5196-5206 come back with dates but the
> other fields all come back with errors. Tickers that return errors include
> ACE, AET, AFL, ALL, AXP, AIG, AMP, and so on.
>
> I can go to the pages on AdvFN and it shows the proper yearly data. For
> example here is the page for ACE
> http://www.advfn.com/p.php?pid=financials&btn=annual_reports&mode=&symbol=NYSE%3AACE
>
> Any thoughts on how to figure out what is wrong?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
5a.

Creating a table of historical prices for 400companies (with RCHGetY

Posted by: "kharatm2001" kharatm2001@yahoo.com   kharatm2001

Sat Jun 4, 2011 11:03 pm (PDT)



Hi All,

I am trying to generate a table for 400 companies, which its rows are dates (for a period of 3 months) and its columns are the companies. The elements of the tables of are the closing prices. So, the value of the cell at i'th column and j'th row is the closing price of company i at date j.

I have some questions:

1- When I use RCHGetYahooHistory(...), I get some integers in the date fields (like 40562) instead of the dates written in correct format. How can I fix this?

2- Do I need to enter the tickers of the companies one by one, or is there a way that I can just put them all in a row, and do it automatically?

Thanks in advance!

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
5b.

Re: Creating a table of historical prices for 400companies (with RCH

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Sat Jun 4, 2011 11:10 pm (PDT)



1. Actually, 40562 is a date. A worksheet cell has a value AND a format.
Just format that value as a date and you should be fine. EXCEL serial dates
are just counts of the number of days since 1899-12-31.

2. RCHGetYahooHistory() can only return data for one company at a time.
Just copy the formula over for each ticker symbol.

However, in the past, if a company has not traded on a given day, there is
no historical quote for that date. I don't know if that is still the case or
not. If it is, that would mean your data could be mis-aligned.

On Sat, Jun 4, 2011 at 11:02 PM, kharatm2001 <kharatm2001@yahoo.com> wrote:

>
> I am trying to generate a table for 400 companies, which its rows are dates
> (for a period of 3 months) and its columns are the companies. The elements
> of the tables of are the closing prices. So, the value of the cell at i'th
> column and j'th row is the closing price of company i at date j.
>
> I have some questions:
>
> 1- When I use RCHGetYahooHistory(...), I get some integers in the date
> fields (like 40562) instead of the dates written in correct format. How can
> I fix this?
>
> 2- Do I need to enter the tickers of the companies one by one, or is there
> a way that I can just put them all in a row, and do it automatically?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
Recent Activity
Visit Your Group
Yahoo! News

Get it all here

Breaking news to

entertainment news

Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Need traffic?

Drive customers

With search ads

on Yahoo!

Need to Reply?

Click one of the "Reply" links to respond to a specific message in the Daily Digest.

Create New Topic | Visit Your Group on the Web
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

Get great advice about dogs and cats. Visit the Dog & Cat Answers Center.

Diposting oleh EXCEL FEED LEARNING di 03.42

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)