9 Messages
Digest #3921
Messages
Wed Feb 15, 2017 5:14 am (PST) . Posted by:
szazdrew
Hi, Randy!
I am using SMF for html parsing and it works great! But a lot of providers store data in csv files.
For example, local files with historical prices (D,O,H,L,C,V). If I need to extract the only close price for exact date, first I need to convert csv to html then parse it with smfGetTagContent or RCHGetTableCell and get a result at one cell at the apreadsheet.
Maybe there is more efficient way?
Or You can add some new functionality to smfGetCSV? Something like in smfGetTagContent with ability to find the string, skip user defined number of separators and extract the exact data. It would be very useful!
I am using SMF for html parsing and it works great! But a lot of providers store data in csv files.
For example, local files with historical prices (D,O,H,L,C,V)
Maybe there is more efficient way?
Or You can add some new functionality to smfGetCSV? Something like in smfGetTagContent with ability to find the string, skip user defined number of separators and extract the exact data. It would be very useful!
Wed Feb 15, 2017 6:53 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Actually, you can do that right now. For example, suppose I want to get the
investment style for Microsoft from the "Holdings" CSV file from
MorningStar:
=INDEX(smfGetCSVFile("
http://portfolios.morningstar.com/fund/holdingsExport?t=SPY&exportType=details
",,500,100),MATCH("MSFT",INDEX(smfGetCSVFile("
http://portfolios.morningstar.com/fund/holdingsExport?t=SPY&exportType=details
",,500,100),,4),0),5)
What does it do?
1. The smfGetCSVFile() function is the same in both cases. The "500,100"
parameters are not normally used, as they default to the range the formula
is entered over. They're needed in this case because the data exists
outside of a workbook range. In any case, that formula is creating a 500
row by 100 column array that the function will fill.
2. The INDEX(...,,4) says to use only the 4th column of the returned
smfGetCSVFile() array.
3. The MATCH("MSFT",...,0) says to find an exact match of "MSFT" in that
fourth column of the returned smfGetCSVFile() array. This tells you what
row "MSFT" in on.
4. The INDEX(...,...,5) says to extract the 5th column of that matched
row.
The bad thing about this technique is it has to retrieve the CSV file from
the Internet twice. So it would NOT be a good way to grab a number of
items. For that, you'd be better off placing the CSV file in a range and
then using EXCEL lookup formulas on the result.
On Wed, Feb 15, 2017 at 6:14 AM, szazdrew@
...wrote:
>
> I am using SMF for html parsing and it works great! But a lot of providers
> store data in csv files.
>
> For example, local files with historical prices (D,O,H,L,C,V). If I need
> to extract the only close price for exact date, first I need to convert csv
> to html then parse it with smfGetTagContent or RCHGetTableCell and get a
> result at one cell at the apreadsheet.
>
> Maybe there is more efficient way?
>
> Or You can add some new functionality to smfGetCSV? Something like in
> smfGetTagContent with ability to find the string, skip user defined number
> of separators and extract the exact data. It would be very useful!
>
investment style for Microsoft from the "Holdings" CSV file from
MorningStar:
=INDEX(smfGetCSVFile("
http://portfolios.morningstar.com/fund/holdingsExport?t=SPY&exportType=details
",,500,100),MATCH("MSFT",INDEX(smfGetCSVFile("
http://portfolios.morningstar.com/fund/holdingsExport?t=SPY&exportType=details
",,500,
What does it do?
1. The smfGetCSVFile(
parameters are not normally used, as they default to the range the formula
is entered over. They're needed in this case because the data exists
outside of a workbook range. In any case, that formula is creating a 500
row by 100 column array that the function will fill.
2. The INDEX(...,,4) says to use only the 4th column of the returned
smfGetCSVFile(
3. The MATCH("
fourth column of the returned smfGetCSVFile(
row "MSFT" in on.
4. The INDEX(...,..
row.
The bad thing about this technique is it has to retrieve the CSV file from
the Internet twice. So it would NOT be a good way to grab a number of
items. For that, you'd be better off placing the CSV file in a range and
then using EXCEL lookup formulas on the result.
On Wed, Feb 15, 2017 at 6:14 AM, szazdrew@
...wrote:
>
> I am using SMF for html parsing and it works great! But a lot of providers
> store data in csv files.
>
> For example, local files with historical prices (D,O,H,L,C,V)
> to extract the only close price for exact date, first I need to convert csv
> to html then parse it with smfGetTagContent or RCHGetTableCell and get a
> result at one cell at the apreadsheet.
>
> Maybe there is more efficient way?
>
> Or You can add some new functionality to smfGetCSV? Something like in
> smfGetTagContent with ability to find the string, skip user defined number
> of separators and extract the exact data. It would be very useful!
>
Wed Feb 15, 2017 4:26 pm (PST) . Posted by:
szazdrew
Randy, it's great! Works well with my local files and will save me a lot of time.
Thank You for solving this case and for all your work! It's priceless!
Thank You for solving this case and for all your work! It's priceless!
Wed Feb 15, 2017 5:16 am (PST) . Posted by:
szazdrew
Hi, Randy!
I am using SMF for html parsing and it works great! But a lot of providers store data in csv files.
For example, local files with historical prices (D,O,H,L,C,V). If I need to extract the only close price for exact date, first I need to convert csv to html then parse it with smfGetTagContent or RCHGetTableCell and get a result at one cell at the spreadsheet.
Maybe there is more efficient way?
Or You can add some new functionality to smfGetCSV? Something like in smfGetTagContent with ability to find the string, skip user defined number of separators and extract the exact data. It would be very useful!
I am using SMF for html parsing and it works great! But a lot of providers store data in csv files.
For example, local files with historical prices (D,O,H,L,C,V)
Maybe there is more efficient way?
Or You can add some new functionality to smfGetCSV? Something like in smfGetTagContent with ability to find the string, skip user defined number of separators and extract the exact data. It would be very useful!
Wed Feb 15, 2017 9:10 am (PST) . Posted by:
g3m1n1980
Randy,
Im still showing an error after loading the new SMF update. =smfGetGuruFocusItem.
The only non errors for GF is TTM. Wondering if its just not available anymore?
=smfGetGuruFocusItem(L$3,136,"TTM")*1000 (works)
=smfGetGuruFocusItem(L$3,6,"Q",0) (doesnt work)
Im also getting errors on the past 5 financials; basically anything quarterly.
Any idea?
Im still showing an error after loading the new SMF update. =smfGetGuruFocusIte
The only non errors for GF is TTM. Wondering if its just not available anymore?
=smfGetGuruFocusIte
=smfGetGuruFocusIte
Im also getting errors on the past 5 financials; basically anything quarterly.
Any idea?
Wed Feb 15, 2017 12:06 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
I'm getting all "Error" results for GF as well -- it's because GuruFocus
has no financials for it. They've almost got nothing there:
http://www.gurufocus.com/stock/gf
I'm surprised you got anything -- unless they are, or were, updating the
web page
Having said that, it appears there are issues -- most of my MMM data
returned has the same values for various line items. I see they've changed
some of the class values I was keying off of (e.g. changed "normal" to
"normal_pershare)".
On Wed, Feb 15, 2017 at 10:10 AM, xxjaysonxx@
...wrote:
>
> Im still showing an error after loading the new SMF update.
> =smfGetGuruFocusItem.
>
> The only non errors for GF is TTM. Wondering if its just not available
> anymore?
>
> =smfGetGuruFocusItem(L$3,136,"TTM")*1000 (works)
>
> =smfGetGuruFocusItem(L$3,6,"Q",0) (doesnt work)
>
> Im also getting errors on the past 5 financials; basically anything
> quarterly.
>
> Any idea?
>
>
has no financials for it. They've almost got nothing there:
http://www.gurufocus.com/stock/gf
I'm surprised you got anything -- unless they are, or were, updating the
web page
Having said that, it appears there are issues -- most of my MMM data
returned has the same values for various line items. I see they've changed
some of the class values I was keying off of (e.g. changed "normal" to
"normal_
On Wed, Feb 15, 2017 at 10:10 AM, xxjaysonxx@
...wrote:
>
> Im still showing an error after loading the new SMF update.
> =smfGetGuruFocusIte
>
> The only non errors for GF is TTM. Wondering if its just not available
> anymore?
>
> =smfGetGuruFocusIte
>
> =smfGetGuruFocusIte
>
> Im also getting errors on the past 5 financials; basically anything
> quarterly.
>
> Any idea?
>
>
Wed Feb 15, 2017 12:11 pm (PST) . Posted by:
"Jason Strauss" g3m1n1980
Randy,
GF was just short for gurufocus. I can't get any per share returns for any tickers.
Is it just me?
Sent from my iPhone
> On Feb 15, 2017, at 3:06 PM, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
>
> I'm getting all "Error" results for GF as well -- it's because GuruFocus has no financials for it. They've almost got nothing there:
>
> http://www.gurufocus.com/stock/gf
>
> I'm surprised you got anything -- unless they are, or were, updating the web page
>
> Having said that, it appears there are issues -- most of my MMM data returned has the same values for various line items. I see they've changed some of the class values I was keying off of (e.g. changed "normal" to "normal_pershare)".
>
> On Wed, Feb 15, 2017 at 10:10 AM, xxjaysonxx@
> ...wrote:
>>
>> Im still showing an error after loading the new SMF update. =smfGetGuruFocusItem.
>>
>> The only non errors for GF is TTM. Wondering if its just not available anymore?
>>
>> =smfGetGuruFocusItem(L$3,136,"TTM")*1000 (works)
>>
>> =smfGetGuruFocusItem(L$3,6,"Q",0) (doesnt work)
>>
>> Im also getting errors on the past 5 financials; basically anything quarterly.
>>
>> Any idea?
>>
>
>
GF was just short for gurufocus. I can't get any per share returns for any tickers.
Is it just me?
Sent from my iPhone
> On Feb 15, 2017, at 3:06 PM, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
>
> I'm getting all "Error" results for GF as well -- it's because GuruFocus has no financials for it. They've almost got nothing there:
>
> http://www.gurufocus.com/stock/gf
>
> I'm surprised you got anything -- unless they are, or were, updating the web page
>
> Having said that, it appears there are issues -- most of my MMM data returned has the same values for various line items. I see they've changed some of the class values I was keying off of (e.g. changed "normal" to "normal_
>
> On Wed, Feb 15, 2017 at 10:10 AM, xxjaysonxx@
> ...wrote:
>>
>> Im still showing an error after loading the new SMF update. =smfGetGuruFocusIte
>>
>> The only non errors for GF is TTM. Wondering if its just not available anymore?
>>
>> =smfGetGuruFocusIte
>>
>> =smfGetGuruFocusIte
>>
>> Im also getting errors on the past 5 financials; basically anything quarterly.
>>
>> Any idea?
>>
>
>
Wed Feb 15, 2017 12:46 pm (PST) . Posted by:
"Jim Ranum" amt2100
I just uninstalled it. It's very glitchy and bogs down Excel. Frequent updates stopping you from starting Excel. And when you're trying to pull in numbers on new cells, it's just plain slow.
And even with a paid subscription, you have to login after you open Excel every time.
No competition to SMF!
Best,
Jim
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Wednesday, February 15, 2017 3:06 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Re: smfgetgurufocusitem
I'm getting all "Error" results for GF as well -- it's because GuruFocus has no financials for it. They've almost got nothing there:
http://www.gurufocus.com/stock/gf
I'm surprised you got anything -- unless they are, or were, updating the web page
Having said that, it appears there are issues -- most of my MMM data returned has the same values for various line items. I see they've changed some of the class values I was keying off of (e.g. changed "normal" to "normal_pershare)".
On Wed, Feb 15, 2017 at 10:10 AM, xxjaysonxx@
...wrote:
Im still showing an error after loading the new SMF update. =smfGetGuruFocusItem.
The only non errors for GF is TTM. Wondering if its just not available anymore?
=smfGetGuruFocusItem(L$3,136,"TTM")*1000 (works)
=smfGetGuruFocusItem(L$3,6,"Q",0) (doesnt work)
Im also getting errors on the past 5 financials; basically anything quarterly.
Any idea?
And even with a paid subscription, you have to login after you open Excel every time.
No competition to SMF!
Best,
Jim
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Wednesday, February 15, 2017 3:06 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Re: smfgetgurufocusitem
I'm getting all "Error" results for GF as well -- it's because GuruFocus has no financials for it. They've almost got nothing there:
http://www.gurufocus.com/stock/gf
I'm surprised you got anything -- unless they are, or were, updating the web page
Having said that, it appears there are issues -- most of my MMM data returned has the same values for various line items. I see they've changed some of the class values I was keying off of (e.g. changed "normal" to "normal_
On Wed, Feb 15, 2017 at 10:10 AM, xxjaysonxx@
...wrote:
Im still showing an error after loading the new SMF update. =smfGetGuruFocusIte
The only non errors for GF is TTM. Wondering if its just not available anymore?
=smfGetGuruFocusIte
=smfGetGuruFocusIte
Im also getting errors on the past 5 financials; basically anything quarterly.
Any idea?
Wed Feb 15, 2017 2:32 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
They changed the class on all the "per share" and "ratio" items, so the
extraction was going to the next "normal" item.
I've upload the changes to the main page:
RCH_Stock_Market_Functions-2.1.2017.02.15.zip
http://ogres-crypt.com/SMF/
On Wed, Feb 15, 2017 at 1:11 PM, Jason Strauss xxjaysonxx@
...wrote:
>
> GF was just short for gurufocus. I can't get any per share returns for
> any tickers.
>
> Is it just me?
>
extraction was going to the next "normal" item.
I've upload the changes to the main page:
RCH_Stock_Market_Functions-2.1.2017.02.15.zip
http://ogres-crypt.com/SMF/
On Wed, Feb 15, 2017 at 1:11 PM, Jason Strauss xxjaysonxx@
...wrote:
>
> GF was just short for gurufocus. I can't get any per share returns for
> any tickers.
>
> Is it just me?
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar