15 New Messages
Digest #2504
Messages
Sun Feb 10, 2013 7:29 am (PST) . Posted by:
"Randy Harmelink" rharmelink
You'd need to retrieve a range of data and then sum up the premiums for
that year. For example:
B2: 2010
B4:C22: =RCHGetYahooHistory("MMM",,,,,,,"v")
C2: =SUMPRODUCT(--(YEAR(B5:B22)=B2),C5:C22)
On Sun, Feb 10, 2013 at 8:09 AM, Jacob Jose pepecan47@yahoo.ca > wrote:
>
> If I use RCHGetYahooHistory() to get the dividends history, how can I get
> the totals by year?
>
> If I have a cell with the value 2010 (for example), how can I get in
> another cell the sum al all dividends paid in 2010?
>
that year. For example:
B2: 2010
B4:C22: =RCHGetYahooHistory
C2: =SUMPRODUCT(
On Sun, Feb 10, 2013 at 8:09 AM, Jacob Jose pepecan47@yahoo.
>
> If I use RCHGetYahooHistory(
> the totals by year?
>
> If I have a cell with the value 2010 (for example), how can I get in
> another cell the sum al all dividends paid in 2010?
>
Sun Feb 10, 2013 11:13 am (PST) . Posted by:
"igorrivin" igorrivin
Thanks! re one access for symbol and list of dates: my excel use might be a little primitive, but the "obvious" thing to do is to have a table, such as:
A B C D
Date ticker1 ticker2 ticker3 ...
and then cell B93 has =smfPricesbyDates(
As for the simpler question, sigh. Where does the price data come from, then?
Anyway, thanks much!
Igor
--- In smf_addin@yahoogrou
>
> Second question first -- Dow Jones has enforced their licensing on the
> quotes from their indexes, and will no longer allow Yahoo to provide them
> in the CSV format. That's why they are no longer available to the
> RCHGetYahooQuotes(
> files.
>
> ============
>
> Actually, smfPricesByDates(
> symbol and set of dates. It retrieves all of the historical data from
> Yahoo, and then extracts only those requested dates. You're not doing one
> function invocation per date, are you?
>
> What I've sometimes done in such situations is create a "dummy" formula
> row. For example, suppose I want to have ticker symbols starting in cell B5
> and downward in the B column, with dates in cells C4:J4. Then I'll leave
> cell B2 empty, and create this formula is cells C2:J2:
>
> =IF($B2="
>
> Now, whenever I want to get quotes on something new, I insert a new row
> below row 4, put my new symbol in the B column of that row, and copy cells
> C2:J2 down to the C thru J columns of that row. Then I copy and paste
> special as values for those values in the new row.
>
> Then, I can add new things whenever I want. I can update any time I want.
> But all the values stay there between closing and reopening of the workbook.
>
> I used to use this technique quite a bit in some of my backtesting
> routines, so I wouldn't need to recalculate everything every time I opened
> the workbook. As long as you have that "dummy" row of formulas available to
> copy, you don't lose any work you did on them.
>
> On Sat, Feb 9, 2013 at 10:19 PM, Igor Rivin wrote:
>
> >
> > I have just started using SMF, and it is great, but here is a question:
> >
> > I need to have historical data for market indices for a few years, and
> > when I use smfPricesbyDates to get this, it takes forever to initialize
> > (since presumably it is making ten thousand or so http queries). Since this
> > data is not likely to change (this would be false for single stock data,
> > assuming it is adjusted for corporate actions), I wonder if there is a way
> > to only get new data...
> >
> > A more minor question is this: for most indices, the Yahoo symbol works in
> > smfPricesbyDates (e.g., ^IXIC for the nasdaq data), but ^DJI does not work
> > for Dow Jones industrials (DJIA does). I am curious why this is...
> >
> >
>
Sun Feb 10, 2013 12:55 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Do you have multiple dates per ticker?
Otherwise, since the historical data is stored by ticker, there is no
efficient way to get multiple tickers for a single date. It would be one
Internet access per combination.
On Sun, Feb 10, 2013 at 12:13 PM, igorrivin igor.rivin@gmail.com > wrote:
>
> Thanks! re one access for symbol and list of dates: my excel use might be
> a little primitive, but the "obvious" thing to do is to have a table, such
> as:
> A B C D
> Date ticker1 ticker2 ticker3 ...
>
> and then cell B93 has =smfPricesbyDates(ticker1, A893), so this WILL call
> the function as many times as there are dates (times the number of
> tickers). Now, it is pretty clear that one can write a VBA script to
> download the prices into some array and then load the array into the
> appropriate place, but is that the only way to do this, or is there some
> simpler way?
>
> As for the simpler question, sigh. Where does the price data come from,
> then?
>
> Anyway, thanks much!
>
Otherwise, since the historical data is stored by ticker, there is no
efficient way to get multiple tickers for a single date. It would be one
Internet access per combination.
On Sun, Feb 10, 2013 at 12:13 PM, igorrivin igor.rivin@gmail.
>
> Thanks! re one access for symbol and list of dates: my excel use might be
> a little primitive, but the "obvious" thing to do is to have a table, such
> as:
> A B C D
> Date ticker1 ticker2 ticker3 ...
>
> and then cell B93 has =smfPricesbyDates(
> the function as many times as there are dates (times the number of
> tickers). Now, it is pretty clear that one can write a VBA script to
> download the prices into some array and then load the array into the
> appropriate place, but is that the only way to do this, or is there some
> simpler way?
>
> As for the simpler question, sigh. Where does the price data come from,
> then?
>
> Anyway, thanks much!
>
Sun Feb 10, 2013 4:16 pm (PST) . Posted by:
"igorrivin" igorrivin
Yes, I have seven years of data per ticker, so as I said, I can certainly write a VBA function which makes one range call per ticker, stores the results in an array, then stuffs the result into appropriate cells, but this seems not quite as elegant as it could be... I wasn't expecting to get multiple tickers for a single date with one call...
--- In smf_addin@yahoogroups.com , Randy Harmelink wrote:
>
> Do you have multiple dates per ticker?
>
> Otherwise, since the historical data is stored by ticker, there is no
> efficient way to get multiple tickers for a single date. It would be one
> Internet access per combination.
>
> On Sun, Feb 10, 2013 at 12:13 PM, igorrivin wrote:
>
> >
> > Thanks! re one access for symbol and list of dates: my excel use might be
> > a little primitive, but the "obvious" thing to do is to have a table, such
> > as:
> > A B C D
> > Date ticker1 ticker2 ticker3 ...
> >
> > and then cell B93 has =smfPricesbyDates(ticker1, A893), so this WILL call
> > the function as many times as there are dates (times the number of
> > tickers). Now, it is pretty clear that one can write a VBA script to
> > download the prices into some array and then load the array into the
> > appropriate place, but is that the only way to do this, or is there some
> > simpler way?
> >
> > As for the simpler question, sigh. Where does the price data come from,
> > then?
> >
> > Anyway, thanks much!
> >
>
--- In smf_addin@yahoogrou
>
> Do you have multiple dates per ticker?
>
> Otherwise, since the historical data is stored by ticker, there is no
> efficient way to get multiple tickers for a single date. It would be one
> Internet access per combination.
>
> On Sun, Feb 10, 2013 at 12:13 PM, igorrivin wrote:
>
> >
> > Thanks! re one access for symbol and list of dates: my excel use might be
> > a little primitive, but the "obvious" thing to do is to have a table, such
> > as:
> > A B C D
> > Date ticker1 ticker2 ticker3 ...
> >
> > and then cell B93 has =smfPricesbyDates(
> > the function as many times as there are dates (times the number of
> > tickers). Now, it is pretty clear that one can write a VBA script to
> > download the prices into some array and then load the array into the
> > appropriate place, but is that the only way to do this, or is there some
> > simpler way?
> >
> > As for the simpler question, sigh. Where does the price data come from,
> > then?
> >
> > Anyway, thanks much!
> >
>
Sun Feb 10, 2013 5:24 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Huh? Seven years of data per ticker? And you're looking at retrieving it
day-by-day?
On Sun, Feb 10, 2013 at 5:16 PM, igorrivin igor.rivin@gmail.com > wrote:
> Yes, I have seven years of data per ticker, so as I said, I can certainly
> write a VBA function which makes one range call per ticker, stores the
> results in an array, then stuffs the result into appropriate cells, but
> this seems not quite as elegant as it could be... I wasn't expecting to get
> multiple tickers for a single date with one call...
>
day-by-day?
On Sun, Feb 10, 2013 at 5:16 PM, igorrivin igor.rivin@gmail.
> Yes, I have seven years of data per ticker, so as I said, I can certainly
> write a VBA function which makes one range call per ticker, stores the
> results in an array, then stuffs the result into appropriate cells, but
> this seems not quite as elegant as it could be... I wasn't expecting to get
> multiple tickers for a single date with one call...
>
Sun Feb 10, 2013 6:01 pm (PST) . Posted by:
"igorrivin" igorrivin
Well, only for indices, of which I only use five (in case you care, which I doubt, ^GSPC, DJIA, ^IXIC, ^RUT, and XLE, which is not, properly speaking, an index, pays dividends, and so DOES have to be reloaded each time).. Not doing anything intelligent (that is, having =smfPricesbyDates(
--- In smf_addin@yahoogrou
>
> Huh? Seven years of data per ticker? And you're looking at retrieving it
> day-by-day?
>
> On Sun, Feb 10, 2013 at 5:16 PM, igorrivin wrote:
>
> > Yes, I have seven years of data per ticker, so as I said, I can certainly
> > write a VBA function which makes one range call per ticker, stores the
> > results in an array, then stuffs the result into appropriate cells, but
> > this seems not quite as elegant as it could be... I wasn't expecting to get
> > multiple tickers for a single date with one call...
> >
>
Sun Feb 10, 2013 6:05 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
What I don't understand is why you need seven years of individual dates, or
why you can't use multiple dates per ticker symbol...
On Sun, Feb 10, 2013 at 7:01 PM, igorrivin igor.rivin@gmail.com > wrote:
>
> Well, only for indices, of which I only use five (in case you care, which
> I doubt, ^GSPC, DJIA, ^IXIC, ^RUT, and XLE, which is not, properly
> speaking, an index, pays dividends, and so DOES have to be reloaded each
> time).. Not doing anything intelligent (that is, having
> =smfPricesbyDates(ticker, date) in the roughly 9000 cells) takes around
> five minutes, which is annoying, but not deadly (since I usually have other
> things to do), so my question is really mostly one of elegance.
>
why you can't use multiple dates per ticker symbol...
On Sun, Feb 10, 2013 at 7:01 PM, igorrivin igor.rivin@gmail.
>
> Well, only for indices, of which I only use five (in case you care, which
> I doubt, ^GSPC, DJIA, ^IXIC, ^RUT, and XLE, which is not, properly
> speaking, an index, pays dividends, and so DOES have to be reloaded each
> time).. Not doing anything intelligent (that is, having
> =smfPricesbyDates(
> five minutes, which is annoying, but not deadly (since I usually have other
> things to do), so my question is really mostly one of elegance.
>
Sun Feb 10, 2013 5:49 pm (PST) . Posted by:
"Steven" sdavis81
Can someone help me with this formula?
I want to pull a quote for a VIX May 13, 17 strike call (VIX130522C00017000
I used this formula, which was a copy and paste from a few years ago
=AVERAGE(RCHGetTabl
It worked then with a different ticker number, which I changed in this formula.
Any help would be appreciated
Sun Feb 10, 2013 6:03 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Your URL is wrong...just try clicking on it.
On Sun, Feb 10, 2013 at 6:49 PM, Steven sdavis81@yahoo.com > wrote:
>
> Can someone help me with this formula?
> I want to pull a quote for a VIX May 13, 17 strike call
> (VIX130522C00017000)
>
> I used this formula, which was a copy and paste from a few years ago
>
> =AVERAGE(RCHGetTableCell("http://finance.yahoo.com/qs=VIX130522C00017000
> ",1,&quo...))
>
> It worked then with a different ticker number, which I changed in this
> formula.
>
> Any help would be appreciated
>
On Sun, Feb 10, 2013 at 6:49 PM, Steven sdavis81@yahoo.
>
> Can someone help me with this formula?
> I want to pull a quote for a VIX May 13, 17 strike call
> (VIX130522C00017000
>
> I used this formula, which was a copy and paste from a few years ago
>
> =AVERAGE(RCHGetTabl
> ",1,&quo.
>
> It worked then with a different ticker number, which I changed in this
> formula.
>
> Any help would be appreciated
>
Mon Feb 11, 2013 7:50 am (PST) . Posted by:
"Steven" sdavis81
Thanks for looking Randy,
I am still having some trouble making this formula work.
The URL is:
http://finance.yahoo.com/q?s=VIX130522C00017000
So there is a "?" mark in the URL which is corrected to formula:
=AVERAGE(RCHGetTableCell("http://finance.yahoo.com/qs=VIX130522C00017000 ",1,&quo...))
This is still giving me a formula error message. I am not familiar with the syntax at the end of the formula, as I did not write it initially.
Could you perhaps suggest the correct formula for yielding the average of the bid and ask for this option listed at this URL?
Thanks again for looking, perhaps your insight will help me understand how to use this command better. I have read the documentation page for this command and cannot figure out how to do this correctly. Much appreciated.
--- In smf_addin@yahoogroups.com , Randy Harmelink wrote:
>
> Your URL is wrong...just try clicking on it.
>
> On Sun, Feb 10, 2013 at 6:49 PM, Steven wrote:
>
> >
> > Can someone help me with this formula?
> > I want to pull a quote for a VIX May 13, 17 strike call
> > (VIX130522C00017000)
> >
> > I used this formula, which was a copy and paste from a few years ago
> >
> > =AVERAGE(RCHGetTableCell("http://finance.yahoo.com/qs=VIX130522C00017000
> > ",1,&quo...))
> >
> > It worked then with a different ticker number, which I changed in this
> > formula.
> >
> > Any help would be appreciated
> >
>
I am still having some trouble making this formula work.
The URL is:
http://finance.
So there is a "?" mark in the URL which is corrected to formula:
=AVERAGE(RCHGetTabl
This is still giving me a formula error message. I am not familiar with the syntax at the end of the formula, as I did not write it initially.
Could you perhaps suggest the correct formula for yielding the average of the bid and ask for this option listed at this URL?
Thanks again for looking, perhaps your insight will help me understand how to use this command better. I have read the documentation page for this command and cannot figure out how to do this correctly. Much appreciated.
--- In smf_addin@yahoogrou
>
> Your URL is wrong...just try clicking on it.
>
> On Sun, Feb 10, 2013 at 6:49 PM, Steven wrote:
>
> >
> > Can someone help me with this formula?
> > I want to pull a quote for a VIX May 13, 17 strike call
> > (VIX130522C00017000
> >
> > I used this formula, which was a copy and paste from a few years ago
> >
> > =AVERAGE(RCHGetTabl
> > ",1,&quo.
> >
> > It worked then with a different ticker number, which I changed in this
> > formula.
> >
> > Any help would be appreciated
> >
>
Sun Feb 10, 2013 8:09 pm (PST) . Posted by:
"Matthew" m_casselman
I am working in both a PC world and Apple. I like the add-in and it works great on my PC Excel 2010.
Is there a version of this which would work on Excel 2011 on my Apple iMac?
Are there any plans to port it?
Thanks.
Is there a version of this which would work on Excel 2011 on my Apple iMac?
Are there any plans to port it?
Thanks.
Sun Feb 10, 2013 8:11 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
No plans to port it to any other environment.
I doubt it would work on any Mac, unless using some type of Windows
emulation software. As is, the add-in requires Microsoft Windows DLLs.
On Sun, Feb 10, 2013 at 7:57 PM, Matthew m_casselman@yahoo.com > wrote:
> I am working in both a PC world and Apple. I like the add-in and it works
> great on my PC Excel 2010.
>
> Is there a version of this which would work on Excel 2011 on my Apple iMac?
>
> Are there any plans to port it?
>
I doubt it would work on any Mac, unless using some type of Windows
emulation software. As is, the add-in requires Microsoft Windows DLLs.
On Sun, Feb 10, 2013 at 7:57 PM, Matthew m_casselman@
> I am working in both a PC world and Apple. I like the add-in and it works
> great on my PC Excel 2010.
>
> Is there a version of this which would work on Excel 2011 on my Apple iMac?
>
> Are there any plans to port it?
>
Mon Feb 11, 2013 12:25 am (PST) . Posted by:
"Tze Wei," tzewei_79
Hi,
Oh I thought &A1 need to be before the ". It works now. thanks!!
--- In smf_addin@yahoogroups.com , Randy Harmelink wrote:
>
> That also would just be a normal EXCEL operation, of concatenation:
>
> =RCHGetTableCell("
> http://investing.money.msn.com/investments/stock-income-statement/?symbol= "&A1,1,"Total
> Revenue")
>
> On Sun, Feb 10, 2013 at 4:42 AM, Tze Wei, wrote:
>
> >
> > I meant:
> >
> > Cell A1 = IBM
> > Cell B1 = =RCHGetTableCell("
> > http://investing.money.msn.com/investments/stock-income-statement/?symbol=A1 ",1,"Total
> > Revenue")
> >
> > Actually I wanted to do something similar to
> > "SMF-Template-Yahoo-Key-Statistics.xls" but using =RCHGetTableCell as only
> > some data are available only in MSN but not in Yahoo.
> >
> > Is that possible?
> >
>
Oh I thought &A1 need to be before the ". It works now. thanks!!
--- In smf_addin@yahoogrou
>
> That also would just be a normal EXCEL operation, of concatenation:
>
> =RCHGetTableCell(
> http://investing.
> Revenue"
>
> On Sun, Feb 10, 2013 at 4:42 AM, Tze Wei, wrote:
>
> >
> > I meant:
> >
> > Cell A1 = IBM
> > Cell B1 = =RCHGetTableCell(
> > http://investing.
> > Revenue"
> >
> > Actually I wanted to do something similar to
> > "SMF-Template-
> > some data are available only in MSN but not in Yahoo.
> >
> > Is that possible?
> >
>
Mon Feb 11, 2013 12:52 am (PST) . Posted by:
"Tze Wei," tzewei_79
Hi,
I tried using the following formula to get the Net Income from MSN but it seems to get the 1st Net Income at the site, which is "Net Income Before Extra. Items"
Is there a way to make the code skip 2 rows below Net Income Before Extra. Items?
=RCHGetTableCell("http://investing.money.msn.com/investments/stock-income-statement/?symbol=NCT ",1,"Net Income")
I tried using the following formula to get the Net Income from MSN but it seems to get the 1st Net Income at the site, which is "Net Income Before Extra. Items"
Is there a way to make the code skip 2 rows below Net Income Before Extra. Items?
=RCHGetTableCell(
Mon Feb 11, 2013 1:01 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Just use an additional search string (you can use up to 4 of them):
=RCHGetTableCell("
http://investing.money.msn.com/investments/stock-income-statement/?symbol=NCT ",1,"Net
Income","Net Income")
...or you could use the "id" descriptor:
=RCHGetTableCell("
http://investing.money.msn.com/investments/stock-income-statement/?symbol=NCT
",1,"id=""NetIncome""")
Just a word of caution -- it's typically not a good idea to use something
like "Net Income" as a search string, all by itself. That string could
easily show up elsewhere in the source code of the web page, such as in
Meta tag descriptions of the web page, or even a news item that may show up
someplace early on the page.
On Mon, Feb 11, 2013 at 1:52 AM, Tze Wei, tzewei_79@yahoo.com.sg > wrote:
>
> I tried using the following formula to get the Net Income from MSN but it
> seems to get the 1st Net Income at the site, which is "Net Income Before
> Extra. Items"
>
> Is there a way to make the code skip 2 rows below Net Income Before Extra.
> Items?
>
> =RCHGetTableCell("
> http://investing.money.msn.com/investments/stock-income-statement/?symbol=NCT ",1,"Net
> Income")
>
=RCHGetTableCell(
http://investing.
Income"
...or you could use the "id" descriptor:
=RCHGetTableCell(
http://investing.
",1,"
Just a word of caution -- it's typically not a good idea to use something
like "Net Income" as a search string, all by itself. That string could
easily show up elsewhere in the source code of the web page, such as in
Meta tag descriptions of the web page, or even a news item that may show up
someplace early on the page.
On Mon, Feb 11, 2013 at 1:52 AM, Tze Wei, tzewei_79@yahoo.
>
> I tried using the following formula to get the Net Income from MSN but it
> seems to get the 1st Net Income at the site, which is "Net Income Before
> Extra. Items"
>
> Is there a way to make the code skip 2 rows below Net Income Before Extra.
> Items?
>
> =RCHGetTableCell(
> http://investing.
> Income"
>
Tidak ada komentar:
Posting Komentar