[smf_addin] Digest Number 3426

EXCEL Stock Market Functions Add-in Group

7 Messages

Digest #3426

1a

Re: Extract Closing Price From Bloomberg by bushpilote

2a

^DJUSxx closings from Yahoo by hjreed@swbell.net

2b

Re: ^DJUSxx closings from Yahoo by "Randy Harmelink" rharmelink

2c

Re: ^DJUSxx closings from Yahoo by hjreed@swbell.net

2d

Re: ^DJUSxx closings from Yahoo by "Randy Harmelink" rharmelink

3a

Any tips on how to get the correct URL for a given page to grab the by mail_to_subu

3b

Re: Any tips on how to get the correct URL for a given page to grab by "Randy Harmelink" rharmelink

Messages

1a

Re: Extract Closing Price From Bloomberg

Sat Jun 6, 2015 10:35 am (PDT) . Posted by:

bushpilote

Terrific. Thanks again Randy. Keep well.

Reply to sender . Reply to group . Reply via Web Post . All Messages (13) . Top ^

2a

^DJUSxx closings from Yahoo

Sat Jun 6, 2015 1:58 pm (PDT) . Posted by:

hjreed@swbell.net

Randy

I'm trying to pull ^DJUSxx closings from Yahoo. The thread I've pasted at the bottom explains why RCHGetYahooQuotes() won't work, but towards the end of that conversation you suggest using RCHGetTableCell().

I've tried RCHGetTableCell(), but I couldn't get to the right row that contained the Close. This isn't the code I had (I've changed it too many times trying to get it to work). but here's what it looks like now:
=RCHGetTableCell(CONCATENATE(CHAR(34),"http://finance.yahoo.com/q?s=",CHAR(37),"5E",RIGHT(E382,6),CHAR(34),",1,",CHAR(34),"yfs_l10_",CHAR(34)),,,,,,,2)


I then tried smfGetTagContent. I keep getting #value (which I know is a xls error), but I don't why it won't work.

Here's the string I'm trying to create that will work:
=smfGetTagContent("http://finance.yahoo.com/q?s=%5EDJUSOS","span",0,"yfs_l10_")


Here's how I'm trying to pull it.

=smfGetTagContent(CONCATENATE("(",CHAR(34),"http://finance.yahoo.com/q?s=%5ED",RIGHT(D382,6),CHAR(34),",",CHAR(34),"span",CHAR(34),",0,",CHAR(34),"yfs_l10_",CHAR(34),")"))



There has to be a simpler way to get this data!!
I'm stuck and not sure how to get what I need.

Suggestions? (or an example of how you would do it?)



For reference here's the discussion thread mentioned above:

https://groups.yahoo.com/neo/groups/smf_addin/conversations/topics/16312 https://groups.yahoo.com/neo/groups/smf_addin/conversations/topics/16312



Thanks again for your time.

Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^

2b

Re: ^DJUSxx closings from Yahoo

Sat Jun 6, 2015 4:56 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

You're making it much harder than what it needs to be. Try:

=smfConvertData(smfGetTagContent("http://finance.yahoo.com/q?s=
"&B5,"span",0,"yfs_l10_"))

...where the ticker symbol (i.e. "^DJUSOS") is in cell B5. I'm not sure
what you're usins?

On Sat, Jun 6, 2015 at 1:58 PM, hjreed@... wrote:

> I'm trying to pull ^DJUSxx closings from Yahoo. The thread I've pasted
> at the bottom explains why RCHGetYahooQuotes() won't work, but towards
> the end of that conversation you suggest using RCHGetTableCell().
>
> I've tried RCHGetTableCell(), but I couldn't get to the right row that
> contained the Close. This isn't the code I had (I've changed it too many
> times trying to get it to work). but here's what it looks like now:
>
> =RCHGetTableCell(CONCATENATE(CHAR(34),"http://finance.yahoo.com/q?s=
> ",CHAR(37),"5E",RIGHT(E382,6),CHAR(34),",1,",CHAR(34),"yfs_l10_",CHAR(34)),,,,,,,2)
>
> I then tried smfGetTagContent. I keep getting #value (which I know is a
> xls error), but I don't why it won't work.
>
> Here's the string I'm trying to create that will work:
>
> =smfGetTagContent("http://finance.yahoo.com/q?s=%5EDJUSOS
> ","span",0,"yfs_l10_")
>
> Here's how I'm trying to pull it.
>
> =smfGetTagContent(CONCATENATE("(",CHAR(34),"
> http://finance.yahoo.com/q?s=%5ED
> ",RIGHT(D382,6),CHAR(34),",",CHAR(34),"span",CHAR(34),",0,",CHAR(34),"yfs_l10_",CHAR(34),")"))
>
> There has to be a simpler way to get this data!!
>
> I'm stuck and not sure how to get what I need.
>
> Suggestions? (or an example of how you would do it?)
>
> For reference here's the discussion thread mentioned above:
>
> *https://groups.yahoo.com/neo/groups/smf_addin/conversations/topics/16312*
> <https://groups.yahoo.com/neo/groups/smf_addin/conversations/topics/16312>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^

2c

Re: ^DJUSxx closings from Yahoo

Sat Jun 6, 2015 10:12 pm (PDT) . Posted by:

hjreed@swbell.net

I said I was stuck!!! and as always, you help us out.


The doc that I have doesn't have smfConvertData in it (it must be old) and I poked around ogres-crypt.com/SMF/ as well but didn't see anything. What function(s) does it take cover?


As always, THANK YOU for all your time and effort...

Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^

2d

Re: ^DJUSxx closings from Yahoo

Sat Jun 6, 2015 11:26 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

It's just a utility routine I use that will convert the passed string into
a number if it can. Otherwise it leaves it alone. It saves getting #VALUE!
responses by trying to do the conversion like this:

=0+smfGetTagContent(...)

On Sat, Jun 6, 2015 at 10:12 PM, hjreed@swbell.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:

> I said I was stuck!!! and as always, you help us out.
>
> The doc that I have doesn't have smfConvertData in it (it must be old) and
> I poked around ogres-crypt.com/SMF/ as well but didn't see anything.
> What function(s) does it take cover?
>
> As always, THANK YOU for all your time and effort...
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^

3a

Any tips on how to get the correct URL for a given page to grab the

Sat Jun 6, 2015 2:26 pm (PDT) . Posted by:

mail_to_subu

Dear Randy and All

I wish to download Indian stock market data from the Bombay Stock exchange site

Specifically from
(a) Quaterly trends and Untitled Page http://www.bseindia.com/stock-share-price/stockreach_financials.aspx?scripcode=500112&expandable=0#ctl00_ContentPlaceHolder1_quatre

http://www.bseindia.com/stock-share-price/stockreach_financials.aspx?scripcode=500112&expandable=0#ctl00_ContentPlaceHolder1_quatre

Untitled Page http://www.bseindia.com/stock-share-price/stockreach_financials.aspx?scripcode=500112&expandable=0#ctl00_ContentPlaceHolder1_quatre


View on www.bseindia.com http://www.bseindia.com/stock-share-price/stockreach_financials.aspx?scripcode=500112&expandable=0#ctl00_ContentPlaceHolder1_quatre
Preview by Yahoo



and
(b) Annual trends Untitled Page http://www.bseindia.com/stock-share-price/stockreach_financials.aspx?scripcode=500112&expandable=0#ctl00_ContentPlaceHolder1_anntre

http://www.bseindia.com/stock-share-price/stockreach_financials.aspx?scripcode=500112&expandable=0#ctl00_ContentPlaceHolder1_anntre

Untitled Page http://www.bseindia.com/stock-share-price/stockreach_financials.aspx?scripcode=500112&expandable=0#ctl00_ContentPlaceHolder1_anntre


View on www.bseindia.com http://www.bseindia.com/stock-share-price/stockreach_financials.aspx?scripcode=500112&expandable=0#ctl00_ContentPlaceHolder1_anntre
Preview by Yahoo



Though the webpages have these different URL, they seem to keep directing me to the same web page , the quarterly trends !! In effect RCH will always grab quarterly data irrespective of which URL I use in the Gettablecell function !!

How does one go to the annual trends / annual #s page ? : Once on this http://www.bseindia.com/stock-share-price/stockreach_financials.aspx?scripcode=500112&expandable=0#ctl00_ContentPlaceHolder1_anntre http://www.bseindia.com/stock-share-price/stockreach_financials.aspx?scripcode=500112&expandable=0#ctl00_ContentPlaceHolder1_anntre page, IF one were to click on the annual trends button (on top of the table), one is directed an annual #s page !!

So, my request to this group : Any tips on how to get the correct URL for a given page to grab the data ? i.e. how to get URL from a page so that every time I get the correct page ?

thanks
regards

Subu

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

3b

Re: Any tips on how to get the correct URL for a given page to grab

Sat Jun 6, 2015 5:02 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Actually, the annual *AND* quarterly data is on the same page. The click on
the tab just toggles which to show and which to hide. Also, placeholders in
the URL cause an error, so you need to remove that. There's actually four
tables there, because they have the quarterly and annual in Cr and in
millions. You just need to change the table ID for which of the four tables
you want. That is:

=RCHGetTableCell("
http://www.bseindia.com/stock-share-price/stockreach_financials.aspx?scripcode=500112
",1,"id='cr'",">Revenue")

=RCHGetTableCell("
http://www.bseindia.com/stock-share-price/stockreach_financials.aspx?scripcode=500112
",1,"id='acr'",">Revenue")

=RCHGetTableCell("
http://www.bseindia.com/stock-share-price/stockreach_financials.aspx?scripcode=500112
",1,"id='m'",">Revenue")

=RCHGetTableCell("
http://www.bseindia.com/stock-share-price/stockreach_financials.aspx?scripcode=500112
",1,"id='am'",">Revenue")

On Sat, Jun 6, 2015 at 2:26 PM, mail_to_subu@... wrote:

>
>
> I wish to download Indian stock market data from the Bombay Stock exchange
> site
>
> Specifically from
> (a) Quaterly trends and Untitled Page
> <http://www.bseindia.com/stock-share-price/stockreach_financials.aspx?scripcode=500112&expandable=0#ctl00_ContentPlaceHolder1_quatre>
> [image: image]
> <http://www.bseindia.com/stock-share-price/stockreach_financials.aspx?scripcode=500112&expandable=0#ctl00_ContentPlaceHolder1_quatre>
> Untitled Page
> <http://www.bseindia.com/stock-share-price/stockreach_financials.aspx?scripcode=500112&expandable=0#ctl00_ContentPlaceHolder1_quatre>
> View on www.bseindia.com
> <http://www.bseindia.com/stock-share-price/stockreach_financials.aspx?scripcode=500112&expandable=0#ctl00_ContentPlaceHolder1_quatre>
> Preview by Yahoo
>
>
> and
> (b) Annual trends Untitled Page
> <http://www.bseindia.com/stock-share-price/stockreach_financials.aspx?scripcode=500112&expandable=0#ctl00_ContentPlaceHolder1_anntre>
> [image: image]
> <http://www.bseindia.com/stock-share-price/stockreach_financials.aspx?scripcode=500112&expandable=0#ctl00_ContentPlaceHolder1_anntre>
> Untitled Page
> <http://www.bseindia.com/stock-share-price/stockreach_financials.aspx?scripcode=500112&expandable=0#ctl00_ContentPlaceHolder1_anntre>
> View on www.bseindia.com
> <http://www.bseindia.com/stock-share-price/stockreach_financials.aspx?scripcode=500112&expandable=0#ctl00_ContentPlaceHolder1_anntre>
> Preview by Yahoo
>
>
> Though the webpages have these different URL, they seem to keep directing
> me to the same web page , the quarterly trends !! In effect RCH will always
> grab quarterly data irrespective of which URL I use in the Gettablecell
> function !!
>
> How does one go to the annual trends / annual #s page ? : Once on this
> http://www.bseindia.com/stock-share-price/stockreach_financials.aspx?scripcode=500112&expandable=0#ctl00_ContentPlaceHolder1_anntre
> page, IF one were to click on the annual trends button (on top of the
> table), one is directed an annual #s page !!
>
> So, my request to this group : Any tips on how to get the correct URL for
> a given page to grab the data ? i.e. how to get URL from a page so that
> every time I get the correct page ?
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF

Visit Your Group

• New Members 18

• Privacy • Unsubscribe • Terms of Use