14 New Messages
Digest #2464
Messages
Fri Jan 11, 2013 5:57 am (PST) . Posted by:
"sjagers" sjagers
Hi Randy. Your functions are great. Thanks for making them available.
Question for you: Do you know which stock quote retrieval function (RCHGetYahooHistory, smfLastPrice, smfPricesByDates, or smfPricesBetween) would be the fastest for retrieving the closing prices of multiple stock symbols (let's say 150) for several dates. The dates would be the same for all stock symbols, but the dates may not be consecutive.
For example:
KR... 12/21/12, 12/20/12, 12/19/12, 12/14/12, 11/30/12, and 11/2/12.
AA... " " " " " "
etc. 150 symbols with same dates above.
The date method is: Date"X", DateX - 1 day, DateX - 2 days, DateX - 7 days, DateX - 30 days, DateX - 60 days.
Does each symbol require its own internet query (or lookup), or is there a way to retrieve all symbols in one lookup (one array)?
Question for you: Do you know which stock quote retrieval function (RCHGetYahooHistory
For example:
KR... 12/21/12, 12/20/12, 12/19/12, 12/14/12, 11/30/12, and 11/2/12.
AA... " " " " " "
etc. 150 symbols with same dates above.
The date method is: Date"X"
Does each symbol require its own internet query (or lookup), or is there a way to retrieve all symbols in one lookup (one array)?
Fri Jan 11, 2013 7:12 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Since all of them do one Internet access per function invocation,
smfPricesByDates() would be the fastest, as long as you can do multiple
dates in one function invocation. Otherwise, RCHGetYahooHistory() would be
about as fast, as long as you retrieved all of the history first, and then
did a lookup on the downloaded data to pull out the data for each date (but
that can be a hassle to do for multiple stocks).
You cannot do multiple ticker symbols in a single function -- there would
be no reason for me to write the function that way, since it would still
require a separate Internet access for each ticker symbol.
Note that if you use a volatile function like NOW() as your "DateX", it
will cause all of the functions to become volatile as well -- meaning they
will recalculate with any change to the workbook.
If this is something you would use regularly, I would set up a trigger cell
for calculation. Otherwise, it will calculate them all when you open the
workbook, and then again when you change the "DateX". For example, instead
of:
=smfPricesByDates(...)
...you could do:
=IF($A$1<>"Y","--",smfPricesByDates(...))
That way, the smfPricesByDates() will only execute if cell A1 contains a
string of "Y". So, set it to "N" when you save the workbook. Then, when you
open it, it will open quickly, filled with "--" strings.
On Fri, Jan 11, 2013 at 6:57 AM, sjagers sjagers@yahoo.com > wrote:
> Hi Randy. Your functions are great. Thanks for making them available.
>
> Question for you: Do you know which stock quote retrieval function
> (RCHGetYahooHistory, smfLastPrice, smfPricesByDates, or smfPricesBetween)
> would be the fastest for retrieving the closing prices of multiple stock
> symbols (let's say 150) for several dates. The dates would be the same for
> all stock symbols, but the dates may not be consecutive.
>
> For example:
> KR... 12/21/12, 12/20/12, 12/19/12, 12/14/12, 11/30/12, and 11/2/12.
> AA... " " " " " "
> etc. 150 symbols with same dates above.
>
> The date method is: Date"X", DateX - 1 day, DateX - 2 days, DateX - 7
> days, DateX - 30 days, DateX - 60 days.
>
> Does each symbol require its own internet query (or lookup), or is there a
> way to retrieve all symbols in one lookup (one array)?
>
smfPricesByDates(
dates in one function invocation. Otherwise, RCHGetYahooHistory(
about as fast, as long as you retrieved all of the history first, and then
did a lookup on the downloaded data to pull out the data for each date (but
that can be a hassle to do for multiple stocks).
You cannot do multiple ticker symbols in a single function -- there would
be no reason for me to write the function that way, since it would still
require a separate Internet access for each ticker symbol.
Note that if you use a volatile function like NOW() as your "DateX"
will cause all of the functions to become volatile as well -- meaning they
will recalculate with any change to the workbook.
If this is something you would use regularly, I would set up a trigger cell
for calculation. Otherwise, it will calculate them all when you open the
workbook, and then again when you change the "DateX"
of:
=smfPricesByDates(
...you could do:
=IF($A$1<
That way, the smfPricesByDates(
string of "Y"
open it, it will open quickly, filled with "--" strings.
On Fri, Jan 11, 2013 at 6:57 AM, sjagers sjagers@yahoo.
> Hi Randy. Your functions are great. Thanks for making them available.
>
> Question for you: Do you know which stock quote retrieval function
> (RCHGetYahooHistory
> would be the fastest for retrieving the closing prices of multiple stock
> symbols (let's say 150) for several dates. The dates would be the same for
> all stock symbols, but the dates may not be consecutive.
>
> For example:
> KR... 12/21/12, 12/20/12, 12/19/12, 12/14/12, 11/30/12, and 11/2/12.
> AA... " " " " " "
> etc. 150 symbols with same dates above.
>
> The date method is: Date"X"
> days, DateX - 30 days, DateX - 60 days.
>
> Does each symbol require its own internet query (or lookup), or is there a
> way to retrieve all symbols in one lookup (one array)?
>
Fri Jan 11, 2013 12:01 pm (PST) . Posted by:
"Cole Reichle" reichle_cole
I downloaded the zip file. Extracted the zip file and added the add-in inside my excel 2007. I see the array functions in excel and I've tried using the examples for getting RCHYahooHistorical functions but nothing is returned. Please help. I am using Windows 7 with excel 2007 pro. My spreadsheet just freezes and nothing happens. What am I doing wrong? I see the macro code was updated in Dec 2012, so I think it should work.
Thanks,
Thanks,
Fri Jan 11, 2013 12:04 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
What is the exact formula you are using? What parameters?
What do you get with:
=RCHGetElementNumber("Version")
On Fri, Jan 11, 2013 at 9:49 AM, Cole Reichle reichle_cole@yahoo.com >wrote:
>
>
> I downloaded the zip file. Extracted the zip file and added the add-in
> inside my excel 2007. I see the array functions in excel and I've tried
> using the examples for getting RCHYahooHistorical functions but nothing is
> returned. Please help. I am using Windows 7 with excel 2007 pro. My
> spreadsheet just freezes and nothing happens. What am I doing wrong? I see
> the macro code was updated in Dec 2012, so I think it should work.
>
What do you get with:
=RCHGetElementNumbe
On Fri, Jan 11, 2013 at 9:49 AM, Cole Reichle reichle_cole@
>
>
> I downloaded the zip file. Extracted the zip file and added the add-in
> inside my excel 2007. I see the array functions in excel and I've tried
> using the examples for getting RCHYahooHistorical functions but nothing is
> returned. Please help. I am using Windows 7 with excel 2007 pro. My
> spreadsheet just freezes and nothing happens. What am I doing wrong? I see
> the macro code was updated in Dec 2012, so I think it should work.
>
Fri Jan 11, 2013 12:54 pm (PST) . Posted by:
"calhandon" calhandon
Howdy Randy and Everyone,
In Excel 2010, I was wondering if there is a good way to create a worksheet that includes plans for future row and/or column insertions/deletions -- into/from the middle of an array-entered-formula range of cells?
The only way I've figured out so far, is to leave an empty row and column at the end of the row or column range. This may then be copied and pasted, but then the array-entered formulas all need to be manually changed to reflect the additional row/column.
My current experimental worksheet contains 7 groups of columns and 2 groups of rows, that all contain array-entered formulas. The number of both groups will increase quite a bit by the time I'm done.
The column groupings are because I want non-downloaded data included in these groups. The row groups are for individual portfolio accounts and investment types.
As an example, the first few column groups contain:
* Local
- Manually-entered Symbol,
* smfGetTagContent to get the full Name,
* Local
- Manually-entered Shares,
- Manually-entered Cost,
- Locally calculated Total Basis,
* RCHGetYahooQuotes formula to obtain:
- Prev Close,
- Open,
- Last Trade,
- $Chg,
- %Chg,
- Vol,
- Avg Vol,
- 52-Wk Low,
- 52-Wk High
* Local:
- 52-Wk Range,
- % From High,
- % From Low,
- 10-Day Trend (Sparkline chart)
* then more RCHGetYahooQuotes
The first two row groups are two IRA accounts, sub-grouped via Excel.
Thus, if I end up with something like 15 column groups, and 20 row groups, I would have to manually change up to 300 array-entered formula groups (big frown). Thus my question above. And splitting all of this into individual sheets and books doesn't get around the problem. It just moves it to a different level.
I am able to write VBA code, but am still learning it, even though it looks like learning VBA for Excel will never stop (lol).
Am also experimenting with writing a Windows Desktop app in Visual Studio 2012 Pro, using C# and either VS Lightswitch (SQL Server), or SQLite via their Managed code fork. Excel and VBA is much simpler, but it's limitations on insert/delete within array-entered formula ranges may be a deal-breaker.
Thank you all, in advance, for any and all suggestions.
-Don
In Excel 2010, I was wondering if there is a good way to create a worksheet that includes plans for future row and/or column insertions/deletion
The only way I've figured out so far, is to leave an empty row and column at the end of the row or column range. This may then be copied and pasted, but then the array-entered formulas all need to be manually changed to reflect the additional row/column.
My current experimental worksheet contains 7 groups of columns and 2 groups of rows, that all contain array-entered formulas. The number of both groups will increase quite a bit by the time I'm done.
The column groupings are because I want non-downloaded data included in these groups. The row groups are for individual portfolio accounts and investment types.
As an example, the first few column groups contain:
* Local
- Manually-entered Symbol,
* smfGetTagContent to get the full Name,
* Local
- Manually-entered Shares,
- Manually-entered Cost,
- Locally calculated Total Basis,
* RCHGetYahooQuotes formula to obtain:
- Prev Close,
- Open,
- Last Trade,
- $Chg,
- %Chg,
- Vol,
- Avg Vol,
- 52-Wk Low,
- 52-Wk High
* Local:
- 52-Wk Range,
- % From High,
- % From Low,
- 10-Day Trend (Sparkline chart)
* then more RCHGetYahooQuotes
The first two row groups are two IRA accounts, sub-grouped via Excel.
Thus, if I end up with something like 15 column groups, and 20 row groups, I would have to manually change up to 300 array-entered formula groups (big frown). Thus my question above. And splitting all of this into individual sheets and books doesn't get around the problem. It just moves it to a different level.
I am able to write VBA code, but am still learning it, even though it looks like learning VBA for Excel will never stop (lol).
Am also experimenting with writing a Windows Desktop app in Visual Studio 2012 Pro, using C# and either VS Lightswitch (SQL Server), or SQLite via their Managed code fork. Excel and VBA is much simpler, but it's limitations on insert/delete within array-entered formula ranges may be a deal-breaker.
Thank you all, in advance, for any and all suggestions.
-Don
Fri Jan 11, 2013 12:58 pm (PST) . Posted by:
"calhandon" calhandon
Fri Jan 11, 2013 1:21 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
What I do is remove the array-entered data from the sort area, or
modifiable area.
That is, columns A thru K may contain my sortable and modifiable database,
but columns M thru Z may contain my RCHGetYahooQuotes() "retrieval"
columns. Then, if I want column B to be a particular column from the M thru
Z area, I would just put a formula of =N12 in cell B12. Using range names,
you can even make this self-documenting, so that instead of =N12, you have
=LastTradedPrice.
This allows the data in columns A thru K to be sorted. When the column with
the ticker symbols changes the rows that the ticker symbols are in, the
RCHGetYahooQuotes() function recalculates for the new sort order. Since it
is array-entered, the update is quick.
Then, instead of deleting rows, you just delete ranges. Just have the
RCHGetYahooQuotes() in columns M thru Z cover 200 rows of input data, and
then you don't need to re-enter it when you add additional ticker symbols
(unless you go over 200 of them). For example:
=RCHGetYahooQuotes(OFFSET($B$2,1,0,200,1),"snl1d1")
...where cell $B$2 is the header cell of the column that contains the
ticker cells. If you put that formula into another worksheet, you could
even delete rows on your "working" worksheet, because row deletions don't
affect how that OFFSET() function works.
On Fri, Jan 11, 2013 at 1:54 PM, calhandon bozo@donandcarla.com > wrote:
> Howdy Randy and Everyone,
>
> In Excel 2010, I was wondering if there is a good way to create a
> worksheet that includes plans for future row and/or column
> insertions/deletions -- into/from the middle of an array-entered-formula
> range of cells?
>
> The only way I've figured out so far, is to leave an empty row and column
> at the end of the row or column range. This may then be copied and pasted,
> but then the array-entered formulas all need to be manually changed to
> reflect the additional row/column.
>
> My current experimental worksheet contains 7 groups of columns and 2
> groups of rows, that all contain array-entered formulas. The number of
> both groups will increase quite a bit by the time I'm done.
>
> The column groupings are because I want non-downloaded data included in
> these groups. The row groups are for individual portfolio accounts and
> investment types.
>
> As an example, the first few column groups contain:
> * Local
> - Manually-entered Symbol,
>
> * smfGetTagContent to get the full Name,
>
> * Local
> - Manually-entered Shares,
> - Manually-entered Cost,
> - Locally calculated Total Basis,
>
> * RCHGetYahooQuotes formula to obtain:
> - Prev Close,
> - Open,
> - Last Trade,
> - $Chg,
> - %Chg,
> - Vol,
> - Avg Vol,
> - 52-Wk Low,
> - 52-Wk High
>
> * Local:
> - 52-Wk Range,
> - % From High,
> - % From Low,
> - 10-Day Trend (Sparkline chart)
>
> * then more RCHGetYahooQuotes
>
> The first two row groups are two IRA accounts, sub-grouped via Excel.
>
> Thus, if I end up with something like 15 column groups, and 20 row groups,
> I would have to manually change up to 300 array-entered formula groups (big
> frown). Thus my question above. And splitting all of this into individual
> sheets and books doesn't get around the problem. It just moves it to a
> different level.
>
> I am able to write VBA code, but am still learning it, even though it
> looks like learning VBA for Excel will never stop (lol).
>
> Am also experimenting with writing a Windows Desktop app in Visual Studio
> 2012 Pro, using C# and either VS Lightswitch (SQL Server), or SQLite via
> their Managed code fork. Excel and VBA is much simpler, but it's
> limitations on insert/delete within array-entered formula ranges may be a
> deal-breaker.
>
> Thank you all, in advance, for any and all suggestions.
>
modifiable area.
That is, columns A thru K may contain my sortable and modifiable database,
but columns M thru Z may contain my RCHGetYahooQuotes(
columns. Then, if I want column B to be a particular column from the M thru
Z area, I would just put a formula of =N12 in cell B12. Using range names,
you can even make this self-documenting, so that instead of =N12, you have
=LastTradedPrice.
This allows the data in columns A thru K to be sorted. When the column with
the ticker symbols changes the rows that the ticker symbols are in, the
RCHGetYahooQuotes(
is array-entered, the update is quick.
Then, instead of deleting rows, you just delete ranges. Just have the
RCHGetYahooQuotes(
then you don't need to re-enter it when you add additional ticker symbols
(unless you go over 200 of them). For example:
=RCHGetYahooQuotes(
...where cell $B$2 is the header cell of the column that contains the
ticker cells. If you put that formula into another worksheet, you could
even delete rows on your "working" worksheet, because row deletions don't
affect how that OFFSET() function works.
On Fri, Jan 11, 2013 at 1:54 PM, calhandon bozo@donandcarla.
> Howdy Randy and Everyone,
>
> In Excel 2010, I was wondering if there is a good way to create a
> worksheet that includes plans for future row and/or column
> insertions/deletion
> range of cells?
>
> The only way I've figured out so far, is to leave an empty row and column
> at the end of the row or column range. This may then be copied and pasted,
> but then the array-entered formulas all need to be manually changed to
> reflect the additional row/column.
>
> My current experimental worksheet contains 7 groups of columns and 2
> groups of rows, that all contain array-entered formulas. The number of
> both groups will increase quite a bit by the time I'm done.
>
> The column groupings are because I want non-downloaded data included in
> these groups. The row groups are for individual portfolio accounts and
> investment types.
>
> As an example, the first few column groups contain:
> * Local
> - Manually-entered Symbol,
>
> * smfGetTagContent to get the full Name,
>
> * Local
> - Manually-entered Shares,
> - Manually-entered Cost,
> - Locally calculated Total Basis,
>
> * RCHGetYahooQuotes formula to obtain:
> - Prev Close,
> - Open,
> - Last Trade,
> - $Chg,
> - %Chg,
> - Vol,
> - Avg Vol,
> - 52-Wk Low,
> - 52-Wk High
>
> * Local:
> - 52-Wk Range,
> - % From High,
> - % From Low,
> - 10-Day Trend (Sparkline chart)
>
> * then more RCHGetYahooQuotes
>
> The first two row groups are two IRA accounts, sub-grouped via Excel.
>
> Thus, if I end up with something like 15 column groups, and 20 row groups,
> I would have to manually change up to 300 array-entered formula groups (big
> frown). Thus my question above. And splitting all of this into individual
> sheets and books doesn't get around the problem. It just moves it to a
> different level.
>
> I am able to write VBA code, but am still learning it, even though it
> looks like learning VBA for Excel will never stop (lol).
>
> Am also experimenting with writing a Windows Desktop app in Visual Studio
> 2012 Pro, using C# and either VS Lightswitch (SQL Server), or SQLite via
> their Managed code fork. Excel and VBA is much simpler, but it's
> limitations on insert/delete within array-entered formula ranges may be a
> deal-breaker.
>
> Thank you all, in advance, for any and all suggestions.
>
Fri Jan 11, 2013 8:04 pm (PST) . Posted by:
"calhandon" calhandon
Thank you very much for the suggestion, Randy. Since MS increased the number of allowable rows and columns in Excel 2010, your suggestion makes perfect sense. I'm already doing something similar by having historical data in another sheet -- all array-entered and retrieved automatically via VBA.
I will start a new experimental sheet doing exactly this.
Thanks again, Randy! Have a great weekend...
-Don
--- In smf_addin@yahoogroups.com , Randy Harmelink wrote:
>
> What I do is remove the array-entered data from the sort area, or
> modifiable area.
>
> That is, columns A thru K may contain my sortable and modifiable database,
> but columns M thru Z may contain my RCHGetYahooQuotes() "retrieval"
> columns. Then, if I want column B to be a particular column from the M thru
> Z area, I would just put a formula of =N12 in cell B12. Using range names,
> you can even make this self-documenting, so that instead of =N12, you have
> =LastTradedPrice.
>
> This allows the data in columns A thru K to be sorted. When the column with
> the ticker symbols changes the rows that the ticker symbols are in, the
> RCHGetYahooQuotes() function recalculates for the new sort order. Since it
> is array-entered, the update is quick.
>
> Then, instead of deleting rows, you just delete ranges. Just have the
> RCHGetYahooQuotes() in columns M thru Z cover 200 rows of input data, and
> then you don't need to re-enter it when you add additional ticker symbols
> (unless you go over 200 of them). For example:
>
> =RCHGetYahooQuotes(OFFSET($B$2,1,0,200,1),"snl1d1")
>
> ...where cell $B$2 is the header cell of the column that contains the
> ticker cells. If you put that formula into another worksheet, you could
> even delete rows on your "working" worksheet, because row deletions don't
> affect how that OFFSET() function works.
I will start a new experimental sheet doing exactly this.
Thanks again, Randy! Have a great weekend...
-Don
--- In smf_addin@yahoogrou
>
> What I do is remove the array-entered data from the sort area, or
> modifiable area.
>
> That is, columns A thru K may contain my sortable and modifiable database,
> but columns M thru Z may contain my RCHGetYahooQuotes(
> columns. Then, if I want column B to be a particular column from the M thru
> Z area, I would just put a formula of =N12 in cell B12. Using range names,
> you can even make this self-documenting, so that instead of =N12, you have
> =LastTradedPrice.
>
> This allows the data in columns A thru K to be sorted. When the column with
> the ticker symbols changes the rows that the ticker symbols are in, the
> RCHGetYahooQuotes(
> is array-entered, the update is quick.
>
> Then, instead of deleting rows, you just delete ranges. Just have the
> RCHGetYahooQuotes(
> then you don't need to re-enter it when you add additional ticker symbols
> (unless you go over 200 of them). For example:
>
> =RCHGetYahooQuotes(
>
> ...where cell $B$2 is the header cell of the column that contains the
> ticker cells. If you put that formula into another worksheet, you could
> even delete rows on your "working" worksheet, because row deletions don't
> affect how that OFFSET() function works.
Fri Jan 11, 2013 1:28 pm (PST) . Posted by:
"JCHyjun" JCHyjun
Randy
The template (http://f1.grp.yahoofs.com/v1/UH3wUPl7Mvy1GE0ntprB4i4xoXgmbdjqPnDR1arsQG_A_-1EhwTyd0qpfhg7D5lMin-6p2_8AWrTaewS-PWAEqjspKUilQ/Uploads%20by%20forum%20members/Price-Performance-Data-Retrieval.xls ) is very useful but I cannot figure out how modify it.
Is it possible to expand the template to 4 companies and reduce performance to 1 year max (I need 1 week, 1 month, 3 months, 6 months and 1 year points only)?
Can you post new reduced template please?
Happy New Year!
JCHyjun
The template (http://f1.grp.
Is it possible to expand the template to 4 companies and reduce performance to 1 year max (I need 1 week, 1 month, 3 months, 6 months and 1 year points only)?
Can you post new reduced template please?
Happy New Year!
JCHyjun
Fri Jan 11, 2013 2:05 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Normally, I don't make changes for workbooks in the "Uploads by members"
folder. However, since I did it before on this one, and now there is a
better way to do it...
What I would do now is to use the smfPricesByDates() function, to just
retrieve the adjusted closing prices for the dates directly, instead of
looking them up. Step-by-step:
- To get the latest trading date of the market (using SPY as a proxy for
the market).
C3: =DATEVALUE(RCHGetYahooQuotes("SPY","d1"))
I wouldn't use the NOW() function because it's a volatile function and
would force everything to recalculate any time you change something.
- Then do B4:C15 any way you want them. Leave those you don't use blank.
For example, for 1-week and 1-month:
C4: =C3-7
C5: =EOMONTH(C$3,-2)+DAY(C$3)
- Then, array-enter over cells E3:E15:
=TRANSPOSE(smfPricesByDates(D2,$C$3:$C$15))
- I would also then "Group" column E so that I can easily hide it from
view.
- Then just copy columns D:E as many times as you need and change the
ticker symbols.
When all that is done, I end up with:
http://finance.groups.yahoo.com/group/smf_addin/files/Uploads%20by%20forum%20members/Price-Performance-Data-Retrieval-2.xls
On Fri, Jan 11, 2013 at 2:28 PM, JCHyjun jchyjun@yahoo.com > wrote:
> Randy
> The template (
> http://f1.grp.yahoofs.com/v1/UH3wUPl7Mvy1GE0ntprB4i4xoXgmbdjqPnDR1arsQG_A_-1EhwTyd0qpfhg7D5lMin-6p2_8AWrTaewS-PWAEqjspKUilQ/Uploads%20by%20forum%20members/Price-Performance-Data-Retrieval.xls )
> is very useful but I cannot figure out how modify it.
>
> Is it possible to expand the template to 4 companies and reduce
> performance to 1 year max (I need 1 week, 1 month, 3 months, 6 months and 1
> year points only)?
>
> Can you post new reduced template please?
>
folder. However, since I did it before on this one, and now there is a
better way to do it...
What I would do now is to use the smfPricesByDates(
retrieve the adjusted closing prices for the dates directly, instead of
looking them up. Step-by-step:
- To get the latest trading date of the market (using SPY as a proxy for
the market).
C3: =DATEVALUE(RCHGetYa
I wouldn't use the NOW() function because it's a volatile function and
would force everything to recalculate any time you change something.
- Then do B4:C15 any way you want them. Leave those you don't use blank.
For example, for 1-week and 1-month:
C4: =C3-7
C5: =EOMONTH(C$3,
- Then, array-enter over cells E3:E15:
=TRANSPOSE(smfPrice
- I would also then "Group" column E so that I can easily hide it from
view.
- Then just copy columns D:E as many times as you need and change the
ticker symbols.
When all that is done, I end up with:
http://finance.
On Fri, Jan 11, 2013 at 2:28 PM, JCHyjun jchyjun@yahoo.
> Randy
> The template (
> http://f1.grp.
> is very useful but I cannot figure out how modify it.
>
> Is it possible to expand the template to 4 companies and reduce
> performance to 1 year max (I need 1 week, 1 month, 3 months, 6 months and 1
> year points only)?
>
> Can you post new reduced template please?
>
Fri Jan 11, 2013 6:04 pm (PST) . Posted by:
"JCHyjun" JCHyjun
Thank you, Randy!
--- In smf_addin@yahoogroups.com , Randy Harmelink wrote:
>
> Normally, I don't make changes for workbooks in the "Uploads by members"
> folder. However, since I did it before on this one, and now there is a
> better way to do it...
>
> What I would do now is to use the smfPricesByDates() function, to just
> retrieve the adjusted closing prices for the dates directly, instead of
> looking them up. Step-by-step:
>
> - To get the latest trading date of the market (using SPY as a proxy for
> the market).
>
> C3: =DATEVALUE(RCHGetYahooQuotes("SPY","d1"))
>
> I wouldn't use the NOW() function because it's a volatile function and
> would force everything to recalculate any time you change something.
>
> - Then do B4:C15 any way you want them. Leave those you don't use blank.
> For example, for 1-week and 1-month:
>
> C4: =C3-7
> C5: =EOMONTH(C$3,-2)+DAY(C$3)
>
> - Then, array-enter over cells E3:E15:
>
> =TRANSPOSE(smfPricesByDates(D2,$C$3:$C$15))
>
> - I would also then "Group" column E so that I can easily hide it from
> view.
>
> - Then just copy columns D:E as many times as you need and change the
> ticker symbols.
>
> When all that is done, I end up with:
>
> http://finance.groups.yahoo.com/group/smf_addin/files/Uploads%20by%20forum%20members/Price-Performance-Data-Retrieval-2.xls
> On Fri, Jan 11, 2013 at 2:28 PM, JCHyjun wrote:
>
> > Randy
> > The template (
> > http://f1.grp.yahoofs.com/v1/UH3wUPl7Mvy1GE0ntprB4i4xoXgmbdjqPnDR1arsQG_A_-1EhwTyd0qpfhg7D5lMin-6p2_8AWrTaewS-PWAEqjspKUilQ/Uploads%20by%20forum%20members/Price-Performance-Data-Retrieval.xls )
> > is very useful but I cannot figure out how modify it.
> >
> > Is it possible to expand the template to 4 companies and reduce
> > performance to 1 year max (I need 1 week, 1 month, 3 months, 6 months and 1
> > year points only)?
> >
> > Can you post new reduced template please?
> >
>
--- In smf_addin@yahoogrou
>
> Normally, I don't make changes for workbooks in the "Uploads by members"
> folder. However, since I did it before on this one, and now there is a
> better way to do it...
>
> What I would do now is to use the smfPricesByDates(
> retrieve the adjusted closing prices for the dates directly, instead of
> looking them up. Step-by-step:
>
> - To get the latest trading date of the market (using SPY as a proxy for
> the market).
>
> C3: =DATEVALUE(RCHGetYa
>
> I wouldn't use the NOW() function because it's a volatile function and
> would force everything to recalculate any time you change something.
>
> - Then do B4:C15 any way you want them. Leave those you don't use blank.
> For example, for 1-week and 1-month:
>
> C4: =C3-7
> C5: =EOMONTH(C$3,
>
> - Then, array-enter over cells E3:E15:
>
> =TRANSPOSE(smfPrice
>
> - I would also then "Group" column E so that I can easily hide it from
> view.
>
> - Then just copy columns D:E as many times as you need and change the
> ticker symbols.
>
> When all that is done, I end up with:
>
> http://finance.
> On Fri, Jan 11, 2013 at 2:28 PM, JCHyjun wrote:
>
> > Randy
> > The template (
> > http://f1.grp.
> > is very useful but I cannot figure out how modify it.
> >
> > Is it possible to expand the template to 4 companies and reduce
> > performance to 1 year max (I need 1 week, 1 month, 3 months, 6 months and 1
> > year points only)?
> >
> > Can you post new reduced template please?
> >
>
Fri Jan 11, 2013 6:33 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
I uploaded a new version:
Price-Performance-Data-Retrieval-v3.xls
This one gets rid of the intermediate data column -- I was just curious if
using the formula this way would work.
On Fri, Jan 11, 2013 at 7:04 PM, JCHyjun jchyjun@yahoo.com > wrote:
> Thank you, Randy!
>
Price-Performance-
This one gets rid of the intermediate data column -- I was just curious if
using the formula this way would work.
On Fri, Jan 11, 2013 at 7:04 PM, JCHyjun jchyjun@yahoo.
> Thank you, Randy!
>
Fri Jan 11, 2013 7:24 pm (PST) . Posted by:
"Jacob Jose" pepecan47
The retrieval of the M* star rating stopped working. This is the formula I've been using successfully for several months:
=VALUE(RCHGetWebData("http://quote.morningstar.com/stock/s.aspx?t= "&A3,"star.gif",1,-1))&"*"
Any idea what is going on?
Thx a lot.
Regards,
Jose L. Jacob
=VALUE(RCHGetWebDat
Any idea what is going on?
Thx a lot.
Regards,
Jose L. Jacob
Fri Jan 11, 2013 7:31 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
They're no longer using an image on the web page for the star rating.
Instead, try:
=0+smfStrExtr(RCHGetWebData("http://quotes.morningstar.com/stock/s?t=
"&C5,"r_star"),"r_star","'")
On Fri, Jan 11, 2013 at 8:24 PM, Jacob Jose pepecan47@yahoo.ca > wrote:
>
> The retrieval of the M* star rating stopped working. This is the formula
> I've been using successfully for several months:
>
> =VALUE(RCHGetWebData("
> http://quote.morningstar.com/stock/s.aspx?t= "&A3,"star.gif",1,-1))&"*"
>
> Any idea what is going on?
>
>
Instead, try:
=0+smfStrExtr(
"&C5,"
On Fri, Jan 11, 2013 at 8:24 PM, Jacob Jose pepecan47@yahoo.
>
> The retrieval of the M* star rating stopped working. This is the formula
> I've been using successfully for several months:
>
> =VALUE(RCHGetWebDat
> http://quote.
>
> Any idea what is going on?
>
>
Tidak ada komentar:
Posting Komentar