Sabtu, 07 Juli 2012

[smf_addin] Digest Number 2268

19 New Messages
Digest #2268
1a
3a
Re: way to prevent updates? by "jmichalak13" jmichalak13
3b
Re: way to prevent updates? by "Randy Harmelink" rharmelink
4a
Re: Barchartproblem by "Stefn Rai" rainerglobal@ymail.com
4b
Re: Barchartproblem by "Randy Harmelink" rharmelink
4c
Re: Barchartproblem by "Stefn Rai" rainerglobal@ymail.com
4d
Re: Barchartproblem by "Randy Harmelink" rharmelink
5a
Emma.org by "Steven" stevenletzer
5b
Re: Emma.org by "Randy Harmelink" rharmelink
6a
You are just amazing Randy !!! by "Subu S" mail_to_subu
6b
Re: You are just amazing Randy !!! by "Ashish Mukerji" ashishkm
7a
DJ-UBS Indices Not Reporting Values by "thomas91112" thomas91112
7b
Re: DJ-UBS Indices Not Reporting Values by "Randy Harmelink" rharmelink
8a
Stock Quotes for Australia (ASX) by "haltonbrat96" haltonbrat96
8b
Re: Stock Quotes for Australia (ASX) by "Randy Harmelink" rharmelink
8c
Re: Stock Quotes for Australia (ASX) by "haltonbrat96" haltonbrat96
8d
Re: Stock Quotes for Australia (ASX) by "Randy Harmelink" rharmelink

Fri Jul 6, 2012 4:10 am (PDT) . Posted by: "bushpilote@yahoo.ca" bushpilote
You are welcomed Randy.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Thanks for the heads up. I just uploaded a new version of the template.
>
> On Thu, Jul 5, 2012 at 7:11 AM, <bushpilote@...> wrote:
>
> > Good morning Randy,
> > In using the latest BarChart quotes extract I uncovered a problem with the
> > monthly data. The closing prices in Column K show up as "#Values".
> >
> > This is due to the monthly "date" section of the tooltip in Column D
> > having one less character string then all other period charts from the
> > 5-minute to the weekly.
> >
> > One way of fixing this problem is to modify the formulae in H13:K13 by
> > adding an if statement to handle the monthly data:
> >
> >
> > H13: =IF(ISERROR($B13),"--",IF($G$3<>"MO - Monthly
> > Chart",VALUE(SUBSTITUTE(smfWord($E13,6,","),"'","")),VALUE(SUBSTITUTE(smfWord($E13,5,","),"'",""))))
> >
> > I13: =IF(ISERROR($B13),"--",IF($G$3<>"MO - Monthly
> > Chart",VALUE(SUBSTITUTE(smfWord($E13,7,","),"'","")),VALUE(SUBSTITUTE(smfWord($E13,6,","),"'",""))))
> >
> > J13: =IF(ISERROR($B13),"--",IF($G$3<>"MO - Monthly
> > Chart",VALUE(SUBSTITUTE(smfWord($E13,8,","),"'","")),VALUE(SUBSTITUTE(smfWord($E13,7,","),"'",""))))
> >
> > K13: =IF(ISERROR($B27),"--",IF($G$3<>"MO - Monthly
> > Chart",VALUE(SUBSTITUTE(smfWord($E27,9,","),"'","")),VALUE(SUBSTITUTE(smfWord($E27,8,","),"'",""))))
> >
> > ......and copying them all down to row 276.
> >
> > Regards
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
> >
>

Fri Jul 6, 2012 6:27 am (PDT) . Posted by: "colinmmoors" colinmmoors
Randy, is it possible to force RCHGetYahooHistory to retrieve the second row of data if the first and second row are the same date (which seems to be happening outside market hours)? The first row seems to be inaccurate while the second row is good, but right now it is pulling from the first row. thanks

--- In smf_addin@yahoogroups.com, "investor952" <investor952@...> wrote:
>
> I sent them a message regarding this a few days ago..haven't yet received a response and problem is still there.
>
> --- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@> wrote:
> >
> > What was Yahoo's response when you asked them about it?
> >
> > On Thu, Jul 5, 2012 at 3:59 AM, colinmmoors <colinmmoors@> wrote:
> >
> > > please go to history for SPY in yahoo finance...
> > > http://finance.yahoo.com/q/hp?s=SPY+Historical+Prices
> > >
> > > do you see two rows for the most recent date with different data?
> > >
> > > this has been the case now for almost a week
> > >
> >
>

Fri Jul 6, 2012 11:39 pm (PDT) . Posted by: "Randy Harmelink" rharmelink
Give version 2.1.2012.07.06 of the add-in from the "Works in Progress"
folder a try. I think I've got it working for the repeating dates. I have
it overlaying the previous row if the next row has the same date.

On Fri, Jul 6, 2012 at 6:27 AM, colinmmoors <colinmmoors@yahoo.com> wrote:

> Randy, is it possible to force RCHGetYahooHistory to retrieve the second
> row of data if the first and second row are the same date (which seems to
> be happening outside market hours)? The first row seems to be inaccurate
> while the second row is good, but right now it is pulling from the first
> row. thanks
>
Fri Jul 6, 2012 7:37 am (PDT) . Posted by: "jmichalak13" jmichalak13
I'm a bit stumped here. I am using the smfUpdateDownloadTable macro as per your instructions, but I am still getting updates when I open my workbook. I had set up the table to fit your specs listed for the macro

--- In smf_addin@yahoogroups.com, "Randy Harmelink" <rharmelink@...> wrote:
>
> I use the smfUpdateDownloadTable macro for such situations. This would
> actually place values into the table, so no recalculation occurs. When I
> want "fresh" values, I just rerun the macro. Here is the documentation for
> the table setup from the macro's module:
>
> 1. The upper left hand corner cell of the table needs to have a range
> name of "Ticker"
> 2. The cells below the "Ticker" cell should be filled in with ticker
> symbols, one per cell
> 3. The cells to the right of the "Ticker" cell should be filled with
> column titles
> 4. The cells above the column titles need to be filled in with SMF
> add-in formulas or element numbers. Use five tildas as a substitute for a
> ticker symbol. For example, any of the following text strings could be used
> to get "Market Capitalization" from Yahoo:
>
> 941
> RCHGetElementNumber("~~~~~", 941)
> RCHGetTableCell("http://finance.yahoo.com/q/ks?s=~~~~~",1,">Market
> Cap")
>
> A recent addition to the process is that if the cell entry above a column
> title is an "x", that column is skipped from any update -- I use this for
> any calculation columns I want in the middle of my table. In all of my
> workbooks, I assign the smfUpdateDownloadTable macro to a keyboard shortcut
> of Ctrl-Shift-J.
>
> On 10/19/07, rjlabs <rick@...> wrote:
> >
> > I have a large table of =RCHGetElementNumber() that has historical
> > quarterly info that I only need to update sporadically. It takes about
> > 15 minutes to update (about 60 tickers x 150 fields).
> >
> > Is there a way I can control when those functions run and update? I
> > want to be able to open the spreadsheet with perhaps stale data and
> > not have it update. However I don't want to set calculation to manual
> > because other parts of the workbook I want regularly calculating. (Any
> > programmer guide or FAQ or embedded docs on this just point me to them.)
>

Fri Jul 6, 2012 7:40 am (PDT) . Posted by: "Randy Harmelink" rharmelink
How could you possibly get an update? There would be no formulas to
calculate/ The macro enters values into the table.

On Fri, Jul 6, 2012 at 7:11 AM, jmichalak13 <jmichalak13@yahoo.com> wrote:

> I'm a bit stumped here. I am using the smfUpdateDownloadTable macro as
> per your instructions, but I am still getting updates when I open my
> workbook. I had set up the table to fit your specs listed for the macro
>
Fri Jul 6, 2012 8:20 am (PDT) . Posted by: "Stefn Rai" rainerglobal@ymail.com
Randy, RCHGetTableCell("http://www.barchart.com/cheatsheet.php?sym=ESU12,1,"Pivot Point 2nd Level Resistance") worked great to get the resistance and support levels if they are located to the left in the table. However, the "Pivot Point" level for some reason is located on the right hand side, with the level to the left( as supposed to the text to the left and the level to the right). Any idea as to how to retrieve the "Pivot Point" level?
Best Regards,
 


________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Tuesday, July 3, 2012 11:49 AM
Subject: Re: [smf_addin] Re: Barchartproblem


 

With FireFox, I just right-click on the web page and choose the "View Page Source" option...

On Tue, Jul 3, 2012 at 8:08 AM, Stefan Rainer <stefan_rainer@ymail.com> wrote:

>
>Thanks Randy! Do you think you could provide an example of how to find the source? I'm not very proficient at this.
>

Fri Jul 6, 2012 8:41 am (PDT) . Posted by: "Randy Harmelink" rharmelink
You should just be able to use:

=RCHGetTableCell("http://www.barchart.com/cheatsheet.php?sym=ESU12",-2,"Pivot
Point 2nd Level Resistance")

...instead. The "-2" says to take the 2nd column to the left of the end of
the row the search string was found on. So then it shouldn't matter if your
search string is to the left or to the right of the price, because both
will look for the table cell from the end of the row instead of after the
search string.

On Fri, Jul 6, 2012 at 8:20 AM, Stefn Rai <rainerglobal@ymail.com> wrote:

>
> Randy, RCHGetTableCell("
> http://www.barchart.com/cheatsheet.php?sym=ESU12,1,"Pivot Point 2nd Level
> Resistance") worked great to get the resistance and support levels if they
> are located to the left in the table. However, the "Pivot Point" level for
> some reason is located on the right hand side, with the level to the left(
> as supposed to the text to the left and the level to the right). Any idea
> as to how to retrieve the "Pivot Point" level?
>
>
Fri Jul 6, 2012 8:52 am (PDT) . Posted by: "Stefn Rai" rainerglobal@ymail.com
I would think so too, but when using
RCHGetTableCell("http://www.barchart.com/cheatsheet.php?sym=ESU12",-2,"Pivot Point") I just receive "Error"
Regards,

  

________________________________
From: Randy Harmelink <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Friday, July 6, 2012 10:41 AM
Subject: Re: [smf_addin] Re: Barchartproblem


 

You should just be able to use:

=RCHGetTableCell("http://www.barchart.com/cheatsheet.php?sym=ESU12",-2,"Pivot Point 2nd Level Resistance")

...instead. The "-2" says to take the 2nd column to the left of the end of the row the search string was found on. So then it shouldn't matter if your search string is to the left or to the right of the price, because both will look for the table cell from the end of the row instead of after the search string.

On Fri, Jul 6, 2012 at 8:20 AM, Stefn Rai <rainerglobal@ymail.com> wrote:

>
>Randy, RCHGetTableCell("http://www.barchart.com/cheatsheet.php?sym=ESU12,1,%22Pivot Point 2nd Level Resistance") worked great to get the resistance and support levels if they are located to the left in the table. However, the "Pivot Point" level for some reason is located on the right hand side, with the level to the left( as supposed to the text to the left and the level to the right). Any idea as to how to retrieve the "Pivot Point" level?
>

Fri Jul 6, 2012 9:13 am (PDT) . Posted by: "Randy Harmelink" rharmelink
That's because the first instance of the "Pivot Point" search string you
are using is in a meta tag that describes the content of the web page, NOT
the one you want in the table:

<meta name="description" content="E-Mini S&P 500 (ESU12) Trader's Cheat
Sheet, Support Resistance, Pivot Point, Key Turning Points." />

You would need to differentiate between the two. For example:

=RCHGetTableCell("http://www.barchart.com/cheatsheet.php?sym=ESU12",-2,">Pivot
Point")

I often use the trailing closure of a previous HTML tag to make a search
string unique -- but it's usage does depend on how the web page is coded.

On Fri, Jul 6, 2012 at 8:52 AM, Stefn Rai <rainerglobal@ymail.com> wrote:

>
> I would think so too, but when using
> RCHGetTableCell("
> http://www.barchart.com/cheatsheet.php?sym=ESU12",-2,"Pivot Point") I
> just receive "Error"
>
>
Fri Jul 6, 2012 9:45 am (PDT) . Posted by: "Steven" stevenletzer
At the web, I want to bring in the table of trades. The cusip= is the reference cusip number.

http://emma.msrb.org/SecurityView/SecurityDetailsTrades.aspx?cusip=4551673K0

Here is a sample of the data:

Trade Date/Time Settlement Date Price (%) Yield (%) Trade Amt ($) Trade Submission Type
06/14/2010 : 02:13 PM 06/17/2010 100.25 4.443 5,000 Customer bought
06/10/2010 : 02:37 PM 06/15/2010 97.75 4.74 5,000 Customer sold
05/27/2010 : 12:02 PM 06/02/2010 102.4380 4.174 20,000 Customer bought

I need help on writing the getwebtable. Please.

Fri Jul 6, 2012 9:51 am (PDT) . Posted by: "Randy Harmelink" rharmelink
As basic as it gets -- just target a column header within the table:

=RCHGetHTMLTable("
http://emma.msrb.org/SecurityView/SecurityDetailsTrades.aspx?cusip=4551673K0
",">Settlement",-1,"",1)

On Fri, Jul 6, 2012 at 9:45 AM, Steven <stevenletzer@yahoo.com> wrote:

> At the web, I want to bring in the table of trades. The cusip= is the
> reference cusip number.
>
>
> http://emma.msrb.org/SecurityView/SecurityDetailsTrades.aspx?cusip=4551673K0
>
> Here is a sample of the data:
>
> Trade Date/Time Settlement Date Price (%) Yield (%) Trade Amt ($) Trade
> Submission Type
> 06/14/2010 : 02:13 PM 06/17/2010 100.25 4.443 5,000 Customer bought
> 06/10/2010 : 02:37 PM 06/15/2010 97.75 4.74 5,000 Customer sold
> 05/27/2010 : 12:02 PM 06/02/2010 102.4380 4.174 20,000 Customer bought
>
> I need help on writing the getwebtable. Please.
>
Fri Jul 6, 2012 12:30 pm (PDT) . Posted by: "Subu S" mail_to_subu

You are just Amazing Randy ...

I come here once in 3 months or probably even less... I casually scroll thru a few pages of messages ...just the short version ... and I see you replying day in day out

This is just amaaaaaazing .....

My very sincere thanks (as a user) and heartiest kudos

regards
Subu

Fri Jul 6, 2012 12:40 pm (PDT) . Posted by: "Ashish Mukerji" ashishkm
Same sentiments here ... couldn't have said it better ... thx. Randy ...

 
Ashish         
Fri Jul 6, 2012 6:11 pm (PDT) . Posted by: "thomas91112" thomas91112
Yahoo Finance supports the Dow Jones-UBS commodity indices. I have been accessing this data for over a year using RCHGetYahooHistory for the following items: ^DJUBSYTR and ^DJUBCNTR. For some reason, they are as of this last week not displaying any historical data. Any ideas? I have gone into Yahoo Finance and they still display all the historical data there but not through the SMF add-in.
Thanks
Mike


Fri Jul 6, 2012 6:20 pm (PDT) . Posted by: "Randy Harmelink" rharmelink
Yahoo is displaying the historical quotes, but they are not making the CSV
file available. If you check, you'll see there is no "Download Data" button
on the historical quotes page. And the CSV file is what
RCHGetYahooHistory() needs for its data.

I suspect this is another example of Dow Jones clamping down on access to
their "proprietary" data.

On Fri, Jul 6, 2012 at 6:11 PM, thomas91112 <thomas91112@yahoo.com> wrote:

> Yahoo Finance supports the Dow Jones-UBS commodity indices. I have been
> accessing this data for over a year using RCHGetYahooHistory for the
> following items: ^DJUBSYTR and ^DJUBCNTR. For some reason, they are as of
> this last week not displaying any historical data. Any ideas? I have gone
> into Yahoo Finance and they still display all the historical data there but
> not through the SMF add-in.
>
Fri Jul 6, 2012 8:08 pm (PDT) . Posted by: "haltonbrat96" haltonbrat96
I have tried to use the RCHGETYAHOOQUOTES() function to obtain stock quotes from Australia. The function works fine, as long as I don't put the 'Server ID' code of "AU" in. This is what my formula looks like: (=rchgetyahooquotes("TLS",,AU,NOW(),)}. I also tried it with a 'Server ID' code of "AU".

What am I doing wrong.

Fri Jul 6, 2012 8:12 pm (PDT) . Posted by: "Randy Harmelink" rharmelink
The "server ID" hasn't been used by Yahoo for well over a year now. I left
the coding in, though, in case they return to it in the future. However,
the next release of the add-in will automatically set it to the default
server that Yahoo always uses, no matter what "server ID" is used.

So you'll need to use the Australian suffix on your ticker symbol without
using the "server ID" parameter.

On Fri, Jul 6, 2012 at 8:03 PM, haltonbrat96 <haltonbrat96@yahoo.com.au>wrote:

> I have tried to use the RCHGETYAHOOQUOTES() function to obtain stock
> quotes from Australia. The function works fine, as long as I don't put the
> 'Server ID' code of "AU" in. This is what my formula looks like:
> (=rchgetyahooquotes("TLS",,AU,NOW(),)}. I also tried it with a 'Server ID'
> code of "AU".
>
> What am I doing wrong.
>
Fri Jul 6, 2012 8:21 pm (PDT) . Posted by: "haltonbrat96" haltonbrat96


--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> The "server ID" hasn't been used by Yahoo for well over a year now. I left
> the coding in, though, in case they return to it in the future. However,
> the next release of the add-in will automatically set it to the default
> server that Yahoo always uses, no matter what "server ID" is used.
>
> So you'll need to use the Australian suffix on your ticker symbol without
> using the "server ID" parameter.
>
> On Fri, Jul 6, 2012 at 8:03 PM, haltonbrat96 <haltonbrat96@...>wrote:
>
> > I have tried to use the RCHGETYAHOOQUOTES() function to obtain stock
> > quotes from Australia. The function works fine, as long as I don't put the
> > 'Server ID' code of "AU" in. This is what my formula looks like:
> > (=rchgetyahooquotes("TLS",,AU,NOW(),)}. I also tried it with a 'Server ID'
> > code of "AU".
> >
> > What am I doing wrong.
> >
>
I tried using (=rchgetyahooquotes("TLS ASX",,,NOW(),)} and now I just get N/A's for the values. Can you give me a clue as to what the suffix should be for the Australian Stock Exchange other then ASX. (also tried without the space eg TLSASX)

Fri Jul 6, 2012 8:45 pm (PDT) . Posted by: "Randy Harmelink" rharmelink
I think the ticker symbol you want is "TLS.AX".

On Fri, Jul 6, 2012 at 8:21 PM, haltonbrat96 <haltonbrat96@yahoo.com.au>wrote:

> I tried using (=rchgetyahooquotes("TLS ASX",,,NOW(),)} and now I just get
> N/A's for the values. Can you give me a clue as to what the suffix should
> be for the Australian Stock Exchange other then ASX. (also tried without
> the space eg TLSASX)
>
Diposting oleh EXCEL FEED LEARNING di 04.09

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)