Rabu, 12 Oktober 2011

[smf_addin] Digest Number 1983

Messages In This Digest (25 Messages)

1a.
Re: Controlling updates From: Joe
1b.
Re: Controlling updates From: Randy Harmelink
1c.
Re: Controlling updates From: Joe Mo
2a.
Using RCHGetTableCell to get "Net Tangible Assets" from Yahoo Balanc From: ritchierun
2b.
Re: Using RCHGetTableCell to get "Net Tangible Assets" from Yahoo Ba From: Randy Harmelink
2c.
Re: Using RCHGetTableCell to get "Net Tangible Assets" from Yahoo Ba From: ritchierun
2d.
Re: Using RCHGetTableCell to get "Net Tangible Assets" from Yahoo Ba From: Randy Harmelink
2e.
Re: Using RCHGetTableCell to get "Net Tangible Assets" from Yahoo Ba From: ritchierun
3a.
Re: FRED Economic Data From: adam pilz
3b.
Re: FRED Economic Data From: Randy Harmelink
4a.
Writing your own elements From: curls07
4b.
Re: Writing your own elements From: Randy Harmelink
5a.
RCH_STOCK_MARKET_FUNCTIONS.XLA ISSUES From: Sylvester
5b.
Re: RCH_STOCK_MARKET_FUNCTIONS.XLA ISSUES From: Randy Harmelink
5c.
Re: RCH_STOCK_MARKET_FUNCTIONS.XLA ISSUES From: scott
5d.
Re: RCH_STOCK_MARKET_FUNCTIONS.XLA ISSUES From: Randy Harmelink
6a.
rchgethtmltable solution From: mic_peg
6b.
Re: rchgethtmltable solution From: Randy Harmelink
6c.
Re: rchgethtmltable solution From: mic_peg
7a.
I feel like a complete idiot... From: scott
7b.
Re: I feel like a complete idiot... From: Randy Harmelink
7c.
Re: I feel like a complete idiot... From: scott
7d.
Re: I feel like a complete idiot... From: Randy Harmelink
7e.
Re: I feel like a complete idiot... From: scott
7f.
Re: I feel like a complete idiot... From: Randy Harmelink

Messages

1a.

Re: Controlling updates

Posted by: "Joe" joe_mo37@yahoo.com   joe_mo37

Wed Oct 12, 2011 5:15 am (PDT)



I've searched the Files section and not been able to locate the smfForceRecalculation macro. Can someone direct me to it?
Joe

Sent from my iPad
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)
1b.

Re: Controlling updates

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

Wed Oct 12, 2011 6:18 am (PDT)



It's already in the add-in. Check the "Links" area of the group for
information on it.

On Wed, Oct 12, 2011 at 5:14 AM, Joe <joe_mo37@yahoo.com> wrote:

> I've searched the Files section and not been able to locate the
> smfForceRecalculation macro. Can someone direct me to it?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)
1c.

Re: Controlling updates

Posted by: "Joe Mo" joe_mo37@yahoo.com   joe_mo37

Wed Oct 12, 2011 7:41 pm (PDT)



Got it. Thanks Randy.
Joe

________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Wednesday, October 12, 2011 8:18 AM
Subject: Re: [smf_addin] Controlling updates

 
It's already in the add-in. Check the "Links" area of the group for information on it.

On Wed, Oct 12, 2011 at 5:14 AM, Joe <joe_mo37@yahoo.com> wrote:

I've searched the Files section and not been able to locate the smfForceRecalculation macro. Can someone direct me to it?
>

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

Using RCHGetTableCell to get "Net Tangible Assets" from Yahoo Balanc

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

Wed Oct 12, 2011 2:11 pm (PDT)



Randy,

I am posting our email, as you suggested. History is below.

Regarding my issue, I should have been more clear. I am using a table similar to the sample posted as "smfUpdateDownloadTable-Sample". Therefore, wouldn't the URL that I used be valid?

I used the first "Market Cap" formula "RCHGetTableCell("http://finance.yahoo.com/q/ks?s=~~~~~",1,">Market Cap")" as a guide and modified this formula to extract what I needed. But it doesn't get Net Tangible Assets when I use "RCHGetTableCell("http://finance.yahoo.com/q/bs?s=~~~~~",1,"Annual Data","Net Tangible Assets")".
Thanks,
Ron
----------------------------------------------------------

You need to give it a valid URL -- the five tildas are a convention used in defining elements for the RCHGetElementNumber() function.

PS: Please direct all future add-in related questions to the Yahoo Group. Otherwise, I may never see them because they end up in my SPAM folder too often. Thanks.

On Wed, Oct 12, 2011 at 9:08 AM, ritchierun <ritchierun@yahoo.com> wrote:

I am using Excel 2007 to set up a table using RCHGetTableCell for various data from Yahoo in columns for multiple stocks in rows. Using your examples, I have been able to extract data from every sheet I needed, except for the Balance Sheet.

I am using the formula "RCHGetTableCell("http://finance.yahoo.com/q/bs?s=~~~~~",1,"Annual Data","Net Tangible Assets")", which results in "Error." I know that I am getting to the right table row, because by adding the [Row#] element to my formula "Currency in USD." is returned.

It is as if the table cells in the "Net Tangible Assets" row are empty.

Can you help?

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

Re: Using RCHGetTableCell to get "Net Tangible Assets" from Yahoo Ba

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

Wed Oct 12, 2011 2:46 pm (PDT)



You are correct -- the tildas should be used in association with the
smfUpdateDownloadTable process.

However, I took the example workbook from the "Files" area, inserted a new
column and used:

RCHGetTableCell("http://finance.yahoo.com/q/bs?s=~~~~~",1,"Annual Data","Net
Tangible Assets")

...as the column definition. It worked fine for me:

RCHGetTableCell("http://finance.yahoo.com/q/bs?s=~~~~~",1,"Annual
Data","Net Tangible Assets") *Ticker* *Net Tangible Assets* MMM $8,072,000
JNJ $27,511,000 MO -$12,648,000 ABT -$2,117,674 WFC $111,625,000
What ticker symbols are you using? What version of the add-in are you using?

On Wed, Oct 12, 2011 at 1:31 PM, ritchierun <ritchierun@yahoo.com> wrote:

>
> Regarding my issue, I should have been more clear. I am using a table
> similar to the sample posted as "smfUpdateDownloadTable-Sample". Therefore,
> wouldn't the URL that I used be valid?
>
> I am using Excel 2007 to set up a table using RCHGetTableCell for various
> data from Yahoo in columns for multiple stocks in rows. Using your examples,
> I have been able to extract data from every sheet I needed, except for the
> Balance Sheet.
>
> I am using the formula "RCHGetTableCell("
> http://finance.yahoo.com/q/bs?s=~~~~~",1,"Annual Data","Net Tangible
> Assets")", which results in "Error." I know that I am getting to the right
> table row, because by adding the [Row#] element to my formula "Currency in
> USD." is returned.
>
> It is as if the table cells in the "Net Tangible Assets" row are empty.
>
> Can you help?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)
2c.

Re: Using RCHGetTableCell to get "Net Tangible Assets" from Yahoo Ba

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

Wed Oct 12, 2011 3:08 pm (PDT)



I am using Version 2.1.2010.08.02 of the Add-in, and was only using ticker symbols: ABX, AUQ, IAG, SLV, and AG.

This morning I began testing with other symbols, like you thinking that it might make a difference. I discovered that it is actually extracting information on some tickers, but not the info I want.

For example, CDE pulls in "$2,095,596". This is Quarterly Net Tangible Assets, not Annual. I now believe the reason that ABX, AUQ, IAG, SLV, and AG returned an error is that they have no Quarterly Data on the Balance Sheet.

So my question now is; Why is it extracting quarterly data for me when I am using the same formula that you did:
RCHGetTableCell("http://finance.yahoo.com/q/bs?s=~~~~~",1,"Annual Data","Net Tangible Assets")
--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> You are correct -- the tildas should be used in association with the
> smfUpdateDownloadTable process.
>
> However, I took the example workbook from the "Files" area, inserted a new
> column and used:
>
> RCHGetTableCell("http://finance.yahoo.com/q/bs?s=~~~~~",1,"Annual Data","Net
> Tangible Assets")
>
> ...as the column definition. It worked fine for me:
>
> RCHGetTableCell("http://finance.yahoo.com/q/bs?s=~~~~~",1,"Annual
> Data","Net Tangible Assets") *Ticker* *Net Tangible Assets* MMM $8,072,000
> JNJ $27,511,000 MO -$12,648,000 ABT -$2,117,674 WFC $111,625,000
> What ticker symbols are you using? What version of the add-in are you using?
>
> On Wed, Oct 12, 2011 at 1:31 PM, ritchierun <ritchierun@...> wrote:
>
> >
> > Regarding my issue, I should have been more clear. I am using a table
> > similar to the sample posted as "smfUpdateDownloadTable-Sample". Therefore,
> > wouldn't the URL that I used be valid?
> >
> > I am using Excel 2007 to set up a table using RCHGetTableCell for various
> > data from Yahoo in columns for multiple stocks in rows. Using your examples,
> > I have been able to extract data from every sheet I needed, except for the
> > Balance Sheet.
> >
> > I am using the formula "RCHGetTableCell("
> > http://finance.yahoo.com/q/bs?s=~~~~~",1,"Annual Data","Net Tangible
> > Assets")", which results in "Error." I know that I am getting to the right
> > table row, because by adding the [Row#] element to my formula "Currency in
> > USD." is returned.
> >
> > It is as if the table cells in the "Net Tangible Assets" row are empty.
> >
> > Can you help?
> >
>

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

Re: Using RCHGetTableCell to get "Net Tangible Assets" from Yahoo Ba

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

Wed Oct 12, 2011 3:17 pm (PDT)



If you want annual data, the correct formula would be:

RCHGetTableCell("http://finance.yahoo.com/q/bs?s=~~~~~*&annual*",1,"Annual
Data","Net Tangible Assets")

The "Annual Data" search string you are using is just the label on a link,
not a description of what is on the page.

On Wed, Oct 12, 2011 at 3:08 PM, ritchierun <ritchierun@yahoo.com> wrote:

> I am using Version 2.1.2010.08.02 of the Add-in, and was only using ticker
> symbols: ABX, AUQ, IAG, SLV, and AG.
>
> This morning I began testing with other symbols, like you thinking that it
> might make a difference. I discovered that it is actually extracting
> information on some tickers, but not the info I want.
>
> For example, CDE pulls in "$2,095,596". This is Quarterly Net Tangible
> Assets, not Annual. I now believe the reason that ABX, AUQ, IAG, SLV, and AG
> returned an error is that they have no Quarterly Data on the Balance Sheet.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)
2e.

Re: Using RCHGetTableCell to get "Net Tangible Assets" from Yahoo Ba

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

Wed Oct 12, 2011 3:56 pm (PDT)



It works! Thank you for your help.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> If you want annual data, the correct formula would be:
>
> RCHGetTableCell("http://finance.yahoo.com/q/bs?s=~~~~~*&annual*",1,"Annual
> Data","Net Tangible Assets")
>
> The "Annual Data" search string you are using is just the label on a link,
> not a description of what is on the page.
>
> On Wed, Oct 12, 2011 at 3:08 PM, ritchierun <ritchierun@...> wrote:
>
> > I am using Version 2.1.2010.08.02 of the Add-in, and was only using ticker
> > symbols: ABX, AUQ, IAG, SLV, and AG.
> >
> > This morning I began testing with other symbols, like you thinking that it
> > might make a difference. I discovered that it is actually extracting
> > information on some tickers, but not the info I want.
> >
> > For example, CDE pulls in "$2,095,596". This is Quarterly Net Tangible
> > Assets, not Annual. I now believe the reason that ABX, AUQ, IAG, SLV, and AG
> > returned an error is that they have no Quarterly Data on the Balance Sheet.
> >
>

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

Re: FRED Economic Data

Posted by: "adam pilz" adampilz.2326@gmail.com   pilzadam

Wed Oct 12, 2011 4:16 pm (PDT)



WOW! you are awesome, I have been looking for a file like this!

Thanks so much,
AP

On Wed, Oct 12, 2011 at 12:01 AM, Randy H <rharmelink@gmail.com> wrote:

> **
>
>
> Users of the Federal Reserve Economic Data (FRED) may find this extensive
> collection of links to be useful:
>
> http://dl.dropbox.com/u/45054803/FRED%20Complete%20Database.xls
>
>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
3b.

Re: FRED Economic Data

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

Wed Oct 12, 2011 4:22 pm (PDT)



I just passed on the link. The actual work to create it was done by two
people over on the METAR (Macro Economic Trends and Risks) group of The
Motley Fool:

http://boards.fool.com/fred-database-is-done-29597073.aspx?sort=whole#29597073

On Wed, Oct 12, 2011 at 4:16 PM, adam pilz <adampilz.2326@gmail.com> wrote:

>
> WOW! you are awesome, I have been looking for a file like this!
>
> Thanks so much,
> AP
>
> On Wed, Oct 12, 2011 at 12:01 AM, Randy H <rharmelink@gmail.com> wrote:
>
>> **
>>
>>
>> Users of the Federal Reserve Economic Data (FRED) may find this extensive
>> collection of links to be useful:
>>
>> http://dl.dropbox.com/u/45054803/FRED%20Complete%20Database.xls
>>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
4a.

Writing your own elements

Posted by: "curls07" agc11d@gmail.com   curls07

Wed Oct 12, 2011 5:11 pm (PDT)



Hey Randy,

I'm trying to write my own elements so I can get some information that is not already provided.

I'm looking to get the average retained earnings growth on ycharts.com http://ycharts.com/companies/GE/retained_earnings_growth

my element goes like this

1952;YCHARTS;RETAINED EARNINGS GROWTH;http://ycharts.com/companies/~~~~~/retained_earnings_growth;1;RATIOTABLE;CO12; ; ;0;0;0;0

I'm not sure how many ;s to place after co12 or how to find out.

Also, is there a basic template that you could post for creating elements that we could go by?

thanks for your time!

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

Re: Writing your own elements

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

Wed Oct 12, 2011 5:34 pm (PDT)



There should be 12 items separated by the semi-colons. If you check the
"Links" area of the group, you'll find a link to a message that gives some
information on that process. However, since that message was written, I've
added an additional parameter to RCHGetTableCell(), so you need to add that
to the end of the element definition -- just set the value to 0.

Also, you can just do something like this:

17005;Evaluate;TempName;RCHGetTableCell("
http://finance.yahoo.com/q/ks?s=~~~~~",1,">Market Cap");;;;;;;;;0

That gives you more flexibility, since you can use other functions in the
formula if you want. The element number, "TempName", and the formula can be
of your choosing. Everything else is fixed.

On Wed, Oct 12, 2011 at 4:49 PM, curls07 <agc11d@gmail.com> wrote:

>
> I'm trying to write my own elements so I can get some information that is
> not already provided.
>
> I'm looking to get the average retained earnings growth on ycharts.com
> http://ycharts.com/companies/GE/retained_earnings_growth
>
> my element goes like this
>
> 1952;YCHARTS;RETAINED EARNINGS GROWTH;
> http://ycharts.com/companies/~~~~~/retained_earnings_growth;1;RATIOTABLE;CO12;
> ; ;0;0;0;0
>
> I'm not sure how many ;s to place after co12 or how to find out.
>
> Also, is there a basic template that you could post for creating elements
> that we could go by?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
5a.

RCH_STOCK_MARKET_FUNCTIONS.XLA ISSUES

Posted by: "Sylvester" sylvesterstanleyjr@yahoo.com   sylvesterstanleyjr

Wed Oct 12, 2011 6:44 pm (PDT)



When I first login in to excel I get workbook prompt saying that this workbook contains links to other data sources and if I would like to update the link. Then I hit the update tab and then I get a prompt saying this workbook contains one or two links that cannot be updated. I then click the edit link tab and this is what i get "RCH_STOCK_MARKET_FUNCTIIONS.XLA A ERROR SOURCE NOT FOUND"

If I just don't update, I can go to a cell displaying "#NAME?" and it will have an array formula like "='C:\Program Files\SMF Add-In\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber($A$1,167)

If I edit the cell to remove everything between the "=" and "xla"
=RCHGetElementNumber($A$1,167) and hit enter I get a error message in the cell.

So why is having 'C:\Program Files\SMF Add_In\RCH_Stock_Market_Functions.xla'!
a problem?

I think I have things installed correctly. I'm running Windows 7 ultimate with Excel 2010

Thank you for all your help in advance.

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

Re: RCH_STOCK_MARKET_FUNCTIONS.XLA ISSUES

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

Wed Oct 12, 2011 7:50 pm (PDT)



Check the "Links" area of the group for info on #NAME? errors.

Basically, you'll get the path on the formula whenever EXCEL cannot resolve
the links. If it does find the add-in, then it will consider the link
resolved and remove the path. Otherwise, you have to handle them manually.
It's an EXCEL thing.

In a new worksheet, what do you get from:

=RCHGetElementNumber("Version")

If that works, you do have the add-in installed. What error message did you
get from:

=RCHGetElementNumber($A$1,167)

If just "Error", what ticker symbol did you have in cell A1? If "Undefined",
there's another "Links" item you need to look at.

On Wed, Oct 12, 2011 at 6:19 PM, Sylvester <sylvesterstanleyjr@yahoo.com>wrote:

> When I first login in to excel I get workbook prompt saying that this
> workbook contains links to other data sources and if I would like to update
> the link. Then I hit the update tab and then I get a prompt saying this
> workbook contains one or two links that cannot be updated. I then click the
> edit link tab and this is what i get "RCH_STOCK_MARKET_FUNCTIIONS.XLA A
> ERROR SOURCE NOT FOUND"
>
> If I just don't update, I can go to a cell displaying "#NAME?" and it will
> have an array formula like "='C:\Program Files\SMF
> Add-In\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber($A$1,167)
>
> If I edit the cell to remove everything between the "=" and "xla"
> =RCHGetElementNumber($A$1,167) and hit enter I get a error message in the
> cell.
>
> So why is having 'C:\Program Files\SMF
> Add_In\RCH_Stock_Market_Functions.xla'!
> a problem?
>
> I think I have things installed correctly. I'm running Windows 7 ultimate
> with Excel 2010
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
5c.

Re: RCH_STOCK_MARKET_FUNCTIONS.XLA ISSUES

Posted by: "scott" scott_grant2@yahoo.com   scott_grant2

Wed Oct 12, 2011 9:55 pm (PDT)




The version has always worked for me, it did this time as well..

I get a circular reference warning on the second one.."RCHGetElementNumber"
i didnt have any stock symbol in the cell A1
i just pasted =RCHGetElementNumber($A$1,167)

thanks for trying to help me..

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Check the "Links" area of the group for info on #NAME? errors.
>
> Basically, you'll get the path on the formula whenever EXCEL cannot resolve
> the links. If it does find the add-in, then it will consider the link
> resolved and remove the path. Otherwise, you have to handle them manually.
> It's an EXCEL thing.
>
> In a new worksheet, what do you get from:
>
> =RCHGetElementNumber("Version")
>
> If that works, you do have the add-in installed. What error message did you
> get from:
>
> =RCHGetElementNumber($A$1,167)
>
> If just "Error", what ticker symbol did you have in cell A1? If "Undefined",
> there's another "Links" item you need to look at.
>
> On Wed, Oct 12, 2011 at 6:19 PM, Sylvester <sylvesterstanleyjr@...>wrote:
>
> > When I first login in to excel I get workbook prompt saying that this
> > workbook contains links to other data sources and if I would like to update
> > the link. Then I hit the update tab and then I get a prompt saying this
> > workbook contains one or two links that cannot be updated. I then click the
> > edit link tab and this is what i get "RCH_STOCK_MARKET_FUNCTIIONS.XLA A
> > ERROR SOURCE NOT FOUND"
> >
> > If I just don't update, I can go to a cell displaying "#NAME?" and it will
> > have an array formula like "='C:\Program Files\SMF
> > Add-In\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber($A$1,167)
> >
> > If I edit the cell to remove everything between the "=" and "xla"
> > =RCHGetElementNumber($A$1,167) and hit enter I get a error message in the
> > cell.
> >
> > So why is having 'C:\Program Files\SMF
> > Add_In\RCH_Stock_Market_Functions.xla'!
> > a problem?
> >
> > I think I have things installed correctly. I'm running Windows 7 ultimate
> > with Excel 2010
> >
>

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

Re: RCH_STOCK_MARKET_FUNCTIONS.XLA ISSUES

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

Wed Oct 12, 2011 9:58 pm (PDT)



Sounds like you tried to paste the formula into cell A1, and the formula
refers to cell A1. That would be your circular reference.

You should put something like MMM in cell A1 and then paste the formula into
cell B1.

On Wed, Oct 12, 2011 at 9:55 PM, scott <scott_grant2@yahoo.com> wrote:

>
> I get a circular reference warning on the second one.."RCHGetElementNumber"
> i didnt have any stock symbol in the cell A1
> i just pasted =RCHGetElementNumber($A$1,167)
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
6a.

rchgethtmltable solution

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

Wed Oct 12, 2011 7:20 pm (PDT)



hi Randy,
from the below codings, how do i get rchgethtmltable to work?
appreciate if you can help me with this function if i want to create an array to return values from r2c1 to r4c4. i don't quite understand the details in the documentation and have tried with all the "-1" and "1" trial error but does not work. thanks!

<TABLE border="2">
<TR>
<!-- Row 1 -->
<TD align="center"> Name </TD>
<TD align="center"> Address </TD>
<TD align="center"> Telephone </TD>
<TD align="center"> Email </TD>
</TR>
<TR>
<!-- Row 2 -->
<TD align="center"> R2C1 </TD>
<TD align="center"> R2C2 </TD>
<TD align="center"> R2C3 </TD>
<TD align="center"> R2C4 </TD>
</TR>
<TR>
<!-- Row 3 -->
<TD align="center"> R3C1 </TD>
<TD align="center"> R3C2 </TD>
<TD align="center"> R3C3 </TD>
<TD align="center"> R3C4 </TD>
</TR>
<TR>
<!-- Row 4 -->
<TD align="center"> R4C1 </TD>
<TD align="center"> R4C2 </TD>
<TD align="center"> R4C3 </TD>
<TD align="center"> R4C4 </TD>
</TR>
</TABLE>

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

Re: rchgethtmltable solution

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

Wed Oct 12, 2011 7:44 pm (PDT)



The RCHGetHTMLTable() function would return the entire table, including the
first row.

For what I see, I would do something like:

=RCHGetHTMLTable("http://ogres-crypt.com/tmp/test4.htm","> Telephone",-1,"
",1)

...which would tell the function to:

1. Search for string "> Telephone" within the source code of the web
page
2. Then find the first starting "<table" tag prior to that search string
(i.e. "-1")
3. Then use the same search string again (i.e. " ")
4. Then find the first ending "</table" tag after that search string
(i.e. "1")

Then the add-in will take everything between the "<table" and "</table" tags
and parse it out into rows and columns, as the HTML tags indicate. However,
it's hand-written code by me, so it won't handle complicated tables or
improperly codes tables (i.e. the code uses "</tr" and "</td" tags to
identify ends of rows and cells).

Determining unique search strings is usually the hardest part. The "-1" and
"1" parameters just tell it which direction to go, and how far, from those
search strings, in order to find the starting table tag and the ending table
tag.

On Wed, Oct 12, 2011 at 7:20 PM, mic_peg <mic_peg@yahoo.com> wrote:

>
> from the below codings, how do i get rchgethtmltable to work?
> appreciate if you can help me with this function if i want to create an
> array to return values from r2c1 to r4c4. i don't quite understand the
> details in the documentation and have tried with all the "-1" and "1" trial
> error but does not work. thanks!
>
> <TABLE border="2">
> <TR>
> <!-- Row 1 -->
> <TD align="center"> Name </TD>
> <TD align="center"> Address </TD>
> <TD align="center"> Telephone </TD>
> <TD align="center"> Email </TD>
> </TR>
> <TR>
> <!-- Row 2 -->
> <TD align="center"> R2C1 </TD>
> <TD align="center"> R2C2 </TD>
> <TD align="center"> R2C3 </TD>
> <TD align="center"> R2C4 </TD>
> </TR>
> <TR>
> <!-- Row 3 -->
> <TD align="center"> R3C1 </TD>
> <TD align="center"> R3C2 </TD>
> <TD align="center"> R3C3 </TD>
> <TD align="center"> R3C4 </TD>
> </TR>
> <TR>
> <!-- Row 4 -->
> <TD align="center"> R4C1 </TD>
> <TD align="center"> R4C2 </TD>
> <TD align="center"> R4C3 </TD>
> <TD align="center"> R4C4 </TD>
> </TR>
> </TABLE>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
6c.

Re: rchgethtmltable solution

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

Wed Oct 12, 2011 8:55 pm (PDT)



thank you for the well explained solution. it is working now.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> The RCHGetHTMLTable() function would return the entire table, including the
> first row.
>
> For what I see, I would do something like:
>
> =RCHGetHTMLTable("http://ogres-crypt.com/tmp/test4.htm","> Telephone",-1,"
> ",1)
>
> ...which would tell the function to:
>
> 1. Search for string "> Telephone" within the source code of the web
> page
> 2. Then find the first starting "<table" tag prior to that search string
> (i.e. "-1")
> 3. Then use the same search string again (i.e. " ")
> 4. Then find the first ending "</table" tag after that search string
> (i.e. "1")
>
> Then the add-in will take everything between the "<table" and "</table" tags
> and parse it out into rows and columns, as the HTML tags indicate. However,
> it's hand-written code by me, so it won't handle complicated tables or
> improperly codes tables (i.e. the code uses "</tr" and "</td" tags to
> identify ends of rows and cells).
>
> Determining unique search strings is usually the hardest part. The "-1" and
> "1" parameters just tell it which direction to go, and how far, from those
> search strings, in order to find the starting table tag and the ending table
> tag.
>
> On Wed, Oct 12, 2011 at 7:20 PM, mic_peg <mic_peg@...> wrote:
>
> >
> > from the below codings, how do i get rchgethtmltable to work?
> > appreciate if you can help me with this function if i want to create an
> > array to return values from r2c1 to r4c4. i don't quite understand the
> > details in the documentation and have tried with all the "-1" and "1" trial
> > error but does not work. thanks!
> >
> > <TABLE border="2">
> > <TR>
> > <!-- Row 1 -->
> > <TD align="center"> Name </TD>
> > <TD align="center"> Address </TD>
> > <TD align="center"> Telephone </TD>
> > <TD align="center"> Email </TD>
> > </TR>
> > <TR>
> > <!-- Row 2 -->
> > <TD align="center"> R2C1 </TD>
> > <TD align="center"> R2C2 </TD>
> > <TD align="center"> R2C3 </TD>
> > <TD align="center"> R2C4 </TD>
> > </TR>
> > <TR>
> > <!-- Row 3 -->
> > <TD align="center"> R3C1 </TD>
> > <TD align="center"> R3C2 </TD>
> > <TD align="center"> R3C3 </TD>
> > <TD align="center"> R3C4 </TD>
> > </TR>
> > <TR>
> > <!-- Row 4 -->
> > <TD align="center"> R4C1 </TD>
> > <TD align="center"> R4C2 </TD>
> > <TD align="center"> R4C3 </TD>
> > <TD align="center"> R4C4 </TD>
> > </TR>
> > </TABLE>
> >
>

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

I feel like a complete idiot...

Posted by: "scott" scott_grant2@yahoo.com   scott_grant2

Wed Oct 12, 2011 7:31 pm (PDT)



I can get the version validation to work, but cant figure out
how to pull data from yahoo? the validation is the only thing working..
man i feel DUMB... i tried the aerospace.xls..
i cant figure out how to add a symbol to even get an error message
back..

I guess i dont use macros very often but i do use excel alot..
on my home computer i have microsoft office excel 2007
im on windows XP version 2002 service pack 2..
not sure what other info you need, i wish i had an error message
to give you or some steps.. anyway im frustrated and if anyone could
help i would appreciate

thanks
scott

First step would be to create this directory on your computer:

C:\Program Files\SMF Add-in

You would extract all of the files from the downloaded '.zip' file into the above folder (can right-click and choose 'Extract files' in Windows). Then, you open Excel. The Add-In works in all Excel versions. In EXCEL 2010, that process is:
Open a new workbook
Use menu option > File > Options > Add-Ins
Then Manage: Excel Add-Ins "Go", and select `Stock Market Functions Add-In' if in the list, or browse to the 'C:\Program Files\SMF Add-in' directory and pick the .XLA file that was extracted from the ZIP archive and double-click on it

From there you can test whether it's working. Type =RCHGetElementNumber("Version") into a cell and it should return the current Version of the Add-in you are using. It will say something like: Version 2.1.2010.08.02

and mine did...

If you get this working you're good to go. Another note - the

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

Re: I feel like a complete idiot...

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

Wed Oct 12, 2011 7:56 pm (PDT)



The aerospace.xls file doesn't contain any add-in functions. It was
something someone posted, asking HOW to get all of those items. My response
is the aerospace-response.xls file, which create their fix grid with an
RCHGetYahooQuotes() function.

Also, be sure to check out the "Links" area of the group. I've tried to make
it a repository of frequently asked questions and issues.

By the way, the add-in really only has about 3 macros. Almost all of its
processing is based on user-defined functions, not macros.

All of the documentation of the functions should have at least one example
of their use. Try some of those for the functions you are interested in.

On Wed, Oct 12, 2011 at 7:05 PM, scott <scott_grant2@yahoo.com> wrote:

> I can get the version validation to work, but cant figure out
> how to pull data from yahoo? the validation is the only thing working..
> man i feel DUMB... i tried the aerospace.xls..
> i cant figure out how to add a symbol to even get an error message
> back..
>
> I guess i dont use macros very often but i do use excel alot..
> on my home computer i have microsoft office excel 2007
> im on windows XP version 2002 service pack 2..
> not sure what other info you need, i wish i had an error message
> to give you or some steps.. anyway im frustrated and if anyone could
> help i would appreciate
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (6)
7c.

Re: I feel like a complete idiot...

Posted by: "scott" scott_grant2@yahoo.com   scott_grant2

Wed Oct 12, 2011 9:20 pm (PDT)



thanks Randy, I actually made a little bit of progress.
I still cant get the aerospace respons to work i get NO VALUE
in my cells, and i read your NO_VALUE link, but still cant figure it out exactly.. i checked my path and everything..
umm, i did try the example with pulling from yahoo and
that actually pulled back data in the first cell, i believe it was the opening price and i could do it manually for the other cells
(if i make my own sheet) but i couldnt do it for the mass cells...
(if that makes sense, i just change to read from b1 to b2 to b3, etc..
and the opening price filled me excel sheet..

i also tried the =RCHGetYahooHistory("MSFT")
and i got NOVALUE...

any advice would be appreciated, i actually learned from your last comment.. not sure why that one cell would work but the ALL of them
dont??

thanks
Scott

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> The aerospace.xls file doesn't contain any add-in functions. It was
> something someone posted, asking HOW to get all of those items. My response
> is the aerospace-response.xls file, which create their fix grid with an
> RCHGetYahooQuotes() function.
>
> Also, be sure to check out the "Links" area of the group. I've tried to make
> it a repository of frequently asked questions and issues.
>
> By the way, the add-in really only has about 3 macros. Almost all of its
> processing is based on user-defined functions, not macros.
>
> All of the documentation of the functions should have at least one example
> of their use. Try some of those for the functions you are interested in.
>
> On Wed, Oct 12, 2011 at 7:05 PM, scott <scott_grant2@...> wrote:
>
> > I can get the version validation to work, but cant figure out
> > how to pull data from yahoo? the validation is the only thing working..
> > man i feel DUMB... i tried the aerospace.xls..
> > i cant figure out how to add a symbol to even get an error message
> > back..
> >
> > I guess i dont use macros very often but i do use excel alot..
> > on my home computer i have microsoft office excel 2007
> > im on windows XP version 2002 service pack 2..
> > not sure what other info you need, i wish i had an error message
> > to give you or some steps.. anyway im frustrated and if anyone could
> > help i would appreciate
> >
>

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

Re: I feel like a complete idiot...

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

Wed Oct 12, 2011 9:34 pm (PDT)



I have no idea what you're referring to with "NO VALUE"...

If I'm understanding the reference, "mass cells" would need the formula to
be array-entered over a range (see documentation).

On Wed, Oct 12, 2011 at 9:20 PM, scott <scott_grant2@yahoo.com> wrote:

> thanks Randy, I actually made a little bit of progress.
> I still cant get the aerospace respons to work i get NO VALUE
> in my cells, and i read your NO_VALUE link, but still cant figure it out
> exactly.. i checked my path and everything..
> umm, i did try the example with pulling from yahoo and
> that actually pulled back data in the first cell, i believe it was the
> opening price and i could do it manually for the other cells
> (if i make my own sheet) but i couldnt do it for the mass cells...
> (if that makes sense, i just change to read from b1 to b2 to b3, etc..
> and the opening price filled me excel sheet..
>
> i also tried the =RCHGetYahooHistory("MSFT")
> and i got NOVALUE...
>
> any advice would be appreciated, i actually learned from your last
> comment.. not sure why that one cell would work but the ALL of them
> dont??
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (6)
7e.

Re: I feel like a complete idiot...

Posted by: "scott" scott_grant2@yahoo.com   scott_grant2

Wed Oct 12, 2011 9:41 pm (PDT)



sorry, i meant #NAME?
im betting youv seen those a million times..
what is weird though is the setting up pulling from yahoo
example with 3 stocks in the links worked for the 1st cell
it didnt work really for the others, or nothing popped up..(NULL - nothing)
but when i tried the aero-space response i get #NAME?

i hope that helps

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> I have no idea what you're referring to with "NO VALUE"...
>
> If I'm understanding the reference, "mass cells" would need the formula to
> be array-entered over a range (see documentation).
>
> On Wed, Oct 12, 2011 at 9:20 PM, scott <scott_grant2@...> wrote:
>
> > thanks Randy, I actually made a little bit of progress.
> > I still cant get the aerospace respons to work i get NO VALUE
> > in my cells, and i read your NO_VALUE link, but still cant figure it out
> > exactly.. i checked my path and everything..
> > umm, i did try the example with pulling from yahoo and
> > that actually pulled back data in the first cell, i believe it was the
> > opening price and i could do it manually for the other cells
> > (if i make my own sheet) but i couldnt do it for the mass cells...
> > (if that makes sense, i just change to read from b1 to b2 to b3, etc..
> > and the opening price filled me excel sheet..
> >
> > i also tried the =RCHGetYahooHistory("MSFT")
> > and i got NOVALUE...
> >
> > any advice would be appreciated, i actually learned from your last
> > comment.. not sure why that one cell would work but the ALL of them
> > dont??
> >
>

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

Re: I feel like a complete idiot...

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

Wed Oct 12, 2011 10:02 pm (PDT)



If you're getting a #NAME? error from entering this in a new workbook:

=RCHGetYahooHistory("MSFT")

...then you probably don't have the add-in installed.

I'm having a lot of trouble following your problem descriptions.

For aerospace-response, did you do a "Find and Replace All" on the path that
showed up in the cell?

On Wed, Oct 12, 2011 at 9:41 PM, scott <scott_grant2@yahoo.com> wrote:

> sorry, i meant #NAME?
> im betting youv seen those a million times..
> what is weird though is the setting up pulling from yahoo
> example with 3 stocks in the links worked for the 1st cell
> it didnt work really for the others, or nothing popped up..(NULL - nothing)
> but when i tried the aero-space response i get #NAME?
>
> i hope that helps
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (6)
Recent Activity
Visit Your Group
Yahoo! News

Fashion News

What's the word on

fashion and style?

Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

New web site?

Drive traffic now.

Get your business

on Yahoo! search.

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 22.02

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)