Rabu, 23 November 2011

[smf_addin] Digest Number 2024

Messages In This Digest (20 Messages)

Messages

1a.

Need help grabbing data from table, based on assumptions

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

Tue Nov 22, 2011 7:57 am (PST)



I'm trying to sum the rows in a table under certain assumption, and I'm not sure where to start (or if doable).

website:
http://www.secform4.com/insider-trading/1488075.htm

want to set the Time Period to "1 Month".

you'll see a table of insider PURCHASE and SALE (only interested in the first table called "Common stock purchase or sale:"
I want to have a formula that adds up all the SHARES TRADED column, if the first column says PURCHASE. so for ticker "IL", there are four purchases for a total of 10,920. The 10,920 is the result i'm hoping to get.

Thanks for the help you're able to pass along.

Steve

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

Re: Need help grabbing data from table, based on assumptions

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

Tue Nov 22, 2011 8:40 am (PST)



This may seem strange but if you get the table in thru an external query you could use
problem is text in col f and purchase being one cell down from IL. However, range sizes are the same so it WORKS.

=SUMPRODUCT((D7:D22="il")*(A8:A23="purchase")*F7:F22)
==================

Don Guillett
SalesAid Software
dguillett1@gmail.com

From: friedman_steve
Sent: Tuesday, November 22, 2011 9:57 AM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Need help grabbing data from table, based on assumptions

I'm trying to sum the rows in a table under certain assumption, and I'm not sure where to start (or if doable).

website:
http://www.secform4.com/insider-trading/1488075.htm

want to set the Time Period to "1 Month".

you'll see a table of insider PURCHASE and SALE (only interested in the first table called "Common stock purchase or sale:"
I want to have a formula that adds up all the SHARES TRADED column, if the first column says PURCHASE. so for ticker "IL", there are four purchases for a total of 10,920. The 10,920 is the result i'm hoping to get.

Thanks for the help you're able to pass along.

Steve

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

Re: Need help grabbing data from table, based on assumptions

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

Tue Nov 22, 2011 9:10 am (PST)



although, i'm trying to do the same analysis for many stocks on a spreadsheet. So getting each table down to the spreadsheet first doesn't work well.
any other ideas?

--- In smf_addin@yahoogroups.com, "dguillett1" <dguillett1@...> wrote:
>
> This may seem strange but if you get the table in thru an external query you could use
> problem is text in col f and purchase being one cell down from IL. However, range sizes are the same so it WORKS.
>
> =SUMPRODUCT((D7:D22="il")*(A8:A23="purchase")*F7:F22)
> ==================
>
> Don Guillett
> SalesAid Software
> dguillett1@...
>
> From: friedman_steve
> Sent: Tuesday, November 22, 2011 9:57 AM
> To: smf_addin@yahoogroups.com
> Subject: [smf_addin] Need help grabbing data from table, based on assumptions
>
>
> I'm trying to sum the rows in a table under certain assumption, and I'm not sure where to start (or if doable).
>
> website:
> http://www.secform4.com/insider-trading/1488075.htm
>
> want to set the Time Period to "1 Month".
>
> you'll see a table of insider PURCHASE and SALE (only interested in the first table called "Common stock purchase or sale:"
> I want to have a formula that adds up all the SHARES TRADED column, if the first column says PURCHASE. so for ticker "IL", there are four purchases for a total of 10,920. The 10,920 is the result i'm hoping to get.
>
> Thanks for the help you're able to pass along.
>
> Steve
>

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

Re: Need help grabbing data from table, based on assumptions

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

Tue Nov 22, 2011 11:57 am (PST)



You're going to need to retrieve the table first and then extract the data
you need.

Unfortunately, I don't see a way to create a bookmarkable page, although
the time period is "sticky" and stored in a cookie. So you can set it first
with the EXCEL Web Query dialog or with IE.

I can get the 10920 value with:

=SUM(INDEX(RCHGetHTMLTable("
http://www.secform4.com/insider-trading/1488075.htm",">Common
Stock",1,"",1,,99,6),,6))

...but that would still be 10920 even if one or more of transactions were
sales.

On Tue, Nov 22, 2011 at 8:57 AM, friedman_steve <friedman_steve@yahoo.com>wrote:

> I'm trying to sum the rows in a table under certain assumption, and I'm
> not sure where to start (or if doable).
>
> website:
> http://www.secform4.com/insider-trading/1488075.htm
>
> want to set the Time Period to "1 Month".
>
> you'll see a table of insider PURCHASE and SALE (only interested in the
> first table called "Common stock purchase or sale:"
> I want to have a formula that adds up all the SHARES TRADED column, if the
> first column says PURCHASE. so for ticker "IL", there are four purchases
> for a total of 10,920. The 10,920 is the result i'm hoping to get.
>
> Thanks for the help you're able to pass along.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
2a.

Best execution

Posted by: "Brent" brent@carolopolis.com   bebentrim

Tue Nov 22, 2011 10:23 am (PST)



I am trying to figure a quick and easy way to verify best execution using Volume Weighted Average Price (VWAP) method.

For example on 11/15/2011 I bought 102 shares of GE at 16.0182.

VWAP = dollar volume / share volume

The dollar volume of a security is calculated by multiplying the number of shares times the share price of each trade and combining the total sum of all the trades throughout a given day.

Share volume is defined as the total number of shares traded throughout the day.

is there any manner to access historical share price at each trade?

Any help would be appreciated.

Brent

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

Re: Best execution

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

Tue Nov 22, 2011 11:42 am (PST)



You probably need a very specialized data provider. I don't know of one.
Probably would not be cheap.

The closest I could think of using free data would be to extract 1-minute
data from barchart.com's charting page.

On Tue, Nov 22, 2011 at 11:23 AM, Brent <brent@carolopolis.com> wrote:

> I am trying to figure a quick and easy way to verify best execution using
> Volume Weighted Average Price (VWAP) method.
>
> For example on 11/15/2011 I bought 102 shares of GE at 16.0182.
>
> VWAP = dollar volume / share volume
>
> The dollar volume of a security is calculated by multiplying the number of
> shares times the share price of each trade and combining the total sum of
> all the trades throughout a given day.
>
> Share volume is defined as the total number of shares traded throughout
> the day.
>
> is there any manner to access historical share price at each trade?
>
> Any help would be appreciated.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
3a.

Looking for the ticker symbol for SPYDER GOLD.

Posted by: "Dennis Sesar" dennis@zis.com   sesard

Tue Nov 22, 2011 3:23 pm (PST)



All the literature that I can find is that the ticker symbol is GLD. If I
use GLD I get an error . Any ideas?

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

Re: Looking for the ticker symbol for SPYDER GOLD.

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

Tue Nov 22, 2011 3:26 pm (PST)



GLD is the correct symbol. But as an ETF, it won't have the same data
available that a mutual fund or a stock would have. So you certainly could
get an error depending on what data you're trying to retrieve.

Since you didn't provide any specifics, that's the only help I can offer.

On Tue, Nov 22, 2011 at 4:08 PM, Dennis Sesar <dennis@zis.com> wrote:

>
> All the literature that I can find is that the ticker symbol is GLD. If
> I use GLD I get an error . Any ideas?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (10)
3c.

Re: Looking for the ticker symbol for SPYDER GOLD.

Posted by: "Dennis Sesar" dennis@zis.com   sesard

Tue Nov 22, 2011 3:36 pm (PST)



Thank you for your quick response. I am trying to get element 25 The Price

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Tuesday, November 22, 2011 3:27 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Looking for the ticker symbol for SPYDER GOLD.

GLD is the correct symbol. But as an ETF, it won't have the same data available that a mutual fund or a stock would have. So you certainly could get an error depending on what data you're trying to retrieve.

Since you didn't provide any specifics, that's the only help I can offer.

On Tue, Nov 22, 2011 at 4:08 PM, Dennis Sesar <dennis@zis.com> wrote:

All the literature that I can find is that the ticker symbol is GLD. If I use GLD I get an error . Any ideas?

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

Re: Looking for the ticker symbol for SPYDER GOLD.

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

Tue Nov 22, 2011 3:43 pm (PST)



Element 25 is from an MSN print page for stock data, which is why it won't
work for an ETF. Oddly enough, though, it does work for a few ETFs -- just
not GLD.

You should be using the RCHGetYahooQuotes() function to get quotes data.

On Tue, Nov 22, 2011 at 4:36 PM, Dennis Sesar <dennis@zis.com> wrote:

>
> Thank you for your quick response. I am trying to get element 25 The
> Price
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (10)
3e.

Re: Looking for the ticker symbol for SPYDER GOLD.

Posted by: "Dennis Sesar" dennis@zis.com   sesard

Tue Nov 22, 2011 3:44 pm (PST)



TY

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Tuesday, November 22, 2011 3:44 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Looking for the ticker symbol for SPYDER GOLD.

Element 25 is from an MSN print page for stock data, which is why it won't work for an ETF. Oddly enough, though, it does work for a few ETFs -- just not GLD.

You should be using the RCHGetYahooQuotes() function to get quotes data.

On Tue, Nov 22, 2011 at 4:36 PM, Dennis Sesar <dennis@zis.com> wrote:

Thank you for your quick response. I am trying to get element 25 The Price

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

Re: Looking for the ticker symbol for SPYDER GOLD.

Posted by: "Dennis Sesar" dennis@zis.com   sesard

Tue Nov 22, 2011 4:55 pm (PST)



I am trying to use the fooling function =RCHGetYahooQuotes("ALL",L1)

I just get a blank cell. Must be doing something wrong.

I think it should return the closing price for Allstate

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Dennis Sesar
Sent: Tuesday, November 22, 2011 3:45 PM
To: smf_addin@yahoogroups.com
Subject: RE: [smf_addin] Looking for the ticker symbol for SPYDER GOLD.

TY

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Tuesday, November 22, 2011 3:44 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Looking for the ticker symbol for SPYDER GOLD.

Element 25 is from an MSN print page for stock data, which is why it won't work for an ETF. Oddly enough, though, it does work for a few ETFs -- just not GLD.

You should be using the RCHGetYahooQuotes() function to get quotes data.

On Tue, Nov 22, 2011 at 4:36 PM, Dennis Sesar <dennis@zis.com> wrote:

Thank you for your quick response. I am trying to get element 25 The Price

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

Re: Looking for the ticker symbol for SPYDER GOLD.

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

Tue Nov 22, 2011 5:13 pm (PST)



The L1 in your formula is a cell reference, not a string literal. Try:

=RCHGetYahooQuotes("ALL","l1")

On Tue, Nov 22, 2011 at 5:55 PM, Dennis Sesar <dennis@zis.com> wrote:

>
> I am trying to use the fooling function =RCHGetYahooQuotes("ALL",L1)****
>
> ** **
>
> I just get a blank cell. Must be doing something wrong.****
>
> ** **
>
> I think it should return the closing price for Allstate
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (10)
3h.

Re: Looking for the ticker symbol for SPYDER GOLD.

Posted by: "Dennis Sesar" dennis@zis.com   sesard

Tue Nov 22, 2011 6:16 pm (PST)



Do you have to put it in quotes?

Sent from my iPhone

On Nov 22, 2011, at 5:13 PM, Randy Harmelink <rharmelink@gmail.com> wrote:

> The L1 in your formula is a cell reference, not a string literal. Try:
>
> =RCHGetYahooQuotes("ALL","l1")
>
>
> On Tue, Nov 22, 2011 at 5:55 PM, Dennis Sesar <dennis@zis.com> wrote:
>
> I am trying to use the fooling function =RCHGetYahooQuotes("ALL",L1)
>
>
>
> I just get a blank cell. Must be doing something wrong.
>
>
>
> I think it should return the closing price for Allstate
>
>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (10)
3i.

Re: Looking for the ticker symbol for SPYDER GOLD.

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

Tue Nov 22, 2011 6:20 pm (PST)



Yes. That's what a string literal is. Just like you did with the ticker
symbol.

Or, you can put the literal strings in a worksheet cell, and refer to those.

On Tue, Nov 22, 2011 at 7:16 PM, Dennis Sesar <dennis@zis.com> wrote:

>
> Do you have to put it in quotes?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (10)
3j.

Re: Looking for the ticker symbol for SPYDER GOLD.

Posted by: "Dennis Sesar" dennis@zis.com   sesard

Tue Nov 22, 2011 7:48 pm (PST)



Thank you once again

Sent from my iPad

On Nov 22, 2011, at 6:20 PM, Randy Harmelink <rharmelink@gmail.com> wrote:

> Yes. That's what a string literal is. Just like you did with the ticker symbol.
>
> Or, you can put the literal strings in a worksheet cell, and refer to those.
>
>
> On Tue, Nov 22, 2011 at 7:16 PM, Dennis Sesar <dennis@zis.com> wrote:
>
> Do you have to put it in quotes?
>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (10)
4a.

Re: for us Canadians - any way to pull TSX/TSE specific data from Go

Posted by: "staspmr" staspmr@yahoo.ca   staspmr

Tue Nov 22, 2011 8:07 pm (PST)



I have no trouble with msn, just add ".to" at the end. You can use "&" in excel to add it automatically.

However, I do have trouble with ADVfn, I tried every format imaginable, including tse:, tsx:, .to, -t, etc

e.g.
http://ca.advfn.com/p.php?pid=cafinancials&btn=annual_reports&mode=&symbol=TSE%3ARIM

=RCHGetElementNumber("rim.to", 5716) Annual Income Statement -- Basic EPS -- Continuing -- FY1

returns error for every permutation of the ticker label

any solutions?

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Sample URLs, please. I don't do anything with TSX/TSE stocks.
>
> On Thu, Sep 22, 2011 at 9:04 PM, r d <freddy@...> wrote:
>
> > Hi
> > Awesome plugin....but
> >
> > Is there a way import "current" and "descriptive" data for TSX/TSE stocks
> > like:
> > Name of Stock
> > Description
> > Current Price
> >
> > ???
> >
> > Seems I can import historical Statement data....but current/descriptive
> > data? Apparently MSN doesn't like Canada...
> >
> > help :(
> >
>

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

Re: for us Canadians - any way to pull TSX/TSE specific data from Go

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

Tue Nov 22, 2011 8:12 pm (PST)



The AdvFN element definitions don't use the "ca" prefix on the URL items.
However, this does work for me:

=RCHGetElementNumber("tse:rim", 5716)

...but I have seen reports that AdvFN does some "default country"
processing based on the location of your IP address, so what works for me
may not work for you? I don't know all the details, since I don't run into
the problem.

On Tue, Nov 22, 2011 at 8:21 PM, staspmr <staspmr@yahoo.ca> wrote:

> I have no trouble with msn, just add ".to" at the end. You can use "&" in
> excel to add it automatically.
>
> However, I do have trouble with ADVfn, I tried every format imaginable,
> including tse:, tsx:, .to, -t, etc
>
> e.g.
>
> http://ca.advfn.com/p.php?pid=cafinancials&btn=annual_reports&mode=&symbol=TSE%3ARIM
>
> =RCHGetElementNumber("rim.to", 5716) Annual Income Statement -- Basic
> EPS -- Continuing -- FY1
>
> returns error for every permutation of the ticker label
>
> any solutions?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
5a.

Ticker for Dow Jones Industrials?

Posted by: "jonbeyer_39" jonabeyer@gmail.com   jonbeyer_39

Tue Nov 22, 2011 10:56 pm (PST)



I can't get a ticker symbol to work with RCHGetYahooQuotes() for the Dow Jones Industrial index. I think either "^DJI" or "INDU" used to work, but for the last month or so they haven't.

The tickers "^IXIC" for NASDAQ and "^GSPC" for S&P still work ok.

Randy, thanks for all your efforts in this fantastic group.

Jon

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

Re: Ticker for Dow Jones Industrials?

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

Tue Nov 22, 2011 11:00 pm (PST)



When someone here asked Yahoo about ^DJI being shut down, they were told
that Yahoo no longer had the rights to distribute the DJIA quotes. INDU did
work for a while, but they must have shut down that workaround.

For your purposes, would DIA suffice?

On Tue, Nov 22, 2011 at 11:52 PM, jonbeyer_39 <jonabeyer@gmail.com> wrote:

> I can't get a ticker symbol to work with RCHGetYahooQuotes() for the Dow
> Jones Industrial index. I think either "^DJI" or "INDU" used to work, but
> for the last month or so they haven't.
>
> The tickers "^IXIC" for NASDAQ and "^GSPC" for S&P still work ok.
>
> Randy, thanks for all your efforts in this fantastic group.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
Recent Activity
Visit Your Group
Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Need traffic?

Drive customers

With search ads

on Yahoo!

Yahoo! News

Odd News

You won't believe

it, but it's true

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 02.31

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)