3 Messages
Digest #3830
Messages
Sun Oct 30, 2016 5:23 am (PDT) . Posted by:
wongkayau
Hi Randy,
It works! You are the best! Thank you so much.
Wish you have a great week ahead. :)
Cheers,
L
It works! You are the best! Thank you so much.
Wish you have a great week ahead. :)
Cheers,
L
Sun Oct 30, 2016 6:22 pm (PDT) . Posted by:
"Gary Funck" garyfunck
I've a got a spreadsheet that computes the ratio of two stock/index
price series and plots the ratio as well as both normalized price series.
It also calculates some statistics to aid in analyzing pairs trades.
At present each data series is fetched like this:
=RCHGetYahooHistory
YEAR(End_Date)
where
End_Date is typically TODAY() and Start_Date is End_Date - Weeks * 5.
Weeks is an input cell where the user specifies the number of weeks
to be used in the analysis. It is assumed that there are approximately 5
trading days in each week.
It works fine, but sometimes for example I may ask for 5 years of data
when one/both of the price series doesn't have that much data.
Then, there is also the issue that both price series might not have
data for some dates even though generally they have data within
the start and end dates.
A reasonable way to work around those issues is two join both data
series on the date column, thus showing data only for the dates that
they have in common. Is there a way to accomplish this without
custom VBA code? I'm using Excel 2010.
thanks,
- Gary
Sun Oct 30, 2016 7:13 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
First thing I'll mention is that it's usually not a good idea to use
TODAY() -- it makes everything that refers to it volatile. That means it
will recalculate if ANYTHING changes in the workbook. I usually use this to
get a static date for the last trading date:
=DATEVALUE(RCHGetYahooQuotes("SPY","d1"))
That leads me to the solution to the other issue. Here's what I would do:
1. Use RCHGetYahooHistory() to get the two data series you want, but I
would use simpler formulas. Just array-enter them over a range large enough
to have 5 years of data fit in them -- say, 1500 rows by 2 columns.
=RCHGetYahooHistory(Symbol1,,,,,,,"d","DA")
=RCHGetYahooHistory(Symbol2,,,,,,,"d","DA")
2. Use RCHGetYahooHistory() to get the two date series for SPY. It should
be the most complete series.
=RCHGetYahooHistory("SPY",,,,,,,"d","D")
3. Create the 5-year date series from the available SPY dates.
4. Use VLOOKUP() formulas on the 5-year date series against each of your
equity dates to create two columns of price data. If one of them isn't
available for a date, I wouldn't drop the date. Instead, I would used the
adjusted closing price from the previous day as a replacement for that
day's adjusted closing price. In essences, just carrying it forward.
5. Create your ratios from the two price columns.
You could combine steps 2 and 3 by using your Start_Date and End_Date
processing, but use the RCHGetYahooQuotes() trick up above in place of the
TODAY() function. Then you would need to use the "resort" parameter as
well, since you want the data from old to new.
On Sun, Oct 30, 2016 at 6:22 PM, Gary Funck gary@
...wrote:
>
> I've a got a spreadsheet that computes the ratio of two stock/index
> price series and plots the ratio as well as both normalized price series.
> It also calculates some statistics to aid in analyzing pairs trades.
>
> At present each data series is fetched like this:
>
> =
>
>
>
> RCHGetYahooHistory(Symbol1,YEAR(Start_Date),MONTH(Start_
> Date),DAY(Start_Date),
> YEAR(End_Date),MONTH(End_Date)
> ,DAY(End_Date),"d","DA",0,1,1,2)
>
> where
>
> End_Date is typically TODAY() and Start_Date is End_Date - Weeks * 5.
> Weeks is an input cell where the user specifies the number of weeks
> to be used in the analysis. It is assumed that there are approximately 5
> trading days in each week.
>
> It works fine, but sometimes for example I may ask for 5 years of data
> when one/both of the price series doesn't have that much data.
> Then, there is also the issue that both price series might not have
> data for some dates even though generally they have data within
> the start and end dates.
>
> A reasonable way to work around those issues is two join both data
> series on the date column, thus showing data only for the dates that
> they have in common. Is there a way to accomplish this without
> custom VBA code? I'm using Excel 2010.
>
TODAY() -- it makes everything that refers to it volatile. That means it
will recalculate if ANYTHING changes in the workbook. I usually use this to
get a static date for the last trading date:
=DATEVALUE(RCHGetYa
That leads me to the solution to the other issue. Here's what I would do:
1. Use RCHGetYahooHistory(
would use simpler formulas. Just array-enter them over a range large enough
to have 5 years of data fit in them -- say, 1500 rows by 2 columns.
=RCHGetYahooHistory
=RCHGetYahooHistory
2. Use RCHGetYahooHistory(
be the most complete series.
=RCHGetYahooHistory
3. Create the 5-year date series from the available SPY dates.
4. Use VLOOKUP() formulas on the 5-year date series against each of your
equity dates to create two columns of price data. If one of them isn't
available for a date, I wouldn't drop the date. Instead, I would used the
adjusted closing price from the previous day as a replacement for that
day's adjusted closing price. In essences, just carrying it forward.
5. Create your ratios from the two price columns.
You could combine steps 2 and 3 by using your Start_Date and End_Date
processing, but use the RCHGetYahooQuotes(
TODAY() function. Then you would need to use the "resort" parameter as
well, since you want the data from old to new.
On Sun, Oct 30, 2016 at 6:22 PM, Gary Funck gary@
...wrote:
>
> I've a got a spreadsheet that computes the ratio of two stock/index
> price series and plots the ratio as well as both normalized price series.
> It also calculates some statistics to aid in analyzing pairs trades.
>
> At present each data series is fetched like this:
>
> =
>
>
>
> RCHGetYahooHistory(
> Date),DAY(Start_
> YEAR(End_Date)
> ,DAY(End_Date)
>
> where
>
> End_Date is typically TODAY() and Start_Date is End_Date - Weeks * 5.
> Weeks is an input cell where the user specifies the number of weeks
> to be used in the analysis. It is assumed that there are approximately 5
> trading days in each week.
>
> It works fine, but sometimes for example I may ask for 5 years of data
> when one/both of the price series doesn't have that much data.
> Then, there is also the issue that both price series might not have
> data for some dates even though generally they have data within
> the start and end dates.
>
> A reasonable way to work around those issues is two join both data
> series on the date column, thus showing data only for the dates that
> they have in common. Is there a way to accomplish this without
> custom VBA code? I'm using Excel 2010.
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar