15 Messages
Digest #4018
Messages
Sat May 20, 2017 2:10 pm (PDT) . Posted by:
eremon9
Regarding local workbook efficiencies that might reduce processing time and minimize dependence on externally acquired data, I realize that I'm obtaining arrays of closing prices for the same tickers daily, with the only changes being for the prior trading date. I've been wondering about a way to just get the most recent closing price, and add it to the existing table, perhaps using RCHGetYahooQuotes and/or SMFPricesByDates. Seems complicated, as there could be days where this retrieval isn't done. Any thoughts? Would something like this be worth the added complexity?
Tom
Tom
Sat May 20, 2017 2:34 pm (PDT) . Posted by:
tonyestep
Tom, try the idea in my earlier post: https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32174 https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32174
Sat May 20, 2017 3:29 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Yahoo adjusts for splits, but not for dividends. I do the dividend
adjustments on their data. If you want to remove the dividend adjustments,
just divide the O/H/L/C by the "Div Adj" column I show. The closing price I
produce should be reproducible by multiplying the "Div Adj" and "Split
Adj" against the unadjusted price.
For example, MMM data from the smfGetYahooHistory template:
*Date* *Open* *High* *Low* *Close* *Volume* *Unadj* *Div Adj* *Split Adj*
*Dividend*
2017-05-18 $194.5000 $195.8200 $193.5400 $195.2400 1929000 $195.2400 1.00000
1.00000
2017-05-17 $196.7400 $196.7400 $194.7200 $194.8400 1881800 $194.8400 1.00000
1.00000 $1.1750
2017-05-16 $196.3075 $196.7648 $195.6912 $196.7350 1183600 $197.9100 0.99406
1.00000
So: "Close" / "Div Adj" = 196.735003662109 / 0.99406295802002 = $197.91
Why would you want prices adjusted by splits, but not by dividends?
Historical quotes are out of context with each other without such
adjustments. Just imagine a stock that pays out a special 10% cash
dividend. Would you want to see such a change in price on your quotes?
I'm cringing at the thought of needing to scrub Yahoo's data. But zero
value and null values are causing errors. The question is whether or not
to throw out days with errors, or to carry forward the previous closing
quote (which could be a lot of work). Scrubbing will add more processing
overhead to retrieving the data...
On Sat, May 20, 2017 at 1:53 PM, tamurphy@
...wrote:
>
> I see from what you provided that the splits for RSP and BRK-B are
> reflected in the "Close" price, but not in the "Unadjusted close" price,
> which is what I was using, as I wanted historical prices unadjusted for
> dividends. So I gather the unadjusted closing prices ignore splits. It
> looks as though the adjusted closing prices do account for dividends, so
> I'm not sure how I'll be able to get closing prices adjusted for splits but
> not for dividends.
>
> I'm now getting the same as you reported for WFC-PL—i.e., just one day.
>
> By the way, I also got a number of inexplicable "null" returns for
> 6/29/16—e.g., SAN and JNJ—with data for dates before and after.
>
>
>
adjustments on their data. If you want to remove the dividend adjustments,
just divide the O/H/L/C by the "Div Adj" column I show. The closing price I
produce should be reproducible by multiplying the "Div Adj" and "Split
Adj" against the unadjusted price.
For example, MMM data from the smfGetYahooHistory template:
*Date* *Open* *High* *Low* *Close* *Volume* *Unadj* *Div Adj* *Split Adj*
*Dividend*
2017-05-18 $194.5000 $195.8200 $193.5400 $195.2400 1929000 $195.2400 1.00000
1.00000
2017-05-17 $196.7400 $196.7400 $194.7200 $194.8400 1881800 $194.8400 1.00000
1.00000 $1.1750
2017-05-16 $196.3075 $196.7648 $195.6912 $196.7350 1183600 $197.9100 0.99406
1.00000
So: "Close" / "Div Adj" = 196.735003662109 / 0.99406295802002 = $197.91
Why would you want prices adjusted by splits, but not by dividends?
Historical quotes are out of context with each other without such
adjustments. Just imagine a stock that pays out a special 10% cash
dividend. Would you want to see such a change in price on your quotes?
I'm cringing at the thought of needing to scrub Yahoo's data. But zero
value and null values are causing errors. The question is whether or not
to throw out days with errors, or to carry forward the previous closing
quote (which could be a lot of work). Scrubbing will add more processing
overhead to retrieving the data...
On Sat, May 20, 2017 at 1:53 PM, tamurphy@
...wrote:
>
> I see from what you provided that the splits for RSP and BRK-B are
> reflected in the "Close" price, but not in the "Unadjusted close" price,
> which is what I was using, as I wanted historical prices unadjusted for
> dividends. So I gather the unadjusted closing prices ignore splits. It
> looks as though the adjusted closing prices do account for dividends, so
> I'm not sure how I'll be able to get closing prices adjusted for splits but
> not for dividends.
>
> I'm now getting the same as you reported for WFC-PL—i.e., just one day.
>
> By the way, I also got a number of inexplicable "null" returns for
> 6/29/16—e.g., SAN and JNJ—with data for dates before and after.
>
>
>
Sat May 20, 2017 3:38 pm (PDT) . Posted by:
eremon9
tonyestep–
Your idea is what prompted my question. Having gathered historical closing prices for a large range of dates, I'm casting about for an efficient way to append prices for a potentially variable number of succeeding dates. I suppose the problem arises from the fact that the historical price retrieval formula is array-entered, thereby fixing the date and cell ranges. A workaround might be to copy values only (sans formulas) to another range, as you suggest; then adjust the starting date of the array formula so that it acquires only missing subsequent closes; and finally appending the newly retrieved data to the saved range. Sounds pretty kludgy, but it would likely work. Thoughts?
Tom
Your idea is what prompted my question. Having gathered historical closing prices for a large range of dates, I'm casting about for an efficient way to append prices for a potentially variable number of succeeding dates. I suppose the problem arises from the fact that the historical price retrieval formula is array-entered, thereby fixing the date and cell ranges. A workaround might be to copy values only (sans formulas) to another range, as you suggest; then adjust the starting date of the array formula so that it acquires only missing subsequent closes; and finally appending the newly retrieved data to the saved range. Sounds pretty kludgy, but it would likely work. Thoughts?
Tom
Sat May 20, 2017 3:49 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
That takes a chance that a split or dividend occurs in between.
Back in the 1980's, I used to maintain my own quotes databases locally, so
I could run all my scans against them (I had some advanced software
products). But that meant I had to maintain the data as well -- for every
ticker change, stock split, dividend, spin-off, whatever. What a pain.
One of the reasons I wrote the add-in is so I could get my needed data on
the fly, and let someone else do all that maintenance work. I also migrated
to online screeners and charting tools, so I didn't need the data locally.
So my use of the add-in is primarily as a dashboard, to get specific views
of data AFTER I use the screening and charting tools.
Which is why I rarely run into issues like hitting the 1000-webpage limit.
:)
In fact, early on, I was using an offline data repository (mostly for AdvFN
and financial statements data) -- there is a "pFile" parameter on the
RCHGetElementNumber() function. That was intended to tell the function
whether or not to grab the data online or from the offline repository (much
quicker). These days, with faster Internet speeds, not really needed.
On Sat, May 20, 2017 at 2:10 PM, tamurphy@
...wrote:
>
> Regarding local workbook efficiencies that might reduce processing time
> and minimize dependence on externally acquired data, I realize that I'm
> obtaining arrays of closing prices for the same tickers daily, with the
> only changes being for the prior trading date. I've been wondering about a
> way to just get the most recent closing price, and add it to the existing
> table, perhaps using RCHGetYahooQuotes and/or SMFPricesByDates. Seems
> complicated, as there could be days where this retrieval isn't done. Any
> thoughts? Would something like this be worth the added complexity?
>
>
>
Back in the 1980's, I used to maintain my own quotes databases locally, so
I could run all my scans against them (I had some advanced software
products). But that meant I had to maintain the data as well -- for every
ticker change, stock split, dividend, spin-off, whatever. What a pain.
One of the reasons I wrote the add-in is so I could get my needed data on
the fly, and let someone else do all that maintenance work. I also migrated
to online screeners and charting tools, so I didn't need the data locally.
So my use of the add-in is primarily as a dashboard, to get specific views
of data AFTER I use the screening and charting tools.
Which is why I rarely run into issues like hitting the 1000-webpage limit.
:)
In fact, early on, I was using an offline data repository (mostly for AdvFN
and financial statements data) -- there is a "pFile" parameter on the
RCHGetElementNumber
whether or not to grab the data online or from the offline repository (much
quicker). These days, with faster Internet speeds, not really needed.
On Sat, May 20, 2017 at 2:10 PM, tamurphy@
...wrote:
>
> Regarding local workbook efficiencies that might reduce processing time
> and minimize dependence on externally acquired data, I realize that I'm
> obtaining arrays of closing prices for the same tickers daily, with the
> only changes being for the prior trading date. I've been wondering about a
> way to just get the most recent closing price, and add it to the existing
> table, perhaps using RCHGetYahooQuotes and/or SMFPricesByDates. Seems
> complicated, as there could be days where this retrieval isn't done. Any
> thoughts? Would something like this be worth the added complexity?
>
>
>
Sat May 20, 2017 4:50 pm (PDT) . Posted by:
eremon9
RCH wrote: "Why would you want prices adjusted by splits, but not by dividends?"
I use the unadjusted closing prices in maintaining my portfolio history, where stock ownership, dividends received, cash, deposits and withdrawals are accounted for in a transactions log that drives the calculated daily results database, which relies on closing prices unadjusted for dividends. Hopefully that description makes sense. In any event, it's been working like a charm until this Yahoo fiasco. Entirely automated except for the maintenance of the transactions log, which takes just a few minutes per month.
Since portfolio historical holdings and prices are frozen, I'm thinking the best bet might be to find a way to maintain static values for relevant closing prices. I also maintain a watchlist where your suggestions are entirely apropos to analyses, and adjusted prices are used.
Thanks for explaining how to back into closing prices adjusted for splits but not dividends.
Tom
I use the unadjusted closing prices in maintaining my portfolio history, where stock ownership, dividends received, cash, deposits and withdrawals are accounted for in a transactions log that drives the calculated daily results database, which relies on closing prices unadjusted for dividends. Hopefully that description makes sense. In any event, it's been working like a charm until this Yahoo fiasco. Entirely automated except for the maintenance of the transactions log, which takes just a few minutes per month.
Since portfolio historical holdings and prices are frozen, I'm thinking the best bet might be to find a way to maintain static values for relevant closing prices. I also maintain a watchlist where your suggestions are entirely apropos to analyses, and adjusted prices are used.
Thanks for explaining how to back into closing prices adjusted for splits but not dividends.
Tom
Sat May 20, 2017 4:53 pm (PDT) . Posted by:
eadamy
I do a lot of testing runs with my model where I will reuse the same data. The calls were really fast with the old Y. Now, whether I use Y or Quandl, the calls are much slower. I'm in the process of building a variant array which will hold symbols and history for anything I've retrieved during the day. Since I use adjusted close data exclusively, there is no value for me in retaining the data permanently.
Earl Adamy
Earl Adamy
Sat May 20, 2017 5:10 pm (PDT) . Posted by:
eadamy
> The question is whether or not to throw out days with errors, or to carry forward the previous closing quote (which could be a lot of work). Scrubbing will add more processing overhead to retrieving the data.
I vote for very limited scrubbing. I have historical models which consume large volumes of adjusted close data and I have historical month by month results from the old Y data which tracked very closely to real-time trading for years. I've been doing a massive amount of testing of adjusted close data with both Y (using your new function) and Quandl Quote Media. Believe it or not, Y's dividend data (free) are more accurate than QM's ($50/month).
In running all of these comparisons, I'm finding that it does not take much in the way of errors to destroy the cumulative accuracy of historical results. I would like to see the smf function carry forward no more than 1 or 2 days to infill Y's null price data. Anything more than that and the results become badly compromised. That is where leaning on Y to clean up bad symbols is critical.
I use about two dozen ETFs heavily. I can now shift rapidly between Y and QM and have filed data reports with Y and dividend reports with QM. In the near term it is a matter who which one cleans up first and I suspect it will be QM because they are paid. However, in the long term, I have more confidence in the quality of Y's dividend history which you so nicely process into adjusted close.
Earl Adamy
I vote for very limited scrubbing. I have historical models which consume large volumes of adjusted close data and I have historical month by month results from the old Y data which tracked very closely to real-time trading for years. I've been doing a massive amount of testing of adjusted close data with both Y (using your new function) and Quandl Quote Media. Believe it or not, Y's dividend data (free) are more accurate than QM's ($50/month).
In running all of these comparisons, I'm finding that it does not take much in the way of errors to destroy the cumulative accuracy of historical results. I would like to see the smf function carry forward no more than 1 or 2 days to infill Y's null price data. Anything more than that and the results become badly compromised. That is where leaning on Y to clean up bad symbols is critical.
I use about two dozen ETFs heavily. I can now shift rapidly between Y and QM and have filed data reports with Y and dividend reports with QM. In the near term it is a matter who which one cleans up first and I suspect it will be QM because they are paid. However, in the long term, I have more confidence in the quality of Y's dividend history which you so nicely process into adjusted close.
Earl Adamy
Sat May 20, 2017 2:32 pm (PDT) . Posted by:
tonyestep
Get all the prices once, using the Annual option, in a separate array. Or perhaps even better, get all the prices you need in a separate workbook, then refer to that workbook in your analysis book. See: https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32174 https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32174
Sat May 20, 2017 2:38 pm (PDT) . Posted by:
palminha
Well I have many (hundreds) tickers that I need 10 years data, so can you send me one sample file or where can I get it?
Thank you
Thank you
Sat May 20, 2017 3:45 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Let me back up a step -- why do you need 10 years off historical data for
hundreds of tickerrs? How are you using it?
On Sat, May 20, 2017 at 2:38 PM, palminha@
...wrote:
>
> Well I have many (hundreds) tickers that I need 10 years data, so can you
> send me one sample file or where can I get it?
>
>
hundreds of tickerrs? How are you using it?
On Sat, May 20, 2017 at 2:38 PM, palminha@
...wrote:
>
> Well I have many (hundreds) tickers that I need 10 years data, so can you
> send me one sample file or where can I get it?
>
>
Sat May 20, 2017 3:18 pm (PDT) . Posted by:
lewglenn
Hi Randy,
I know you've been dealing with this problem for several weeks now thanks to Yahoo's latest actions. I've downloaded the latest SMFAdd-In. Can you please tell me what I need to do to replace the RCHGetYahooHistory call in the following coding (to get 50-day MA):
For r = 2 To rowmax
If IsEmpty(Cells(r, "P")) Then
Res = Cells(r, "A")
Cells(r, "I") = Application.WorksheetFunction.Average(RCHGetYahooHistory(Res, , , , , , , , "a", 0, , , 50, 1))
End If
Next r
Many thanks,
Lew
I know you've been dealing with this problem for several weeks now thanks to Yahoo's latest actions. I've downloaded the latest SMFAdd-In. Can you please tell me what I need to do to replace the RCHGetYahooHistory call in the following coding (to get 50-day MA):
For r = 2 To rowmax
If IsEmpty(Cells(
Res = Cells(r, "A"
Cells(r, "I"
End If
Next r
Many thanks,
Lew
Sat May 20, 2017 4:41 pm (PDT) . Posted by:
eadamy
> I just added code to default them if no range was used for the function, to the difference between the two dates and the size of the pItems parameter. That may be larger than needed, but it will just use what it needs when returning data.
Just to clarify (when calling from VBA) are you resizing the returned array to match the number of active data rows?
When I moved to processing CSV data, the split() function provided a properly sized array so I was able to dispense with processing the empty rows in my model. In implementing the new smf function, I pass a row estimate based on ((((calendar days) / 5) * 7) *1.1). When the array is returned, I size a new array and move the data into it. That would be duplicative if you are already doing (or planning on doing) that.
Earl
Just to clarify (when calling from VBA) are you resizing the returned array to match the number of active data rows?
When I moved to processing CSV data, the split() function provided a properly sized array so I was able to dispense with processing the empty rows in my model. In implementing the new smf function, I pass a row estimate based on ((((calendar days) / 5) * 7) *1.1). When the array is returned, I size a new array and move the data into it. That would be duplicative if you are already doing (or planning on doing) that.
Earl
Sat May 20, 2017 4:47 pm (PDT) . Posted by:
eadamy
Google Drive has an option to maintain and sync the files on your Windows machine. This works particularly well if you have automatic backup to a network drive. Here is an article:
https://www.howtogeek.com/228989/how-to-use-the-desktop-google-drive-app/ https://www.howtogeek.com/228989/how-to-use-the-desktop-google-drive-app/
Earl Adamy
https://www.howtogeek.com/228989/how-to-use-the-desktop-google-drive-app/ https://www.howtogeek.com/228989/how-to-use-the-desktop-google-drive-app/
Earl Adamy
Sat May 20, 2017 5:15 pm (PDT) . Posted by:
"Marc Johnson" marclerjohnson
A lot of you are very savvy, so I apologize if this observation wastes your time, but . . .
After the ransomware encrypts all your drive letters and your cloud sync replaces all your cloud files with the encrypted files, I hope everyone has at least one offline (disconnected, "air gap") backup. Terabyte USB3 drives might seem very retro to some, but having two, used for on-site and off-site backups, can save your derriere.
Get Outlook for Android
On Sat, May 20, 2017 at 5:47 PM -0600, "earladamy@gmail.com [smf_addin]" <smf_addin@yahoogroups.com> wrote:
Google Drive has an option to maintain and sync the files on your Windows machine. This works particularly well if you have automatic backup to a network drive. Here is an article:
https://www.howtogeek.com/228989/how-to-use-the-desktop-google-drive-app/
Earl Adamy
After the ransomware encrypts all your drive letters and your cloud sync replaces all your cloud files with the encrypted files, I hope everyone has at least one offline (disconnected, "air gap") backup. Terabyte USB3 drives might seem very retro to some, but having two, used for on-site and off-site backups, can save your derriere.
Get Outlook for Android
On Sat, May 20, 2017 at 5:47 PM -0600, "earladamy@gmail.com [smf_addin]" <smf_addin@yahoogroups.com> wrote:
Google Drive has an option to maintain and sync the files on your Windows machine. This works particularly well if you have automatic backup to a network drive. Here is an article:
https://www.howtogeek.com/228989/how-to-use-the-desktop-google-drive-app/
Earl Adamy
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar