Selasa, 01 November 2016

[smf_addin] Digest Number 3831

6 Messages

Digest #3831

Messages

Mon Oct 31, 2016 11:22 am (PDT) . Posted by:

"Luther Kinchen" lutherkinchen

Any help would be appreciated.

I've looked through the documentation and could not find asource to pull in the closing price for indexes.

 
Is it possible to pull in index data, for differentdates?  Such as:

 
I would like to know how to pull in:

1)     Today'sclosing price on the DJI, COMPX, SPX and VIX

2)     Priorweeks (last price available for prior week) closing price

3)     Priormonth (last day of month) closing price.

4)     PriorYear (last day of year) closing price

 
Thank you,

Luther

Mon Oct 31, 2016 1:02 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

First off, let me say that I usually avoid the indexes, because they don't
include dividends. For example, YTD the S&P 500 index is up 4.4%. However,
if you had held SPY, the ETF that mirrors the S&P 500 index, you would also
have collected dividends on the underlying stocks, and be up 5.9%.

I generally use the smfPricesByDates() functions, which uses Yahoo's
historical quotes to fill in the quotes by date (using adjusted closing
prices). However, that doesn't work for any Dow Jones indexes, because Dow
Jones licenses their quotes and won't allow Yahoo to provide them in a CSV
file format.

Having said that, here's what I'd do:

D5: =DATEVALUE(RCHGetYahooQuotes("SPY","d1"))
E5: =D5-WEEKDAY(D5)-1
F5: =EOMONTH(D5,-1)
G5: =EOMONTH(D5,-MONTH(D5))
C6: SPY
D6-G6: =smfPricesByDates(C6,$D$5:$G$5)

Row 6 can be copied downward and then filled with different ticker symbols.

The above formulas give me something like:

*Ticker* *2016-10-31* *2016-10-28* *2016-09-30* *2015-12-31* *YTD*
SPY $212.66 $212.54 $216.30 $200.76 5.9%
^GSPC $2,127.86 $2,126.41 $2,168.27 $2,043.94 4.1%
However, that does mean that the "Prior week" Friday's price COULD be prior
to the "Prior Month". For example, if you ran that tomorrow, the current
day would be 2016-11-01, the Friday previous to that would still be
2016-10-28, but the previous month end would 2016-10-31.

You may want to refer the "Prior Month" to be the month-end prior to the
previous week-end date? And the "Prior Year" to be the year-end prior to
the month-end?

Not sure exactly what you're looking for. Just warning you that the dates
may not always be in descending order as written.

On Mon, Oct 31, 2016 at 11:05 AM, Luther Kinchen lutherkinchen@
​...wrote:

>
> Any help would be appreciated.
> I've looked through the documentation and could not find a source to pull
> in the closing price for indexes.
>
> Is it possible to pull in index data, for different dates? Such as:
>
> I would like to know how to pull in:
> 1) Today's closing price on the DJI, COMPX, SPX and VIX
> 2) Prior weeks (last price available for prior week) closing price
> 3) Prior month (last day of month) closing price.
> 4) Prior Year (last day of year) closing price
>
>
>

Mon Oct 31, 2016 4:14 pm (PDT) . Posted by:

lutherkinchen

Thank you for the quick response.

The only error we get, is today's closing price on the DJI.
For some reason, today's closing price reflects "N/A", as if no price is available.


Would you happen to know why that would occur, or how we could possibly correct it?
Randy, Your time and talents are greatly appreciated. Thanks again.




Name symbol 10/31/16 10/28/16 09/30/16 12/31/15 YTD SPX ETF SPY $212.55 $212.54 $216.30 $200.76 5.9% DJI ^DJI N/A $18,161.19 $18,308.15 $17,425.03 #VALUE! Nasdaq ^IXIC $5,189.13 $5,190.10 $5,312.00 $5,007.41 3.6% SPX 500 ^GSPC $2,126.15 $2,126.41 $2,168.27 $2,043.94 4.0% SP100 Lrg ^OEX $943.87 $945.32 $958.68 $911.43 3.6% SP400 Mid ^MID $1,509.46 $1,499.71 $1,552.26 $1,398.58 7.9% SP600 Sml ^SML $722.59 $718.63 $756.90 $671.74 7.6% RUT ^RUT $1,191.39 $1,187.61 $1,251.65 $1,135.89 4.9% NYSE ^NYA $10,481.89 $10,476.62 $10,721.73 $10,143.42 3.3% VIX ^VIX $17.06 $16.19 $13.29 $18.21 -6.3%


Mon Oct 31, 2016 5:59 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

That's a result of the licensing of any Dow Jone Indexes, as I mentioned.
I'm surprised you have any data, actually.

Apparently, Dow Jones is now allowing their historical quotes to be
delivered in a CSV file, but not the current quotes.

You can extract the current value of the Dow from the main Yahoo page:

=smfGetTagContent("http://finance.yahoo.com/","span",2,">Dow",,,,1)

On Mon, Oct 31, 2016 at 4:14 PM, lutherkinchen@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> The only error we get, is today's closing price on the DJI.
> For some reason, today's closing price reflects "N/A", as if no price is
> available.
>
> Would you happen to know why that would occur, or how we could possibly
> correct it?
> Randy, Your time and talents are greatly appreciated. Thanks again.
>
>
> Name symbol 10/31/16 10/28/16 09/30/16 12/31/15 YTD
> SPX ETF SPY $212.55 $212.54 $216.30 $200.76 5.9%
> DJI ^DJI N/A $18,161.19 $18,308.15 $17,425.03 #VALUE!
> Nasdaq ^IXIC $5,189.13 $5,190.10 $5,312.00 $5,007.41 3.6%
> SPX 500 ^GSPC $2,126.15 $2,126.41 $2,168.27 $2,043.94 4.0%
> SP100 Lrg ^OEX $943.87 $945.32 $958.68 $911.43 3.6%
> SP400 Mid ^MID $1,509.46 $1,499.71 $1,552.26 $1,398.58 7.9%
> SP600 Sml ^SML $722.59 $718.63 $756.90 $671.74 7.6%
> RUT ^RUT $1,191.39 $1,187.61 $1,251.65 $1,135.89 4.9%
> NYSE ^NYA $10,481.89 $10,476.62 $10,721.73 $10,143.42 3.3%
> VIX ^VIX $17.06 $16.19 $13.29 $18.21 -6.3%
>
>
>

Mon Oct 31, 2016 1:48 pm (PDT) . Posted by:

"Gary Funck" garyfunck

On 10/30/16 19:13:32, SMF Addin wrote:
> 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"))

Good tip. Thanks.

> 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")

I do enter them over a large range as an array formula now.

You're saying above not to bother with a start/end date, and just
let RCHGetYahooHistory() fill in as much recent data as will fit into
the large array? Then, just pick off the number of samples that
are needed?

> 2. Use RCHGetYahooHistory() to get the two date series for SPY. It should
> be the most complete series.
> =RCHGetYahooHistory("SPY",,,,,,,"d","D")

*two* date series for SPY?

> 3. Create the 5-year date series from the available SPY dates.

OK.

> 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.

Something like this:
Col A: Date (from SPY)
Col B: VLOOKUP() of date in A in prices series for Symbol1, or blank
Col C: VLOOKUP() of date in A in prices series for Symbol2, or blank

At this point, filter for rows in B and C that are non-blank.
The resulting table can be used as the data set to be analyzed.

Alternatively, let's say I want to write a user-defined function
to do this merge. How is the result of RCHGetYahooHistory()
placed into a VBA array? Do you have any examples where something
like that is done?

thanks,
- Gary

Mon Oct 31, 2016 2:16 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

On Mon, Oct 31, 2016 at 1:48 PM, Gary Funck gary@
​...wrote:

>
> > 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")
>
> I do enter them over a large range as an array formula now.
>
> You're saying above not to bother with a start/end date, and just
> let RCHGetYahooHistory() fill in as much recent data as will fit into
> the large array? Then, just pick off the number of samples that
> are needed?
>



*Yup. I always avoid specifying the dates unless I need to. If your ending
date is the most recent date, you should never need to use an ending date.​*

> > 2. Use RCHGetYahooHistory() to get the two date series for SPY. It should
> > be the most complete series.
> > =RCHGetYahooHistory("SPY",,,,,,,"d","D")
>
> *two* date series for SPY?
>

*​My bad. I copied the start of the previous item and didn't​ delete
enough. You just need the one.*

> 3. Create the 5-year date series from the available SPY dates.
>
> OK.
>
> > 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.
>
> Something like this:
> Col A: Date (from SPY)
> Col B: VLOOKUP() of date in A in prices series for Symbol1, or blank
> Col C: VLOOKUP() of date in A in prices series for Symbol2, or blank
>
> At this point, filter for rows in B and C that are non-blank.
> The resulting table can be used as the data set to be analyzed.
>
> Alternatively, let's say I want to write a user-defined function
> to do this merge. How is the result of RCHGetYahooHistory()
> placed into a VBA array? Do you have any examples where something
> like that is done?
>
>


*That's exactly what the smfPricesByDates() function does. It retrieves a
full range of data using RCHGetYahooHistory() and then searches for the
dates requested by the parameters to the function. ​The smfPricesBetween()
function is similar.*

*The pertinent statements in each: vHQ = RCHGetYahooHistory(pTicker, , ,
, , , , "d", "DA", 0, 0, 0, iDays, 2) vHQ = RCHGetYahooHistory(pTicker, ,
, , , , , "d", "DOHLCV", 0, 1, 0, Int(Now - pBegDate + 3), 6)*

*​The "pDim1" and "pDim2" tell the function how many rows (i.e. "days") and
columns of data to return.​*

*There are also a number of similar functions in the modZAdditional module.
Just things I played around with at some point, and may have made into a
function that replaced it, to do even more.*
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF

Tidak ada komentar:

Posting Komentar