[smf_addin] Digest Number 2213

EXCEL Stock Market Functions Add-in

Messages In This Digest (25 Messages)

1a.

Info on csv download From: bagzigingahouse

1b.

Re: Info on csv download From: Randy Harmelink

1c.

Re: Info on csv download From: bagzigingahouse

1d.

Re: Info on csv download From: Randy Harmelink

1e.

Re: Info on csv download From: bagzigingahouse

1f.

Re: Info on csv download From: bagzigingahouse

1g.

Re: Info on csv download From: Randy Harmelink

1h.

Re: Info on csv download From: bagzigingahouse

1i.

Re: Info on csv download From: Randy Harmelink

2a.

Hi, Japanese Tickers earning date From: Chirag Vyas

2b.

Re: Hi, Japanese Tickers earning date From: Randy Harmelink

2c.

Re: Hi, Japanese Tickers earning date From: Randy H

2d.

Re: Hi, Japanese Tickers earning date From: Chirag Vyas

2e.

Re: Hi, Japanese Tickers earning date From: Randy Harmelink

3a.

Re: How to get 1 or 5 minute data From: kerekoukin

4a.

Historical Market Cap From: Brent

4b.

Re: Historical Market Cap From: Brent

4c.

Re: Historical Market Cap From: Randy Harmelink

5a.

Macintosh users? From: JP

5b.

Re: Macintosh users? From: Randy Harmelink

6a.

Re: Correlation Data From: oyz79

6b.

Re: Correlation Data From: Randy Harmelink

6c.

Re: Correlation Data From: oyz79

6d.

Re: Correlation Data From: Randy Harmelink

6e.

Re: Correlation Data From: oyz79

View All Topics | Create New Topic

Messages

1a.

Info on csv download

Posted by: "bagzigingahouse" bagzigingahouse@gmail.com   bagzigingahouse

Wed May 16, 2012 2:58 am (PDT)

Hello,

i would kindly like to ask, if there is maybe a function among SMF that allows daily download from a csv file posted on the web? What i would like to do is to build a spreadsheet that will update with daily prices from multiple csv files that are being posted at CBOE website on a daily basis. Is this possibile to achieve with some of the SMF functions or is this a task that requires more serious programming?

Thank you
Igor

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (9)

1b.

Re: Info on csv download

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

Wed May 16, 2012 4:35 am (PDT)

More details on the CBOE files please...

Possibly the smfGetCSVFile() function could do the trick.

On Wed, May 16, 2012 at 2:58 AM, bagzigingahouse
<bagzigingahouse@gmail.com>wrote:

>
> i would kindly like to ask, if there is maybe a function among SMF that
> allows daily download from a csv file posted on the web? What i would like
> to do is to build a spreadsheet that will update with daily prices from
> multiple csv files that are being posted at CBOE website on a daily basis.
> Is this possibile to achieve with some of the SMF functions or is this a
> task that requires more serious programming?
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (9)

1c.

Re: Info on csv download

Posted by: "bagzigingahouse" bagzigingahouse@gmail.com   bagzigingahouse

Wed May 16, 2012 4:43 am (PDT)

The files are contract months located here:

http://cfe.cboe.com/Products/historicalVIX.aspx

Each contract has its own file. Im going to review how the smfGetCSVFile() function works in the meanwhile. Any further help is appreciated.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> More details on the CBOE files please...
>
> Possibly the smfGetCSVFile() function could do the trick.
>
> On Wed, May 16, 2012 at 2:58 AM, bagzigingahouse
> <bagzigingahouse@...>wrote:
>
> >
> > i would kindly like to ask, if there is maybe a function among SMF that
> > allows daily download from a csv file posted on the web? What i would like
> > to do is to build a spreadsheet that will update with daily prices from
> > multiple csv files that are being posted at CBOE website on a daily basis.
> > Is this possibile to achieve with some of the SMF functions or is this a
> > task that requires more serious programming?
> >
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (9)

1d.

Re: Info on csv download

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

Wed May 16, 2012 4:50 am (PDT)

This appears to work just fine:

=smfGetCSVFile("
http://cfe.cboe.com/Publish/ScheduledTask/MktData/datahouse/CFE_F13_VX.csv")

On Wed, May 16, 2012 at 4:43 AM, bagzigingahouse
<bagzigingahouse@gmail.com>wrote:

> The files are contract months located here:
>
> http://cfe.cboe.com/Products/historicalVIX.aspx
>
> Each contract has its own file. Im going to review how the smfGetCSVFile()
> function works in the meanwhile. Any further help is appreciated.
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (9)

1e.

Re: Info on csv download

Posted by: "bagzigingahouse" bagzigingahouse@gmail.com   bagzigingahouse

Wed May 16, 2012 5:07 am (PDT)

Randy, you are my God! It is working!

Now if u could maybe give me a tip on how can i change the sort. Data on these files are starting from smaller to greater, while Yahoo data are in reverse order. Is there a way to revert the sort here?

And another tip would be how to change the Trade date format to be compatible with Yahoo download data? Csv Trade date data is in some format that i cant change no matter what i try. Any tips for this?

Thanks a bunch
Igor

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> This appears to work just fine:
>
> =smfGetCSVFile("
> http://cfe.cboe.com/Publish/ScheduledTask/MktData/datahouse/CFE_F13_VX.csv")
>
> On Wed, May 16, 2012 at 4:43 AM, bagzigingahouse
> <bagzigingahouse@...>wrote:
>
> > The files are contract months located here:
> >
> > http://cfe.cboe.com/Products/historicalVIX.aspx
> >
> > Each contract has its own file. Im going to review how the smfGetCSVFile()
> > function works in the meanwhile. Any further help is appreciated.
> >
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (9)

1f.

Re: Info on csv download

Posted by: "bagzigingahouse" bagzigingahouse@gmail.com   bagzigingahouse

Wed May 16, 2012 9:12 am (PDT)

Randy,

i tried to open the file on the XP now, and the getCSV does not seem to work. My assumption is that the problem is the pop up message i get when trying to open hyperlink from excell by clicking on the link. I tried to disable the msg via Microsoft help but it did not succede, it is still popping. Any idea on how i can bypass this?

Btw, i solved the sorting issue i think, and on XP the dates looks fine. Now if i only could get the pop up msg out of the road..

Thanks

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> This appears to work just fine:
>
> =smfGetCSVFile("
> http://cfe.cboe.com/Publish/ScheduledTask/MktData/datahouse/CFE_F13_VX.csv")
>
> On Wed, May 16, 2012 at 4:43 AM, bagzigingahouse
> <bagzigingahouse@...>wrote:
>
> > The files are contract months located here:
> >
> > http://cfe.cboe.com/Products/historicalVIX.aspx
> >
> > Each contract has its own file. Im going to review how the smfGetCSVFile()
> > function works in the meanwhile. Any further help is appreciated.
> >
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (9)

1g.

Re: Info on csv download

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

Wed May 16, 2012 10:22 am (PDT)

The popup message is from your browser. It should be bypassed when EXCEL
asks for the file directly. Maybe you need to visit the site with IE or the
EXCEL Web Query dialog to create some type of cookie?

On Wed, May 16, 2012 at 9:11 AM, bagzigingahouse
<bagzigingahouse@gmail.com>wrote:

>
> i tried to open the file on the XP now, and the getCSV does not seem to
> work. My assumption is that the problem is the pop up message i get when
> trying to open hyperlink from excell by clicking on the link. I tried to
> disable the msg via Microsoft help but it did not succede, it is still
> popping. Any idea on how i can bypass this?
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (9)

1h.

Re: Info on csv download

Posted by: "bagzigingahouse" bagzigingahouse@gmail.com   bagzigingahouse

Wed May 16, 2012 10:43 am (PDT)

Yea, but the problem is that i dont get the popup when using smf, only when clicking on the link itself.
And not sure how to use the web query option for getting the csv...

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> The popup message is from your browser. It should be bypassed when EXCEL
> asks for the file directly. Maybe you need to visit the site with IE or the
> EXCEL Web Query dialog to create some type of cookie?
>
> On Wed, May 16, 2012 at 9:11 AM, bagzigingahouse
> <bagzigingahouse@...>wrote:
>
> >
> > i tried to open the file on the XP now, and the getCSV does not seem to
> > work. My assumption is that the problem is the pop up message i get when
> > trying to open hyperlink from excell by clicking on the link. I tried to
> > disable the msg via Microsoft help but it did not succede, it is still
> > popping. Any idea on how i can bypass this?
> >
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (9)

1i.

Re: Info on csv download

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

Wed May 16, 2012 11:03 am (PDT)

You don't need to use the web query option to get the csv -- it just
creates a cookie for the web site. You can just cancel out of the web query
after visiting the web site.

On Wed, May 16, 2012 at 10:43 AM, bagzigingahouse <bagzigingahouse@gmail.com
> wrote:

> Yea, but the problem is that i dont get the popup when using smf, only
> when clicking on the link itself.
> And not sure how to use the web query option for getting the csv...
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (9)

2a.

Hi, Japanese Tickers earning date

Posted by: "Chirag Vyas" vyas.chirag@yahoo.in   vyas.chirag

Wed May 16, 2012 4:07 am (PDT)

Is there anyway I can retrieve Japanese tickers earnings date into spreadsheet from earnings.com or any other site??

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

2b.

Re: Hi, Japanese Tickers earning date

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

Wed May 16, 2012 4:36 am (PDT)

I would need some examples. I have no idea of what a Japanese ticker symbol
would be.

On Wed, May 16, 2012 at 4:07 AM, Chirag Vyas <vyas.chirag@yahoo.in> wrote:

> Is there anyway I can retrieve Japanese tickers earnings date into
> spreadsheet from earnings.com or any other site??
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

2c.

Re: Hi, Japanese Tickers earning date

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

Wed May 16, 2012 4:47 am (PDT)

I see nothing under ticker symbol 2651 on Yahoo or earnings.com.

On Wed, May 16, 2012 at 4:41 AM, Chirag Vyas <vyas.chirag@yahoo.in
<mailto:vyas.chirag@yahoo.in> > wrote:

Japanese ticker symbol are numeric like 2651, 2670, 3086, 3099, 3116.

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

2d.

Re: Hi, Japanese Tickers earning date

Posted by: "Chirag Vyas" vyas.chirag@yahoo.in   vyas.chirag

Wed May 16, 2012 5:20 am (PDT)

Hi, sorry forgot to mention that we should add .T after every ticker in earnings.com. Yahoo do not keep Japanese tickers info IMO. 2651.T does the trick.
 
Regards,
Chirag Vyas,
(+91)9833630022

________________________________
From: Randy H <rharmelink@gmail.com>
To: smf_addin@yahoogroups.com
Sent: Wednesday, 16 May 2012 5:17 PM
Subject: [smf_addin] Re: Hi, Japanese Tickers earning date


 
I see nothing under ticker symbol 2651 on Yahoo or earnings.com.

On Wed, May 16, 2012 at 4:41 AM, Chirag Vyas <vyas.chirag@yahoo.in> wrote:

>
>Japanese ticker symbol are numeric like 2651, 2670, 3086, 3099,  3116.
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

2e.

Re: Hi, Japanese Tickers earning date

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

Wed May 16, 2012 11:10 am (PDT)

If all you want is the latest date in the list from earnings.com:

=RCHGetTableCell("
http://www.earnings.com/company.asp?client=cb&ticker=2651.t",7, ">Earnings
Releases","Date/Time",,,1)

On Wed, May 16, 2012 at 5:20 AM, Chirag Vyas <vyas.chirag@yahoo.in> wrote:

>
> Hi, sorry forgot to mention that we should add .T after every ticker in
> earnings.com. Yahoo do not keep Japanese tickers info IMO. 2651.T does
> the trick.
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

3a.

Re: How to get 1 or 5 minute data

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

Wed May 16, 2012 4:25 am (PDT)

I know this topic is old but if someone is looking here...
is there a way to get more than 4 days 1 or 5 minutes data (like it is currently doing via the RCHGetWebData function...i was told that we can retrieve up to a max of 15 days via yahoo...Is it possible, if yes can someone guide me how to do it?

--- In smf_addin@yahoogroups.com, "cocoaacc" <cocoaacc@...> wrote:
>
> Dear Randy Harmelink,
>
> I find the way to graph the data now.
>
> many many thanks!
> I really enjoy using SMF-Addin!
> Thank you!
> Will
>
> --- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@> wrote:
> >
> > Why can't you graph the data?
> >
> > On Tue, Sep 27, 2011 at 11:17 PM, cocoaacc <cocoaacc@> wrote:
> >
> > >
> > > take a look the file from the following folder:
> > >
> > > Files > Templates and Examples > RCHGetWebData
> > >
> > > there is a file called "RCHGetWebData-Template-BarChart-Quotes-Extract"
> > >
> > > This file can get 5min, 30min data for US equity.
> > >
> > > Actually I am looking for 30min data as well......but I can't graph the
> > > data. Don't know why?
> > >
> > > Just wondering if you can find out the way to graph this data.
> > >
> >
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

4a.

Historical Market Cap

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

Wed May 16, 2012 10:29 am (PDT)

Anyone know if there is a way to grab historical market cap or simply shares outstanding for equities?

If shares outstanding can be grabbed (by quarter) then I can just multiple by share price.

BEB

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

4b.

Re: Historical Market Cap

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

Wed May 16, 2012 10:56 am (PDT)

Actually, I got it using the advfin element, but what is FQ20 date?
--- In smf_addin@yahoogroups.com, "prabhakar_karri" <prabhakar_karri@...> wrote:
>
> Hi,
> Is there a way to get historical market cap for a given ticker? For instance, market cap of MMM on Jan 1 2012; Jan 1, 2011; etc.
>
> Appreciate any pointers on this.
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

4c.

Re: Historical Market Cap

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

Wed May 16, 2012 11:05 am (PDT)

There are separate elements for the dates...check the element listing file
that came with the add-in ZIP archive.

If you want more history, you should use the smfGetAdvFNElement() function.

On Wed, May 16, 2012 at 10:56 AM, Brent <brent@carolopolis.com> wrote:

>
> Actually, I got it using the advfin element, but what is FQ20 date?
> --- In smf_addin@yahoogroups.com, "prabhakar_karri" <prabhakar_karri@...>
> wrote:
> >
> > Hi,
> > Is there a way to get historical market cap for a given ticker? For
> instance, market cap of MMM on Jan 1 2012; Jan 1, 2011; etc.
> >
> > Appreciate any pointers on this.
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

5a.

Macintosh users?

Posted by: "JP" front.paige@yahoo.com   front.paige

Wed May 16, 2012 1:22 pm (PDT)

I am using Excel 2011 for Mac. I would like to pull data from any website to update a stock portfolio. But, The Mac version os Excel will do not do Web Queries like older versions.

I have been told that I need another software package that allows Excel for mac to preform Web Queries to pull data. Does anyone have any experience with this?

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)

5b.

Re: Macintosh users?

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

Wed May 16, 2012 1:59 pm (PDT)

Can you open a file from the Internet? For example:

http://download.finance.yahoo.com/d/quotes.csv?s=MMM,IBM,SPY,IWM&f=sl1d1t1c1ohgv&e=.csv

On Wed, May 16, 2012 at 1:22 PM, JP <front.paige@yahoo.com> wrote:

> I am using Excel 2011 for Mac. I would like to pull data from any website
> to update a stock portfolio. But, The Mac version os Excel will do not do
> Web Queries like older versions.
>
> I have been told that I need another software package that allows Excel
> for mac to preform Web Queries to pull data. Does anyone have any
> experience with this?
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)

6a.

Re: Correlation Data

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

Wed May 16, 2012 7:15 pm (PDT)

For correl over 20 closing prices of 2 equities (column A and B), is it formatted as =CORREL(A1:A20,B1:B20)? That doesn't seem to generate accurate data when cross-referenced to other sources

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> I think all you would do is:
>
> =CORREL(adjusted_closing_prices_of_equity_1,adjusted_closing_prices_of_equity_2)
>
> On Tue, May 15, 2012 at 8:56 PM, oyz79 <oyz79@...> wrote:
>
> > I am trying to create a historical correlation matrix for multiple ETFs I
> > have in an smf compatible spreadsheet.
> >
> > I have the daily closing price data importing into excel from yahoo.
> > However, I am struggling with how to best calculate and create a
> > correlation matrix (for example the 20 day historical correlation) for a
> > list of 10 ETFs.
> >
> > Has anyone done this in excel? I know there is a correlation function in
> > excel but I am struggling with how to write the actual formula. Any
> > suggestions?
> >
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (7)

6b.

Re: Correlation Data

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

Wed May 16, 2012 7:20 pm (PDT)

Example? It came close for me when I compared to a source last night, for
126 days.

http://etfscreen.com/corr.php

On Wed, May 16, 2012 at 7:15 PM, oyz79 <oyz79@yahoo.com> wrote:

> For correl over 20 closing prices of 2 equities (column A and B), is it
> formatted as =CORREL(A1:A20,B1:B20)? That doesn't seem to generate accurate
> data when cross-referenced to other sources
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (7)

6c.

Re: Correlation Data

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

Wed May 16, 2012 7:39 pm (PDT)

I get -.88 for AAXJ and TLT, 20 day correlation.

My adjusted close data for TLT is

122.01
121.66
121.17
119.50
118.52
118.87
118.79
118.16
118.14
117.22
117.25
116.43
117.04
117.01
116.91
116.21
116.81
117.63
116.79
116.77

My adjusted close data for AAXJ is:
52.40
52.61
53.53
54.19
54.14
54.83
55.55
55.54
56.40
56.72
56.54
56.20
56.21
56.24
55.92
55.39
55.18
56.05
55.91
56.10

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Example? It came close for me when I compared to a source last night, for
> 126 days.
>
> http://etfscreen.com/corr.php
>
> On Wed, May 16, 2012 at 7:15 PM, oyz79 <oyz79@...> wrote:
>
> > For correl over 20 closing prices of 2 equities (column A and B), is it
> > formatted as =CORREL(A1:A20,B1:B20)? That doesn't seem to generate accurate
> > data when cross-referenced to other sources
> >
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (7)

6d.

Re: Correlation Data

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

Wed May 16, 2012 7:46 pm (PDT)

And what source are you comparing to?

On Wed, May 16, 2012 at 7:39 PM, oyz79 <oyz79@yahoo.com> wrote:

> I get -.88 for AAXJ and TLT, 20 day correlation.
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (7)

6e.

Re: Correlation Data

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

Wed May 16, 2012 7:57 pm (PDT)

ETF Replay shows -.64 as does Assetcorrelation.com. ETF Replay uses adjusted data, not sure about assetcorrelation.com

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> And what source are you comparing to?
>
> On Wed, May 16, 2012 at 7:39 PM, oyz79 <oyz79@...> wrote:
>
> > I get -.88 for AAXJ and TLT, 20 day correlation.
> >
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (7)

Recent Activity

•  81
  New Members
•  1
  New Files

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

Messages | Files | Photos | Polls

Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Individual | Switch format to Traditional
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe