15 Messages
Digest #4191
Messages
Wed Nov 1, 2017 10:44 pm (PDT) . Posted by:
nelsonmuntz1000
Thanks for your quick solution, Randy. Hopefully Yahoo gets their act together or we likely have a lot of work ahead to revamp existing models..
I looked into the Last Trade time and the Ex-Dividend Date trying to figure out the values. Looks like Yahoo uses a serial number start date of January 1, 1970 measured in seconds.
I believe the following formulas are correct, though I haven't tested them during market hours.
To convert the trade date and time in Eastern Standard time apply the following formula: =(datevalue-4*3600)/86400+25569. Date and time formats can be applied to the resultant value. Example Last Traded 1509566515 result would be 1-Nov-17 04:01:55 PM
To convert the ex-date apply the following formula: =datevalue/86400+25569
Hope this helps anyone trying to make sense of the numbers
I looked into the Last Trade time and the Ex-Dividend Date trying to figure out the values. Looks like Yahoo uses a serial number start date of January 1, 1970 measured in seconds.
I believe the following formulas are correct, though I haven't tested them during market hours.
To convert the trade date and time in Eastern Standard time apply the following formula: =(datevalue-
To convert the ex-date apply the following formula: =datevalue/86400+
Hope this helps anyone trying to make sense of the numbers
Wed Nov 1, 2017 11:25 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The ZIP is from 2017.10.31 because it also contains some IBD element number
changes. But the XLA file was last changed on 2017.10.24.
I'll have to try and avoid such a discrepancy in the future. It can be
confusing. Even I did a double take when someone cited the version to me. :(
On Wed, Nov 1, 2017 at 6:53 PM, Joe Mo joe_mo37@
...
wrote:
>
> I downloaded the 2.1.2017.10.31 zip file from your link, closed Excel, put
> the new version in my smf folder and restarted Excel. Now,
> =RCHGetElementNumber("Version") is returning:
>
> Stock Market Functions add-in, Version 2.1.2017.*10.24* (C:\Program
> Files\SMF Add-in; Windows (32-bit) NT 6.01; 14.0; ; ; 1)
>
> not 10.31. Is 10.24 the correct version despite the discrepancy?
>
> As always, many thanks for your expertise and dedication!
>
changes. But the XLA file was last changed on 2017.10.24.
I'll have to try and avoid such a discrepancy in the future. It can be
confusing. Even I did a double take when someone cited the version to me. :(
On Wed, Nov 1, 2017 at 6:53 PM, Joe Mo joe_mo37@
...
wrote:
>
> I downloaded the 2.1.2017.10.
> the new version in my smf folder and restarted Excel. Now,
> =RCHGetElementNumbe
>
> Stock Market Functions add-in, Version 2.1.2017.*10.
> Files\SMF Add-in; Windows (32-bit) NT 6.01; 14.0; ; ; 1)
>
> not 10.31. Is 10.24 the correct version despite the discrepancy?
>
> As always, many thanks for your expertise and dedication!
>
Wed Nov 1, 2017 11:28 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Turns out that is the dividend payment date, not the ex-dividend date. So
not so useful.
There are routines to convert between UNIX and EXCEL serial dates --
smfUNIX2Date() and smfDate2UNIX(). Just one parameter -- the date to be
converted.
On Wed, Nov 1, 2017 at 10:44 PM, ccoupe300@
...
wrote:
>
> Thanks for your quick solution, Randy. Hopefully Yahoo gets their act
> together or we likely have a lot of work ahead to revamp existing models..
>
> I looked into the Last Trade time and the Ex-Dividend Date trying to
> figure out the values. Looks like Yahoo uses a serial number start date of
> January 1, 1970 measured in seconds.
>
> I believe the following formulas are correct, though I haven't tested them
> during market hours.
>
> To convert the trade date and time in Eastern Standard time apply the
> following formula: =(datevalue-4*3600)/86400+25569. Date and time formats
> can be applied to the resultant value. Example Last Traded 1509566515
> result would be 1-Nov-17 04:01:55 PM
>
> To convert the ex-date apply the following formula: =datevalue/86400+25569
>
> Hope this helps anyone trying to make sense of the numbers
>
not so useful.
There are routines to convert between UNIX and EXCEL serial dates --
smfUNIX2Date(
converted.
On Wed, Nov 1, 2017 at 10:44 PM, ccoupe300@
...
wrote:
>
> Thanks for your quick solution, Randy. Hopefully Yahoo gets their act
> together or we likely have a lot of work ahead to revamp existing models..
>
> I looked into the Last Trade time and the Ex-Dividend Date trying to
> figure out the values. Looks like Yahoo uses a serial number start date of
> January 1, 1970 measured in seconds.
>
> I believe the following formulas are correct, though I haven't tested them
> during market hours.
>
> To convert the trade date and time in Eastern Standard time apply the
> following formula: =(datevalue-
> can be applied to the resultant value. Example Last Traded 1509566515
> result would be 1-Nov-17 04:01:55 PM
>
> To convert the ex-date apply the following formula: =datevalue/86400+
>
> Hope this helps anyone trying to make sense of the numbers
>
Wed Nov 1, 2017 11:20 pm (PDT) . Posted by:
"Dave Hook" davie_001
Hi,
Thanks for the patch, but cannot get it to work. Since your array goes to
Line 411, I copied a column of symbols from my SMF spreadsheet, and all
symbols below A20 were unfilled. Tried to copy the page to add several
leftovers, and managed to get them all in three tabs.
Copied column heads 15, 17-20 in columns past #4 to get the Last, Change,
Low, High but the Last Price shown is not the market close from today. The
refresh of the SMF tab made no corrections, so I wonder what the so-called
Last really represents.
If you could tell me what you think happened, it would be great, as
creating the array formula seems really hard.
Dave...
P.S. This function is essential to my elaborate portfolio spreadsheet that
gives me a (almost) real time valuation of twelve accounts with seventy
holdings, that Excel pivot tables let me slice and dice away.
Thanks for the patch, but cannot get it to work. Since your array goes to
Line 411, I copied a column of symbols from my SMF spreadsheet, and all
symbols below A20 were unfilled. Tried to copy the page to add several
leftovers, and managed to get them all in three tabs.
Copied column heads 15, 17-20 in columns past #4 to get the Last, Change,
Low, High but the Last Price shown is not the market close from today. The
refresh of the SMF tab made no corrections, so I wonder what the so-called
Last really represents.
If you could tell me what you think happened, it would be great, as
creating the array formula seems really hard.
Dave...
P.S. This function is essential to my elaborate portfolio spreadsheet that
gives me a (almost) real time valuation of twelve accounts with seventy
holdings, that Excel pivot tables let me slice and dice away.
Wed Nov 1, 2017 11:35 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Check to make sure you have the latest version installed:
=RCHGetElementNumber("Version")
As I said on the blog, I rewrote the smfGetYahooPortfolioView() routine so
it would be faster and could handle more ticker symbols.I haven'[t checked
lately, but equity quotes are supposed to be real time, although options
quotes are delayed.
For the three ticker symbols in the sample, the "Last" prices appear to
match what is on Yahoo web pages. What ticker symbols aren't matching for
you?
On Wed, Nov 1, 2017 at 9:38 PM, Dave Hook dahook@
...
wrote:
>
> Thanks for the patch, but cannot get it to work. Since your array goes to
> Line 411, I copied a column of symbols from my SMF spreadsheet, and all
> symbols below A20 were unfilled. Tried to copy the page to add several
> leftovers, and managed to get them all in three tabs.
>
> Copied column heads 15, 17-20 in columns past #4 to get the Last, Change,
> Low, High but the Last Price shown is not the market close from today. The
> refresh of the SMF tab made no corrections, so I wonder what the so-called
> Last really represents.
>
> If you could tell me what you think happened, it would be great, as
> creating the array formula seems really hard.
>
> Dave...
>
> P.S. This function is essential to my elaborate portfolio spreadsheet that
> gives me a (almost) real time valuation of twelve accounts with seventy
> holdings, that Excel pivot tables let me slice and dice away.
>
=RCHGetElementNumbe
As I said on the blog, I rewrote the smfGetYahooPortfoli
it would be faster and could handle more ticker symbols.I haven'[t checked
lately, but equity quotes are supposed to be real time, although options
quotes are delayed.
For the three ticker symbols in the sample, the "Last" prices appear to
match what is on Yahoo web pages. What ticker symbols aren't matching for
you?
On Wed, Nov 1, 2017 at 9:38 PM, Dave Hook dahook@
...
wrote:
>
> Thanks for the patch, but cannot get it to work. Since your array goes to
> Line 411, I copied a column of symbols from my SMF spreadsheet, and all
> symbols below A20 were unfilled. Tried to copy the page to add several
> leftovers, and managed to get them all in three tabs.
>
> Copied column heads 15, 17-20 in columns past #4 to get the Last, Change,
> Low, High but the Last Price shown is not the market close from today. The
> refresh of the SMF tab made no corrections, so I wonder what the so-called
> Last really represents.
>
> If you could tell me what you think happened, it would be great, as
> creating the array formula seems really hard.
>
> Dave...
>
> P.S. This function is essential to my elaborate portfolio spreadsheet that
> gives me a (almost) real time valuation of twelve accounts with seventy
> holdings, that Excel pivot tables let me slice and dice away.
>
Thu Nov 2, 2017 1:42 am (PDT) . Posted by:
"V. Shankar" tarakayan
today, it's been like this for a while....Shankar
On Thursday, November 2, 2017, 2:22:06 AM GMT+5:30, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
Announced on the blog 25 minutes ago, when I ran into myself:
https://smf-add-in.blogspot.com/2017/11/alert-rchgetyahooquotes-problems.html
On Wed, Nov 1, 2017 at 1:30 PM, codyklein@... wrote:
Is it me or did anyone else notice RCHGetYahooQuotes stopped working late this afternoon?
On Thursday, November 2, 2017, 2:22:06 AM GMT+5:30, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
Announced on the blog 25 minutes ago, when I ran into myself:
https://smf-add-in.blogspot.com/2017/11/alert-rchgetyahooquotes-problems.html
On Wed, Nov 1, 2017 at 1:30 PM, codyklein@... wrote:
Is it me or did anyone else notice RCHGetYahooQuotes stopped working late this afternoon?
Thu Nov 2, 2017 2:14 am (PDT) . Posted by:
peter.oliver
Hi Randy,
Thanks for the prompt alternative access to Yahoo data.
I installed the latest version, made the parameters changes in my spreadsheet and it is working as intended.
I realized that the parameter NOW on the RCHGetYahooQuotes() is now removed from the smfGetYahooPortfolioView() and the keyboard F9 key is not working anymore. Any other easy way to refresh the data, please?
Peter.
Thanks for the prompt alternative access to Yahoo data.
I installed the latest version, made the parameters changes in my spreadsheet and it is working as intended.
I realized that the parameter NOW on the RCHGetYahooQuotes(
Peter.
Thu Nov 2, 2017 2:32 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Just run the smfForceRecalculation macro. I have it assigned to a button on
my Quick Access Toolbar. I use it often.
If you just want to have it recalculate the smfGetYahooPortfolioView()
function, add a dummy ticker in the list. That will change the URL and
force it to get a new version of the JSON file.
On Thu, Nov 2, 2017 at 2:14 AM, peter.oliver@
...
wrote:
> Thanks for the prompt alternative access to Yahoo data.
>
> I installed the latest version, made the parameters changes in my
> spreadsheet and it is working as intended.
>
> I realized that the parameter NOW on the RCHGetYahooQuotes() is now
> removed from the smfGetYahooPortfolioView() and the keyboard F9 key is not
> working anymore. Any other easy way to refresh the data, please?
>
my Quick Access Toolbar. I use it often.
If you just want to have it recalculate the smfGetYahooPortfoli
function, add a dummy ticker in the list. That will change the URL and
force it to get a new version of the JSON file.
On Thu, Nov 2, 2017 at 2:14 AM, peter.oliver@
...
wrote:
> Thanks for the prompt alternative access to Yahoo data.
>
> I installed the latest version, made the parameters changes in my
> spreadsheet and it is working as intended.
>
> I realized that the parameter NOW on the RCHGetYahooQuotes(
> removed from the smfGetYahooPortfoli
> working anymore. Any other easy way to refresh the data, please?
>
Thu Nov 2, 2017 5:30 am (PDT) . Posted by:
"V. Shankar" tarakayan
Ok. I switched To smfgetyahoo....So we wait for a while to see if RCHGetYahooQuotes works?
Thanks..
Sent from my iPhone
> On 02-Nov-2017, at 3:02 PM, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
>
> Just run the smfForceRecalculation macro. I have it assigned to a button on my Quick Access Toolbar. I use it often.
>
> If you just want to have it recalculate the smfGetYahooPortfolioView() function, add a dummy ticker in the list. That will change the URL and force it to get a new version of the JSON file.
>
> On Thu, Nov 2, 2017 at 2:14 AM, peter.oliver@
> ...
> wrote:
>> Thanks for the prompt alternative access to Yahoo data.
>>
>> I installed the latest version, made the parameters changes in my spreadsheet and it is working as intended.
>>
>> I realized that the parameter NOW on the RCHGetYahooQuotes() is now removed from the smfGetYahooPortfolioView() and the keyboard F9 key is not working anymore. Any other easy way to refresh the data, please?
>>
>
>
Thanks..
Sent from my iPhone
> On 02-Nov-2017, at 3:02 PM, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
>
> Just run the smfForceRecalculati
>
> If you just want to have it recalculate the smfGetYahooPortfoli
>
> On Thu, Nov 2, 2017 at 2:14 AM, peter.oliver@
> ...
> wrote:
>> Thanks for the prompt alternative access to Yahoo data.
>>
>> I installed the latest version, made the parameters changes in my spreadsheet and it is working as intended.
>>
>> I realized that the parameter NOW on the RCHGetYahooQuotes(
>>
>
>
Thu Nov 2, 2017 6:28 am (PDT) . Posted by:
indinvestor111
Hi Randy,
Like the others, I much appreciate the work you do on this excellent add-in! Was wondering if you've considered creating a get element function for the last price from the Yahoo KS page or or some other page as a backup for when the GetYahooQuotes server is acting up.
Thanks
Like the others, I much appreciate the work you do on this excellent add-in! Was wondering if you've considered creating a get element function for the last price from the Yahoo KS page or or some other page as a backup for when the GetYahooQuotes server is acting up.
Thanks
Thu Nov 2, 2017 6:29 am (PDT) . Posted by:
bob_15861586
First, I want to thank Randy for his work on SMF and his fast response to Yahoo's change.
I am hoping to use the function smfGetYahooPortfolioView to get the last value of a stock given its stock symbol. I was hoping to find detailed documentation on how this function works but I failed to find it. What I would like is to be able to place in a cell, something like: =smfGetYahooPortfolioView("XOM") and have the last value of XOM appear in the cell. By the way, XOM is the stock symbol for Exxon Mobil. Please tell me how to do this.
Bob
I am hoping to use the function smfGetYahooPortfoli
Bob
Thu Nov 2, 2017 7:20 am (PDT) . Posted by:
john_hoel
Yesterday I changed to smfGetYahooPortfolioView() with array-entry and it worked flawlessly. Today it mostly does not work. I get either the first cell only, or the first few cells only, or the function is entirely ignored.
I tried recreating the array entry but nothing changed. I tried completely closing Excel, then reopen, no change.
I tried recreating the array entry but nothing changed. I tried completely closing Excel, then reopen, no change.
Thu Nov 2, 2017 7:39 am (PDT) . Posted by:
thadmalley
I'm not sure why you would need to do this as an array. Is there an functionality where it just works line by line? I know that is less efficient, but that's how I use the add in.
I'm not actually sure how the array works from the example, anyways.
I'm not actually sure how the array works from the example, anyways.
Thu Nov 2, 2017 7:45 am (PDT) . Posted by:
codyklein
Anyone have a formula to get the following:
1-Time of last trade as a text value format 9:30 AM
2-Date of last trade as text
3- EBITDA
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar