15 Messages
Digest #4318
Messages
Mon Mar 19, 2018 9:30 am (PDT) . Posted by:
"Pieter van Leeuwen" pietertvl
Using the protocol you suggested in your blog awhile back
Create spreadsheet in Google Docs, using this to fetch the open (etc)
=GOOGLEFINANCE($A4,"priceopen")
then your SMF tool pulls the entire matrix of symbols and quotes into Excel
Could be I misunderstood what went down ... or is that "=GOOGLEFINANCE( )" instruction on its way out too?
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Monday, March 19, 2018 9:08 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Google Finance not working ... working for me
Parts of Google may still be working. For now.. What URL and function are you using for the quotes?
But if I enter the old Google Finance URL in my browser here:
https://finance.google.com/finance?q=MMM
....I now get redirected to the new (limited) Google Finance:
https://www.google.com/search?q=MMM <https://www.google.com/search?q=MMM&tbm=fin> &tbm=fin
On Mon, Mar 19, 2018 at 6:58 AM, 'Pieter van Leeuwen' pieter_biz@
...
wrote:
I was rather concerned when I read this exchange last night.
But I'm not seeing anything different this morning than last week .... quotes still being pulled into Google Docs as before with the GoogleFinance quote fetch tool.
Was this remark targeted to other financial data formerly supplied by GF? I'm getting quote updates just fine..
Create spreadsheet in Google Docs, using this to fetch the open (etc)
=GOOGLEFINANCE($A4,"priceopen")
then your SMF tool pulls the entire matrix of symbols and quotes into Excel
Could be I misunderstood what went down ... or is that "=GOOGLEFINANCE( )" instruction on its way out too?
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Monday, March 19, 2018 9:08 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Google Finance not working ... working for me
Parts of Google may still be working. For now.. What URL and function are you using for the quotes?
But if I enter the old Google Finance URL in my browser here:
https://finance.google.com/finance?q=MMM
....I now get redirected to the new (limited) Google Finance:
https://www.google.com/search?q=MMM <https://www.google.com/search?q=MMM&tbm=fin> &tbm=fin
On Mon, Mar 19, 2018 at 6:58 AM, 'Pieter van Leeuwen' pieter_biz@
...
wrote:
I was rather concerned when I read this exchange last night.
But I'm not seeing anything different this morning than last week .... quotes still being pulled into Google Docs as before with the GoogleFinance quote fetch tool.
Was this remark targeted to other financial data formerly supplied by GF? I'm getting quote updates just fine..
Mon Mar 19, 2018 9:38 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
I don't know if that process will be affected or not. Maybe not. Probably
not?
Everything I've seen going away is related to the Google Finance web pages.
They still offer current quotes through their new web pages, so they have
the data to feed the API for Google Sheets. I'm not sure why they would
unplug that...?
On Mon, Mar 19, 2018 at 9:30 AM, 'Pieter van Leeuwen' pieter_biz@
...
wrote:
>
> Using the protocol you suggested in your blog awhile back
>
>
>
> Create spreadsheet in Google Docs, using this to fetch the open (etc)
>
>
>
> =GOOGLEFINANCE($A4,"priceopen")
>
>
>
> then your SMF tool pulls the entire matrix of symbols and quotes into Excel
>
>
>
> Could be I misunderstood what went down ... or is that "=GOOGLEFINANCE(
> )" instruction on its way out too?
>
>
>
not?
Everything I've seen going away is related to the Google Finance web pages.
They still offer current quotes through their new web pages, so they have
the data to feed the API for Google Sheets. I'm not sure why they would
unplug that...?
On Mon, Mar 19, 2018 at 9:30 AM, 'Pieter van Leeuwen' pieter_biz@
...
wrote:
>
> Using the protocol you suggested in your blog awhile back
>
>
>
> Create spreadsheet in Google Docs, using this to fetch the open (etc)
>
>
>
> =GOOGLEFINANCE(
>
>
>
> then your SMF tool pulls the entire matrix of symbols and quotes into Excel
>
>
>
> Could be I misunderstood what went down ... or is that "=GOOGLEFINANC
> )" instruction on its way out too?
>
>
>
Tue Mar 20, 2018 3:25 pm (PDT) . Posted by:
tonyestep
All the Google history pages seem to be gone with the wind. This leaves us with no backup if Yahoo does the same. Also, there are days when the Yahoo database doesn't get properly updated and the data can't be used until late in the day or sometimes not at all. When that happened in the past, I had a parallel system using Google, but now that's not available. Is there any other hope for an alternative source, even if there was a subscription fee attached? This is scary....
Tue Mar 20, 2018 5:46 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
There are plenty of other free sources of historical quotes (note that some
require being logged in, or API keys):
https://www.quandl.com/api/v3/datasets/WIKI/AAPL.csv
https://stooq.com/q/d/l/?s=AAPL.US&i=d
https://www.alphavantage.co/query?function=TIME_SERIES_DAILY_ADJUSTED&symbol=AAPL&outputsize=full&apikey=...&datatype=csv
https://marketdata.websol.barchart.com/getHistory.csv?apikey=...&symbol=AAPL&startDate=20100101&splits=true÷nds=true&volume=sum&nearby=1&jerq=true
https://api.iextrading.com/1.0/stock/aapl/chart/5y?format=csv
https://api.tiingo.com/tiingo/daily/AAPL/prices?startDate=2012-1-1&endDate=2016-1-1
And there are more...
On Tue, Mar 20, 2018 at 3:19 PM, tonyestep@
...
wrote:
>
> All the Google history pages seem to be gone with the wind. This leaves us
> with no backup if Yahoo does the same. Also, there are days when the Yahoo
> database doesn't get properly updated and the data can't be used until late
> in the day or sometimes not at all. When that happened in the past, I had a
> parallel system using Google, but now that's not available. Is there any
> other hope for an alternative source, even if there was a subscription fee
> attached? This is scary....
>
require being logged in, or API keys):
https://www.quandl.com/api/v3/datasets/WIKI/AAPL.csv
https://stooq.com/q/d/l/?s=AAPL.US&i=d
https://www.alphavantage.co/query?function=TIME_SERIES_DAILY_ADJUSTED&symbol=AAPL&outputsize=full&apikey=...&datatype=csv
https://marketdata.websol.barchart.com/getHistory.csv?apikey=...&symbol=AAPL&startDate=20100101&splits=true÷nds=true&volume=sum&nearby=1&jerq=true
https://api.iextrading.com/1.0/stock/aapl/chart/5y?format=csv
https://api.tiingo.com/tiingo/daily/AAPL/prices?startDate=2012-1-1&endDate=2016-1-1
And there are more...
On Tue, Mar 20, 2018 at 3:19 PM, tonyestep@
...
wrote:
>
> All the Google history pages seem to be gone with the wind. This leaves us
> with no backup if Yahoo does the same. Also, there are days when the Yahoo
> database doesn't get properly updated and the data can't be used until late
> in the day or sometimes not at all. When that happened in the past, I had a
> parallel system using Google, but now that's not available. Is there any
> other hope for an alternative source, even if there was a subscription fee
> attached? This is scary....
>
Mon Mar 19, 2018 9:31 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
There very well could be line items I didn't add, since I used a few web
pages as my templates and included what I found there. There's no way I
could know of every line item that GuruFocus would offer. Or this could be
a new line item they added since I wrote the function. Because of the
nature of the extraction, it can't be defined as an RCHGetElementNumber()
extraction. It requires special processing that meant writing a new
function for it.
One of the reasons I wrote the function is because normal extraction is not
straight-forward. It can be done, but there are exceptions here and there.
Something like this general formula works most of the time:
=smfGetTagContent("https://www.gurufocus.com/financials/MMM","td",C4,">Fiscal
Period",">Minority Interest","ttm",,1)
Where C4 ranges from -16 to -2 for the annuals, -1 for the TTM, and 1 to 5
for the quarterlies.
I notice the smfGetGuruFocusCSVItem() template has the Minority Interest
line item, so it must have been available when I created that template.
On Mon, Mar 19, 2018 at 6:49 AM, ajtalukdar@
...
wrote:
>
> Working with your template sample "smfGetGuruFocusItem-Full-Template.xls"
> I was able to pull all the IS and BS data I need from GuruFocus, EXCEPT one
> - Minority Interest. This appears between 'NonCurrent Deferred
> Liabilities' and 'Other Long-Term Liabilities' in GuruFocus.
>
> If you see your template, you will notice that you have skipped the fetch
> for Minority Interest. It should have appeared between Line 82 (NonCurrent
> Deferred Liabilities) and Line 83 (Other Long-Term Liabilities).
>
> I thought of adding the definition for Minority Interest myself in one of
> the constant files (smf-elements-?.txt), but could not find the GuruFocus
> constants defined anywhere. Hence I got stuck at this point.
>
> Can you help me fetch this element please?
>
pages as my templates and included what I found there. There's no way I
could know of every line item that GuruFocus would offer. Or this could be
a new line item they added since I wrote the function. Because of the
nature of the extraction, it can't be defined as an RCHGetElementNumber()
extraction. It requires special processing that meant writing a new
function for it.
One of the reasons I wrote the function is because normal extraction is not
straight-forward. It can be done, but there are exceptions here and there.
Something like this general formula works most of the time:
=smfGetTagContent("https://www.gurufocus.com/financials/MMM","
Period"
Where C4 ranges from -16 to -2 for the annuals, -1 for the TTM, and 1 to 5
for the quarterlies.
I notice the smfGetGuruFocusCSVI
line item, so it must have been available when I created that template.
On Mon, Mar 19, 2018 at 6:49 AM, ajtalukdar@
...
wrote:
>
> Working with your template sample "smfGetGuruFoc
> I was able to pull all the IS and BS data I need from GuruFocus, EXCEPT one
> - Minority Interest. This appears between 'NonCurrent Deferred
> Liabilities&#
>
> If you see your template, you will notice that you have skipped the fetch
> for Minority Interest. It should have appeared between Line 82 (NonCurrent
> Deferred Liabilities) and Line 83 (Other Long-Term Liabilities)
>
> I thought of adding the definition for Minority Interest myself in one of
> the constant files (smf-elements-
> constants defined anywhere. Hence I got stuck at this point.
>
> Can you help me fetch this element please?
>
Mon Mar 19, 2018 11:00 am (PDT) . Posted by:
ajtalukdar
Worked like a charm. Thanks for the quick response.
Mon Mar 19, 2018 5:09 pm (PDT) . Posted by:
thealphainvestor
Is Yahoo Earnings Growth busted?
=RCHGetElementNumber("AAPL",621)
is consistently returning Error
Thanks in advance for any advice
- Bruce
=RCHGetElementNumbe
is consistently returning Error
Thanks in advance for any advice
- Bruce
Mon Mar 19, 2018 5:14 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Yah. See:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/topics/34693
I've had a few things I needed to add to the ToDo list because time is
tight these days.
On Mon, Mar 19, 2018 at 5:09 PM, the.alpha.investor@
...
wrote:
>
>
> Is Yahoo Earnings Growth busted?
>
> =RCHGetElementNumber("AAPL",621)
>
> is consistently returning Error
>
> Thanks in advance for any advice
>
>
https://groups.yahoo.com/neo/groups/smf_addin/conversations/topics/34693
I've had a few things I needed to add to the ToDo list because time is
tight these days.
On Mon, Mar 19, 2018 at 5:09 PM, the.alpha.investor@
...
wrote:
>
>
> Is Yahoo Earnings Growth busted?
>
> =RCHGetElementNumbe
>
> is consistently returning Error
>
> Thanks in advance for any advice
>
>
Tue Mar 20, 2018 11:47 am (PDT) . Posted by:
ceounicom
Hi Randy, all
I have been using excel 2010 forever simply because 'it works'; but recently had Office 2016 installed in order to work with some other client docs.
Tried opening my old workbooks in Excel 2016 and just get "#NAME?" where formulas would be.
Do i need to change settings in Excel 2016 to make it play nice with SMF add-in? If this has been previously answered, sorry for the bother, but point me at the thread if you can. Thanks and hope all is well
JG
I have been using excel 2010 forever simply because 'it works'; but recently had Office 2016 installed in order to work with some other client docs.
Tried opening my old workbooks in Excel 2016 and just get "#NAME?
Do i need to change settings in Excel 2016 to make it play nice with SMF add-in? If this has been previously answered, sorry for the bother, but point me at the thread if you can. Thanks and hope all is well
JG
Tue Mar 20, 2018 2:55 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
You'd need to use the add-in manager to tell EXCEL about the add-in.
Otherwise, EXCEL knows nothing about it.
And if any workbooks were saved with the add-in in a different location
than you have on your new machine, you'll get link errors that will need to
be corrected. That can easily be done with the smfFixLinks macro. This
occurs because EXCEL saved a hard-coded location of the add-in with the
functions when a workbook is saved. If the add-in manager has it in a
different location, it tags them as unresolved links.
On Tue, Mar 20, 2018 at 11:45 AM, ceounicom@
...
wrote:
>
> I have been using excel 2010 forever simply because 'it works'; but
> recently had Office 2016 installed in order to work with some other client
> docs.
>
> Tried opening my old workbooks in Excel 2016 and just get "#NAME?" where
> formulas would be.
>
> Do i need to change settings in Excel 2016 to make it play nice with SMF
> add-in? If this has been previously answered, sorry for the bother, but
> point me at the thread if you can. Thanks and hope all is well
>
>
Otherwise, EXCEL knows nothing about it.
And if any workbooks were saved with the add-in in a different location
than you have on your new machine, you'll get link errors that will need to
be corrected. That can easily be done with the smfFixLinks macro. This
occurs because EXCEL saved a hard-coded location of the add-in with the
functions when a workbook is saved. If the add-in manager has it in a
different location, it tags them as unresolved links.
On Tue, Mar 20, 2018 at 11:45 AM, ceounicom@
...
wrote:
>
> I have been using excel 2010 forever simply because 'it works'; but
> recently had Office 2016 installed in order to work with some other client
> docs.
>
> Tried opening my old workbooks in Excel 2016 and just get "#NAME?
> formulas would be.
>
> Do i need to change settings in Excel 2016 to make it play nice with SMF
> add-in? If this has been previously answered, sorry for the bother, but
> point me at the thread if you can. Thanks and hope all is well
>
>
Wed Mar 21, 2018 1:51 pm (PDT) . Posted by:
ceounicom
Hi Randy,
I can open a workbook in 2016, point it to the excel add-in, and formulas seem to work A-ok while i work on that.
if i close that workbook, open something else, close that, then reopen the original again, it changes the active formulae from
e.g.
=RCHgetelementnumber(ticker,function)
to
='C:\SMF Add-In\RCH_Stock_Market_Functions.xla'!RCHgetelementnumber(ticker,function)
basically, it seems like i have to deactivate, then re-activate the add-in every time i open a workbook with live formulae
(every time i reopen, it gives, "Warning: workbook has links to external sources that could be unsafe")
It seems like there's some security setting, or something, that prevents the links/formulas from remaining the same between save and reopen?
i was having this problem before while running 2010 and 2016 simultaneously; i've since uninstalled 2010 and was hoping it would help, but so far not so much.
I can open a workbook in 2016, point it to the excel add-in, and formulas seem to work A-ok while i work on that.
if i close that workbook, open something else, close that, then reopen the original again, it changes the active formulae from
e.g.
=RCHgetelementnumbe
to
='C:\
basically, it seems like i have to deactivate, then re-activate the add-in every time i open a workbook with live formulae
(every time i reopen, it gives, "Warning: workbook has links to external sources that could be unsafe"
It seems like there's some security setting, or something, that prevents the links/formulas from remaining the same between save and reopen?
i was having this problem before while running 2010 and 2016 simultaneously; i've since uninstalled 2010 and was hoping it would help, but so far not so much.
Wed Mar 21, 2018 2:18 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
How are you "pointing it to the EXCEL add-in"? Whatever process you're
doing there may be causing the problem. I haven't found EXCEL's "Edit
Links" process do be reliable in fixing the problem.
If you have fixed the links (properly) and then saved the workbook, opening
it again immediately should NOT result in unresolved links.
The problem is when you have a path on the front of a function like that,
it means the add-in listed in the add-in manager is not in the same
location as it was when the workbook was saved. When EXCEL saves a workbook
it saves a hard-coded location of the add-in. When you open that workbook
and that hard-coded location isn't the current one, it leave it on the
function and flags it as an "unresolved link".
The best way to fix these unresolved links is to run the smfFixLinks macro
in the add-in. Then, when you save the workbook, it will save a hard-coded
link to the current location of the add-in. Next time, you shouldn't get
those unresolved links. Unless you move or deactivate the add-in.
On Wed, Mar 21, 2018 at 1:02 PM, ceounicom@
...
wrote:
>
> I can open a workbook in 2016, point it to the excel add-in, and formulas
> seem to work A-ok while i work on that.
>
> if i close that workbook, open something else, close that, then reopen the
> original again, it changes the active formulae from
>
> e.g.
> =RCHgetelementnumber(ticker,function)
> to
> ='C:\SMF Add-In\RCH_Stock_Market_Functions.xla'!
> RCHgetelementnumber(ticker,function)
>
> basically, it seems like i have to deactivate, then re-activate the add-in
> every time i open a workbook with live formulae
>
> (every time i reopen, it gives, "Warning: workbook has links to external
> sources that could be unsafe")
>
> It seems like there's some security setting, or something, that prevents
> the links/formulas from remaining the same between save and reopen?
>
> i was having this problem before while running 2010 and 2016
> simultaneously; i've since uninstalled 2010 and was hoping it would help,
> but so far not so much.
>
>
doing there may be causing the problem. I haven't found EXCEL's "Edit
Links" process do be reliable in fixing the problem.
If you have fixed the links (properly) and then saved the workbook, opening
it again immediately should NOT result in unresolved links.
The problem is when you have a path on the front of a function like that,
it means the add-in listed in the add-in manager is not in the same
location as it was when the workbook was saved. When EXCEL saves a workbook
it saves a hard-coded location of the add-in. When you open that workbook
and that hard-coded location isn't the current one, it leave it on the
function and flags it as an "unresolved link".
The best way to fix these unresolved links is to run the smfFixLinks macro
in the add-in. Then, when you save the workbook, it will save a hard-coded
link to the current location of the add-in. Next time, you shouldn'
those unresolved links. Unless you move or deactivate the add-in.
On Wed, Mar 21, 2018 at 1:02 PM, ceounicom@
...
wrote:
>
> I can open a workbook in 2016, point it to the excel add-in, and formulas
> seem to work A-ok while i work on that.
>
> if i close that workbook, open something else, close that, then reopen the
> original again, it changes the active formulae from
>
> e.g.
> =RCHgetelementnumbe
> to
> ='C:\
> RCHgetelementnumber
>
> basically, it seems like i have to deactivate, then re-activate the add-in
> every time i open a workbook with live formulae
>
> (every time i reopen, it gives, "Warning: workbook has links to external
> sources that could be unsafe"
>
> It seems like there's some security setting, or something, that prevents
> the links/formulas from remaining the same between save and reopen?
>
> i was having this problem before while running 2010 and 2016
> simultaneously; i've since uninstalled 2010 and was hoping it would help,
> but so far not so much.
>
>
Sat Mar 24, 2018 5:37 am (PDT) . Posted by:
stumpy_chris
I'm back. I have use the above strings and was working well. But now I noticed the fund prices do not update.
Should it not be picking up the current price.
Thanks.
Should it not be picking up the current price.
Thanks.
Sat Mar 24, 2018 10:54 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
You would need to run the smfForceRecalculation to update values.
On Sat, Mar 24, 2018 at 5:37 AM, cschwartz@
...
wrote:
>
> I'm back. I have use the above strings and was working well. But now I
> noticed the fund prices do not update.
>
> Should it not be picking up the current price.
>
>
>
On Sat, Mar 24, 2018 at 5:37 AM, cschwartz@
...
wrote:
>
> I'm back. I have use the above strings and was working well. But now I
> noticed the fund prices do not update.
>
> Should it not be picking up the current price.
>
>
>
Sat Mar 24, 2018 11:37 am (PDT) . Posted by:
stumpy_chris
Thanks how do I use this (and where do I place the string).
Thanks.
Thanks.
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar