Minggu, 16 Oktober 2011

[smf_addin] Digest Number 1987

Messages In This Digest (6 Messages)

1a.
Re: Fair Value - Automated.xls From: osmangulseven@yahoo.com
2a.
Re: Get data for over 3000 companies? From: dguillett1
2b.
Re: Get data for over 3000 companies? From: Randy Harmelink
2c.
Re: Get data for over 3000 companies? From: Ron Spruell
3a.
Re: =RCHGetTableCell() From: Pete
3b.
Re: =RCHGetTableCell() From: NicholasDavid

Messages

1a.

Re: Fair Value - Automated.xls

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

Sat Oct 15, 2011 4:21 am (PDT)





--- In smf_addin@yahoogroups.com, "kiwi2y_98" <kiwi2y_98@...> wrote:
>
> Great work!
> Question -- Are the exponents missing in the calculations,
> rows 23 thru 27, columns A thru J ?
> thanks again.
>

No, they are not. The upper boundary includes the book value, and lower boundary does not. Nevertheless, there is a minor issue with the file. I should have automated the bond yields. It says 4.49% (30 yr AAA corporate bond yield) on the file, but that is static. Yahoo Finance has the data on corporate bond yields as well as treasuries:

http://finance.yahoo.com/bonds/composite_bond_rates

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

Re: Get data for over 3000 companies?

Posted by: "dguillett1" dguillett1@gmail.com   donaldb36

Sat Oct 15, 2011 5:19 am (PDT)



Without using the smf addin and with xl2003 and just clicking on the link HERE on the email, it brings up the .csv file with the list for 6815 almost instantly. How do I just call that from an excel macro?

Don Guillett
SalesAid Software
dguillett1@gmail.com

From: Randy Harmelink
Sent: Friday, October 14, 2011 11:42 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Get data for over 3000 companies?

I think it's an EXCEL 2003 problem. I just got rid of the function and kept just:

="http://finviz.com/export.ashx?v=151&T="&smfJoin(A8:A56,",")&"&c="&smfJoin(B6:C6,",")

That string is exactly 255 bytes long. I doubt that is a coincidence. My bet would be that if that string gets longer than 255 bytes, EXCEL 2003 has a problem. Try these two functions:

=LEN("http://finviz.com/export.ashx?v=151&T="&smfJoin(A8:A56,",")&"&c="&smfJoin(B6:C6,","))
=LEN("http://finviz.com/export.ashx?v=151&T="&smfJoin(A8:A66,",")&"&c="&smfJoin(B6:C6,","))

...and see if you get an error on the second LEN() function.

If that is the problem, the easiest solution would be to drop the "t=" parameter and just get a list of all ticker symbols. But that will make your VLOOKUP() processing a little slower.

On Fri, Oct 14, 2011 at 9:27 PM, Ron Spruell <hashky@yahoo.com> wrote:

I am using the beta version.
Stock Market Functions add-in, Version 2.1.2011.04.03 (C:\Program Files\SMF Add-In; 1)

I installed the UDF sfJoin and tried it. No change. 47 Tickers is the working limit. After that everything turns to #Value!

Here are 3 files. The first is a pdf with a Ticker input Range of A8:A56 with values. The second is a pdf with a Ticker input Range of A8:A57 which has the #VALUE! output. The third is the xls file.

Where should I go from here?

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

Re: Get data for over 3000 companies?

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

Sat Oct 15, 2011 8:30 am (PDT)



Just process it the same way you do the Yahoo Quotes CSV file in you
XLTrader workbooks. My smfGetCSVFile() just calls the RCHGetYahooQuotes()
function, so all they do is parse out a CSV file. I believe your workbooks
import the file and then use the "text to columns" function?

On Sat, Oct 15, 2011 at 5:19 AM, dguillett1 <dguillett1@gmail.com> wrote:

>
> Without using the smf addin and with xl2003 and just clicking on the link
> HERE on the email, it brings up the .csv file with the list for 6815 almost
> instantly. How do I just call that from an excel macro?
>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (23)
2c.

Re: Get data for over 3000 companies?

Posted by: "Ron Spruell" hashky@yahoo.com   hashky

Sat Oct 15, 2011 11:06 am (PDT)



After reading several hours of posts on the Internet, I have determined that you are correct.  It has something to do with the way Excel 2003 determines the data type. 

Excel, by default, looks at the first 8 rows and determines the data type.  If the cell contains more than 255 characters in any of the first 8 rows, then Excel thinks it is importing a memo field. It has something to do with IMEX=.  

I make a few changes to the registry that were suggested in the posts.  I couldn't get the field type to change to allow more than 255 characters.

I think the easy answer may be Excel 2010. 

>________________________________
>From: Randy Harmelink <rharmelink@gmail.com>
>To: smf_addin@yahoogroups.com
>Sent: Friday, October 14, 2011 11:42 PM
>Subject: Re: [smf_addin] Get data for over 3000 companies?
>
>
> 
>I think it's an EXCEL 2003 problem. I just got rid of the function and kept just:
>
>="http://finviz.com/export.ashx?v=151&T="&smfJoin(A8:A56,",")&"&c="&smfJoin(B6:C6,",")
>
>That string is exactly 255 bytes long. I doubt that is a coincidence. My bet would be that if that string gets longer than 255 bytes, EXCEL 2003 has a problem. Try these two functions:
>
>=LEN("http://finviz.com/export.ashx?v=151&T="&smfJoin(A8:A56,",")&"&c="&smfJoin(B6:C6,","))
>=LEN("http://finviz.com/export.ashx?v=151&T="&smfJoin(A8:A66,",")&"&c="&smfJoin(B6:C6,","))
>
>...and see if you get an error on the second LEN() function.
>
>If that is the problem, the easiest solution would be to drop the "t=" parameter and just get a list of all ticker symbols. But that will make your VLOOKUP() processing a little slower.
>
>
>On Fri, Oct 14, 2011 at 9:27 PM, Ron Spruell <hashky@yahoo.com> wrote:
>
>
>>
>>I am using the beta version. 
>>Stock Market Functions add-in, Version 2.1.2011.04.03 (C:\Program Files\SMF Add-In; 1)
>>
>>
>>I installed the UDF sfJoin and tried it.  No change.  47 Tickers is the working limit.  After that everything turns to #Value!
>>
>>
>>Here are 3 files.  The first is a pdf with a Ticker input Range of A8:A56 with values.  The second is a pdf with a Ticker input Range of A8:A57 which has the #VALUE! output.  The third is the xls file.
>>
>>
>>Where should I go from here?
>>
>
>
>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (23)
3a.

Re: =RCHGetTableCell()

Posted by: "Pete" petekoch@yahoo.com   petekoch

Sat Oct 15, 2011 10:14 am (PDT)



In the Files section under Documentation, Randy has provided instructions for using the various functions.

You might want to start with RCHGetElementNumber, RCHGetYahooQuotes, and smfGetTableCell. Master those and the rest come easy....

--- In smf_addin@yahoogroups.com, NicholasDavid <nicholasdavid@...> wrote:
>
> Thanks Pete, where do I go to learn how to do this. Is a place on the group files that explains the syntax for this. I have all the basic formulas but, no explanation of how to do it. DAvid
>
> ----- Original Message -----
> From: "Pete" <petekoch@...>
> To: "smf addin" <smf_addin@yahoogroups.com>
> Sent: Friday, October 14, 2011 10:07:14 AM
> Subject: [smf_addin] Re: =RCHGetTableCell()
>
>  
>
>
>
>
> =RCHGetYahooQuotes("^TNX","l1") or RCHGetYahooQuotes("^TNX","l1")/100.
> That's a lower-case "L" in"l1".
>
> --- In smf_addin@yahoogroups.com , "David" <nicholasdavid@> wrote:
> >
> > Does someone have the formula for getting the current US10Y Yield?
> >
>

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

Re: =RCHGetTableCell()

Posted by: "NicholasDavid" nicholasdavid@comcast.net   dnicholas4967

Sat Oct 15, 2011 2:36 pm (PDT)



Thanks!

----- Original Message -----
From: "Pete" <petekoch@yahoo.com>
To: "smf addin" <smf_addin@yahoogroups.com>
Sent: Saturday, October 15, 2011 12:14:12 PM
Subject: [smf_addin] Re: =RCHGetTableCell()

 

In the Files section under Documentation, Randy has provided instructions for using the various functions.

You might want to start with RCHGetElementNumber, RCHGetYahooQuotes, and smfGetTableCell. Master those and the rest come easy....

--- In smf_addin@yahoogroups.com , NicholasDavid <nicholasdavid@...> wrote:
>
> Thanks Pete, where do I go to learn how to do this. Is a place on the group files that explains the syntax for this. I have all the basic formulas but, no explanation of how to do it. DAvid
>
> ----- Original Message -----
> From: "Pete" <petekoch@...>
> To: "smf addin" < smf_addin@yahoogroups.com >
> Sent: Friday, October 14, 2011 10:07:14 AM
> Subject: [smf_addin] Re: =RCHGetTableCell()
>
>  
>
>
>
>
> =RCHGetYahooQuotes("^TNX","l1") or RCHGetYahooQuotes("^TNX","l1")/100.
> That's a lower-case "L" in"l1".
>
> --- In smf_addin@yahoogroups.com , "David" <nicholasdavid@> wrote:
> >
> > Does someone have the formula for getting the current US10Y Yield?
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (7)
Recent Activity
Visit Your Group
Need traffic?

Drive customers

With search ads

on Yahoo!

Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Yahoo! News

Fashion News

What's the word on

fashion and style?

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.

Diposting oleh EXCEL FEED LEARNING di 01.56

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)