Selasa, 07 Mei 2013

[smf_addin] Digest Number 2606

13 New Messages

Digest #2606
2b
2d
Figured out RCHGetYahooQuotes by "jmackro" jmackro
2e
4a
Best way to pull multiple tickers by "dmatu2000" dmatu2000
4b
Re: Best way to pull multiple tickers by "Randy Harmelink" rharmelink

Messages

Mon May 6, 2013 4:29 am (PDT) . Posted by:

"jcarlosd" jcarlosd

I have a similar problem to this previous post:
http://finance.groups.yahoo.com/group/smf_addin/message/12251 that was not resolved.

The table I am trying to read is missing a </TR> in the first row, so all data is moved 8 columns to the right.

Is there any way to avoid that displacement?

URL is http://www.bolsasymercados.es/mab/esp/mercado/accfix_c.htm

VBA code I am using is:
strURL = "http://www.bolsasymercados.es/mab/esp/mercado/accfix_c.htm"
strArray = RCHGetHTMLTable(strURL, ">Nombre&quot;, -1, "", 1, , 4000, 9)

(can be easily adapted to excel cell formulas)

J. Carlos

Mon May 6, 2013 10:16 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

One workaround I can offer is to get the data by column:

=INDEX(RCHGetHTMLTable("
http://www.bolsasymercados.es/mab/esp/mercado/accfix_c.htm
","",1,"",1,,4000,30),,10)
=INDEX(RCHGetHTMLTable("
http://www.bolsasymercados.es/mab/esp/mercado/accfix_c.htm
","",1,"",1,,4000,30),,16)
=INDEX(RCHGetHTMLTable("
http://www.bolsasymercados.es/mab/esp/mercado/accfix_c.htm
","",1,"",1,,4000,30),,17)

Otherwise, I think you would need to get it cell by cell.

Or extract it from your full array that is returned.

On Mon, May 6, 2013 at 4:29 AM, jcarlosd <jcdelrio@procesando.com> wrote:

> I have a similar problem to this previous post:
> http://finance.groups.yahoo.com/group/smf_addin/message/12251 that was
> not resolved.
>
> The table I am trying to read is missing a </TR> in the first row, so all
> data is moved 8 columns to the right.
>
> Is there any way to avoid that displacement?
>
> URL is http://www.bolsasymercados.es/mab/esp/mercado/accfix_c.htm
>
> VBA code I am using is:
> strURL = "http://www.bolsasymercados.es/mab/esp/mercado/accfix_c.htm"
> strArray = RCHGetHTMLTable(strURL, ">Nombre&quot;, -1, "", 1, , 4000, 9)
>
> (can be easily adapted to excel cell formulas)
>

Mon May 6, 2013 4:58 pm (PDT) . Posted by:

"jmackro" jmackro

I have tried, but for the life of me, I cannot get the RCHGetYahooQuotes function to work. However I configure it, it just returns "#NAME?"

I started by logging into the SMF_AddOn section of Yahoo Groups, clicking on "Files", "Add-In Files", and "RCH_Stock_Market_Functions". I downloaded that, unzipped it, and put the resulting 13 files into a directory named "SMF Add-In" created in my Program Files folder.

The 13 files consist of:
RCH_Stock_Market_Functions
RCH Get Element Number – Element Definitions
SMF-Elements 0 through 9
SMF-Elements info

Are those the correct files to support RCHGetYahooQuotes?

Next I created a blank Excel workbook (I am running Excel 2003 SP3 under XT). I clicked on tools, add-ins, browse. I navigated to c:\program files\smf add-in, and highlighted "RCH Stock Market Functions". That created a new entry on the add in screen called"Stock Market Functions Add-In" (it seems odd that the file name, and the name in the Excel add-on list differed slightly). I put a check mark in the box next to "Stock Market Functions Add-In", and hit "OK".

Then I tried using the RCHGetYahooQuotes function with a simple call: "=RCHGetYahooQuotes(IBM)" which returned "#NAME?". Duplicating other examples given in the Files section of Yahoo Groups produced the same result.

Can you see what I am doing wrong? Is Excel 2003 simply too old to support the RCH Stock Market Functions?

Mon May 6, 2013 5:02 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

The problem is the way you tried to invoke the function:

=RCHGetYahooQuotes(IBM)

In that case, EXCEL is expecting IBM to be a named range. Since it can't
find it, it gives you a #NAME? error. You should be passing a literal (or a
cell reference pointing to a string literal. For example:

=RCHGetYahooQuotes("IBM")
=RCHGetYahooQuotes(A1)

Also, if you want that function to return more than just a single cell's
worth of data, you'll need to array-enter it over a range. Check out the
documentation for the function in the "Documentation" folder of the FILES
area of the Yahoo group. Also, there is a step-by-step example of setting
up this function in the LINKS area of the Yahoo group.

On Mon, May 6, 2013 at 3:11 PM, jmackro <jmackro@alum.mit.edu> wrote:

> I have tried, but for the life of me, I cannot get the RCHGetYahooQuotes
> function to work. However I configure it, it just returns "#NAME?"
>
> I started by logging into the SMF_AddOn section of Yahoo Groups, clicking
> on "Files", "Add-In Files", and "RCH_Stock_Market_Functions". I downloaded
> that, unzipped it, and put the resulting 13 files into a directory named
> "SMF Add-In" created in my Program Files folder.
>
> The 13 files consist of:
> RCH_Stock_Market_Functions
> RCH Get Element Number รข€" Element Definitions
> SMF-Elements 0 through 9
> SMF-Elements info
>
> Are those the correct files to support RCHGetYahooQuotes?
>
> Next I created a blank Excel workbook (I am running Excel 2003 SP3 under
> XT). I clicked on tools, add-ins, browse. I navigated to c:\program
> files\smf add-in, and highlighted "RCH Stock Market Functions". That
> created a new entry on the add in screen called"Stock Market Functions
> Add-In" (it seems odd that the file name, and the name in the Excel add-on
> list differed slightly). I put a check mark in the box next to "Stock
> Market Functions Add-In", and hit "OK".
>
> Then I tried using the RCHGetYahooQuotes function with a simple call:
> "=RCHGetYahooQuotes(IBM)" which returned "#NAME?". Duplicating other
> examples given in the Files section of Yahoo Groups produced the same
> result.
>
> Can you see what I am doing wrong? Is Excel 2003 simply too old to
> support the RCH Stock Market Functions?
>

Mon May 6, 2013 8:16 pm (PDT) . Posted by:

"jmackro" jmackro

Sorry, I neglected to type the quotes in my initial statement of the problem. Yes, I was putting the symbol in quotes (as in =RCHGetYahooQuotes("IBM")), or using a cell reference (as in =RCHGetYahooQuotes(A1)where cell A1 contains IBM).

Here's another odd result that may help to debug this:

If I enter =RCHGetYahooQuotes("IBM"), it will return IBM.

If I enter =RCHGetYahooQuotes(A1), where cell A1 contains IBM, it will return IBM.

If I enter =RCHGetYahooQuotes(A1,l1,,NOW(),), where cell A1 contains IBM, it will return nothing

If I enter =RCHGetYahooQuotes("IBM",l1,,NOW(),), it will return nothing

Thank you

----------------------------------------------------------

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> The problem is the way you tried to invoke the function:
>
> =RCHGetYahooQuotes(IBM)
>
> In that case, EXCEL is expecting IBM to be a named range. Since it can't
> find it, it gives you a #NAME? error. You should be passing a literal (or a
> cell reference pointing to a string literal. For example:
>
> =RCHGetYahooQuotes("IBM")
> =RCHGetYahooQuotes(A1)
>

Mon May 6, 2013 8:28 pm (PDT) . Posted by:

"jmackro" jmackro

Oh, the second parameter needs to be in quotes! As in -=RCHGetYahooQuotes("IBM","l1"). I was entering: =RCHGetYahooQuotes("IBM",l1)

Sorry, guess I just didn't experiment enough - it's working now.

Mon May 6, 2013 9:36 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

On Mon, May 6, 2013 at 8:16 PM, jmackro <jmackro@alum.mit.edu> wrote:

> Sorry, I neglected to type the quotes in my initial statement of the
> problem. Yes, I was putting the symbol in quotes (as in
> =RCHGetYahooQuotes("IBM")), or using a cell reference (as in
> =RCHGetYahooQuotes(A1)where cell A1 contains IBM).
>
> Here's another odd result that may help to debug this:
>
> If I enter =RCHGetYahooQuotes("IBM"), it will return IBM.
>

That is correct. As noted in the documentation, the default Yahoo codes are
"sl1d1t1c1ohgv". The first item, "s", says to return the symbol for the
stock. So you got IBM returned.

If you want more than the one item, you need to array-enter the formula
over a range. In this case, a 1-row, by 9-column range would be enough room
for all of the default Yahoo codes:

IBM 202.78 5/6/2013 4:02pm -1.73 203.96 203.99 201.52 4827188

> If I enter =RCHGetYahooQuotes(A1), where cell A1 contains IBM, it will
> return IBM.
>

Correct. Same results as above.

> If I enter =RCHGetYahooQuotes(A1,l1,,NOW(),), where cell A1 contains IBM,
> it will return nothing
>

Because l1 there is a cell reference. What you are trying to do is:

=RCHGetYahooQuotes(A1,"l1",,NOW(),)

> If I enter =RCHGetYahooQuotes("IBM",l1,,NOW(),), it will return nothing
>

Correct, because your Yahoo code is still empty. Unless you put something
into cell L1.

Mon May 6, 2013 4:58 pm (PDT) . Posted by:

"CT Phan" tora_toba

Hi Randy,
I'm trying to add element numbers for Yahoo quarterly balance sheet info (e.g. http://finance.yahoo.com/q/bs?s=aapl&ql=1) by editing the smf-elements-2.txt file. Most of the elements that I've added work fine except I'm having a problem with the data elements that are in bold, such as "Total Current Assets" or "Total Assets". I suspect it has to do with the <strong> tags for those lines in the page. I've looked through your code for a few minutes and couldn't think of how to pass in parameters via the text file to accommodate for this, so I figured asking you would be easier than trying to edit your code.
Thanks so much for the awesome tool!
CT

Mon May 6, 2013 5:08 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

First off, I wouldn't suggest putting your own element definitions into the
smf-elements-2.txt file. If I make changes to element definitions in that
file, you won't be able to replace the file with my updated file without
wiping out all of your definitions. The add-in looks for files
smf-elements-1.txt through smf-elements-20.txt when loading element
definitions, so you should just pick a number that isn't already being used
for your definitions. Then they won't be wiped out or lost if you apply any
updates for the add-in. And -- be sure to keep a backup. :)

As for the other issue, these work fine for me:

=RCHGetTableCell("http://finance.yahoo.com/q/bs?s=aapl&ql=1",1,"Total
Current Assets")
=RCHGetTableCell("http://finance.yahoo.com/q/bs?s=aapl&ql=1",1,"Total
Assets")

On Mon, May 6, 2013 at 2:37 PM, CT Phan <ctphan02@hotmail.com> wrote:

> I'm trying to add element numbers for Yahoo quarterly balance sheet info
> (e.g. http://finance.yahoo.com/q/bs?s=aapl&ql=1) by editing the
> smf-elements-2.txt file. Most of the elements that I've added work fine
> except I'm having a problem with the data elements that are in bold, such
> as "Total Current Assets" or "Total Assets". I suspect it has to do with
> the <strong> tags for those lines in the page. I've looked through your
> code for a few minutes and couldn't think of how to pass in parameters via
> the text file to accommodate for this, so I figured asking you would be
> easier than trying to edit your code.
>

Mon May 6, 2013 4:58 pm (PDT) . Posted by:

"dmatu2000" dmatu2000

How can I pull data for more than ticker at a time in a spreadsheet? For example, on the same tab, I would like to be able to pull data for 100+ tickers at once.

Mon May 6, 2013 5:16 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

I need more context to answer the question.

If you're referring to using the RCHGetYahooQuotes() function, check out
the entry in the LINKS area of the group. It tells how to set up a table
using that function. That function can return multiple data items on up to
200 ticker symbols, in one Internet access.

On Mon, May 6, 2013 at 1:57 PM, dmatu2000 <david_matuszak@yahoo.com> wrote:

> How can I pull data for more than ticker at a time in a spreadsheet? For
> example, on the same tab, I would like to be able to pull data for 100+
> tickers at once.
>

Mon May 6, 2013 9:57 pm (PDT) . Posted by:

"PeteA" option2z

Has anyone tried using one of the SMF calls to read the data in a Google
Docs Spreadsheet? I looked at the HTML behind the "GD Spreadsheet" and it
is daunting. Perhaps I can figure it out, but thought I'd ask first.

TIA,
Pete A

Tue May 7, 2013 12:41 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

I just tried it -- this works fine to get first row and first column:

=smfGetTagContent("
https://docs.google.com/spreadsheet/lv?key=0Au-lHos7OyYjdERYN0lROXRJSDNFQ2NOZ1h0MmV0T0E&rm=full#gid=0
","td",1,"class='rowNum&#39;>1<")

...or second row and second column:

=smfGetTagContent("
https://docs.google.com/spreadsheet/lv?key=0Au-lHos7OyYjdERYN0lROXRJSDNFQ2NOZ1h0MmV0T0E&rm=full#gid=0
","td",2,"class='rowNum&#39;>2<")

The URL is the "List" view of the spreadsheet.

Not sure of the specifics of a more complicated spreadsheet, but it does
seem to work for this simple example.

On Mon, May 6, 2013 at 9:57 PM, PeteA <optionzz@gmail.com> wrote:

>
> Has anyone tried using one of the SMF calls to read the data in a Google
> Docs Spreadsheet? I looked at the HTML behind the รข€œGD Spreadsheetรข€ and
> it is daunting. Perhaps I can figure it out, but thought Iรข€™d ask first.**
> **
>
>
>
Diposting oleh EXCEL FEED LEARNING di 02.35

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)