This is currently rendering the module unusable. I tried extended quotes with just a single symbol - GHL and got this error:

PDOException: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'chg_52_week_low' at row 1: UPDATE {stockapi} SET name=:db_update_placeholder_0, last_trade_price_only=:db_update_placeholder_1, last_trade_date=:db_update_placeholder_2, last_trade_time=:db_update_placeholder_3, chg=:db_update_placeholder_4, pct_chg=:db_update_placeholder_5, volume=:db_update_placeholder_6, avg_daily_volume=:db_update_placeholder_7, bid=:db_update_placeholder_8, ask=:db_update_placeholder_9, previous_close=:db_update_placeholder_10, open=:db_update_placeholder_11, days_range=:db_update_placeholder_12, 52_week_range=:db_update_placeholder_13, eps=:db_update_placeholder_14, pe_ratio=:db_update_placeholder_15, dividend_pay_date=:db_update_placeholder_16, dividend_per_share=:db_update_placeholder_17, dividend_yield=:db_update_placeholder_18, market_cap=:db_update_placeholder_19, exchange=:db_update_placeholder_20, chg_and_pct_chg=:db_update_placeholder_21, short_ratio=:db_update_placeholder_22, one_yr_target_price=:db_update_placeholder_23, eps_est_current_yr=:db_update_placeholder_24, eps_est_next_year=:db_update_placeholder_25, eps_est_next_quarter=:db_update_placeholder_26, price_per_eps_est_current_year=:db_update_placeholder_27, price_per_eps_est_next_year=:db_update_placeholder_28, peg_ratio=:db_update_placeholder_29, book_value=:db_update_placeholder_30, price_per_book=:db_update_placeholder_31, price_per_sales=:db_update_placeholder_32, ebitda=:db_update_placeholder_33, 50_day_moving_average=:db_update_placeholder_34, 200_day_moving_average=:db_update_placeholder_35, last_trade_size=:db_update_placeholder_36, ticker_trend=:db_update_placeholder_37, more_info=:db_update_placeholder_38, trade_links=:db_update_placeholder_39, chg_52_week_low=:db_update_placeholder_40, order_book_rt=:db_update_placeholder_41, after_hours_change_rt=:db_update_placeholder_42, pe_rt=:db_update_placeholder_43, holdings_gain_rt=:db_update_placeholder_44, holdings_gain_pct_rt=:db_update_placeholder_45, days_value_chg_rt=:db_update_placeholder_46, holdings_value_rt=:db_update_placeholder_47, notes=:db_update_placeholder_48, highlimit=:db_update_placeholder_49, lowlimit=:db_update_placeholder_50, annualized_gain=:db_update_placeholder_51, trade_date=:db_update_placeholder_52, holdings_gain=:db_update_placeholder_53, holdings_gain_pct=:db_update_placeholder_54, days_value_change=:db_update_placeholder_55, holdings_value=:db_update_placeholder_56, commission=:db_update_placeholder_57, price_paid=:db_update_placeholder_58, shares_owned=:db_update_placeholder_59, pct_chg_frm_200_day_ma=:db_update_placeholder_60, chg_frm_200_day_ma=:db_update_placeholder_61, pct_chg_frm_50_day_ma=:db_update_placeholder_62, chg_frm_50_day_ma=:db_update_placeholder_63, ex_dividend_date=:db_update_placeholder_64, pct_chg_from_52_wk_high=:db_update_placeholder_65, chg_from_52_wk_high=:db_update_placeholder_66, pct_chg_from_52_wk_low=:db_update_placeholder_67, updated=:db_update_placeholder_68 WHERE (symbol = :db_condition_placeholder_0) ; Array ( [:db_update_placeholder_0] => Apple Inc. [:db_update_placeholder_1] => 426.31 [:db_update_placeholder_2] => 7/22/2013 [:db_update_placeholder_3] => 4:00pm [:db_update_placeholder_4] => +1.36 [:db_update_placeholder_5] => +0.32% [:db_update_placeholder_6] => 7417138 [:db_update_placeholder_7] => 14211800 [:db_update_placeholder_8] => 425.70 [:db_update_placeholder_9] => 425.80 [:db_update_placeholder_10] => 424.95 [:db_update_placeholder_11] => 429.55 [:db_update_placeholder_12] => 425.468 - 429.75 [:db_update_placeholder_13] => 385.10 - 705.07 [:db_update_placeholder_14] => 41.896 [:db_update_placeholder_15] => 10.14 [:db_update_placeholder_16] => May 16 [:db_update_placeholder_17] => 11.00 [:db_update_placeholder_18] => 2.59 [:db_update_placeholder_19] => 0 [:db_update_placeholder_20] => NasdaqNM [:db_update_placeholder_21] => +1.36 - +0.32% [:db_update_placeholder_22] => 2.20 [:db_update_placeholder_23] => 538.44 [:db_update_placeholder_24] => 39.41 [:db_update_placeholder_25] => 43.34 [:db_update_placeholder_26] => 8.08 [:db_update_placeholder_27] => 10.78 [:db_update_placeholder_28] => 9.81 [:db_update_placeholder_29] => 0.52 [:db_update_placeholder_30] => 144.124 [:db_update_placeholder_31] => 2.95 [:db_update_placeholder_32] => 2.36 [:db_update_placeholder_33] => 57.381B [:db_update_placeholder_34] => 425.229 [:db_update_placeholder_35] => 445.366 [:db_update_placeholder_36] => 872 [:db_update_placeholder_37] => 997 [:db_update_placeholder_38] => &nbsp;++-+==&nbsp; [:db_update_placeholder_39] => cnsprmiIed [:db_update_placeholder_40] => <a href=http://edit.finance.yahoo.com/ef?.intl=us&.done=http://finance.yahoo.com/d/quotes.csvr%3fs%3dGHL%20AAPL%20INTC%20IBM%20HPQ%20GOOG%20MSFT%20GE%20DIS%20DELL%20AMZN%26f%3dsnl1d1t1c1p2va2bapomwerr1dyj1xcs7t8e7e8e9r6r7r5b4p6p5j4m3m4k3t7it6j5i5c8r2g6g5w4v7n4l2l3g3d2g4g1w1v1c3p1s1m6m5m8m7qk5k4j6%26e%3d.csv">Choose&nbsp;Brokerage</a> [:db_update_placeholder_41] => +41.21 [:db_update_placeholder_42] => N/A [:db_update_placeholder_43] => N/A - N/A [:db_update_placeholder_44] => N/A [:db_update_placeholder_45] => N/A [:db_update_placeholder_46] => N/A - N/A [:db_update_placeholder_47] => N/A - N/A [:db_update_placeholder_48] => N/A [:db_update_placeholder_49] => - [:db_update_placeholder_50] => - [:db_update_placeholder_51] => - [:db_update_placeholder_52] => - [:db_update_placeholder_53] => - [:db_update_placeholder_54] => - [:db_update_placeholder_55] => - - - [:db_update_placeholder_56] => - - +0.32% [:db_update_placeholder_57] => - [:db_update_placeholder_58] => 0 [:db_update_placeholder_59] => - [:db_update_placeholder_60] => - [:db_update_placeholder_61] => -4.28% [:db_update_placeholder_62] => -19.056 [:db_update_placeholder_63] => +0.25% [:db_update_placeholder_64] => +1.081 [:db_update_placeholder_65] => May 9 [:db_update_placeholder_66] => -39.54% [:db_update_placeholder_67] => -278.76 [:db_update_placeholder_68] => 1374529739 [:db_condition_placeholder_0] => AAPL ) in stockapi_save() (line 68 of /srv/bindings/1b7a6ee44a4e440694ca9f424e75a47d/code/sites/all/modules/stockapi/stockapi.inc).

As you can see it's trying to drop a whole link tag into that field:

[:db_update_placeholder_40] => <a href=http://edit.finance.yahoo.com/ef?.intl=us&.done=http://finance.yahoo.com/d/quotes.csvr%3fs%3dGHL%20AAPL%20INTC%20IBM%20HPQ%20GOOG%20MSFT%20GE%20DIS%20DELL%20AMZN%26f%3dsnl1d1t1c1p2va2bapomwerr1dyj1xcs7t8e7e8e9r6r7r5b4p6p5j4m3m4k3t7it6j5i5c8r2g6g5w4v7n4l2l3g3d2g4g1w1v1c3p1s1m6m5m8m7qk5k4j6%26e%3d.csv">Choose&nbsp;Brokerage</a>

Looks like the variables are going into the wrong fields, that should have been the trade links field right before chg_52_week_low.

Comments

andy.alexander’s picture

andy.alexander commented

This appears to be because some of the fields have commas and aren't being properly enclosed in quotes.

The issue is inside stockapi_fetch(), trying to figure out how to fix it.

andy.alexander’s picture

andy.alexander commented

The solution, as far as I can tell, is converting the query from the CSV API to the YQL API, but I'm not familiar with the YQL API

EDIT: This is not correct. More information in comment below.

andy.alexander’s picture

andy.alexander commented

This is due to a parsing error.

Some fields have values in the thousands, say, for a given example, 3,600, only this value is unquoted.

This breaks the parsing, because it doesn't expect to be separated there.

As a temporary measure, testing that the correct amount of values is in a particular stock array is a possible solution.

Here's a theoretical way to do it, inside _stockapi_parse_csv():


  $quote_length = drupal_strlen(stockapi_get_quotetype(variable_get('stockapi_quotetype', 'basic'))) / 2;

  // Clean up the data.
  foreach ($lines as $key => $stock) {
    // Checks whether the number of keys in the quote type matches the result, otherwise passes a warning and removes the stock from being saved
        if(count($stock) != $quote_length) {
      watchdog("Stock API", "Error parsing stock: " . $stock[0]);
      unset($lines[$key]);
    }

and to ensure the correct value is calculated for the $quote_length variable, this change to stockapi_get_quotetype():

  $quotetype['basic'] = 's0n0l1d1t1c1p2v0a2b0a0p0o0m0w0e0r0r1d0y0j1x0';
  $quotetype['extended'] = $quotetype['basic'] . 'c0s7t8e7e8e9r6r7r5b4p6p5j4m3m4k3t7i0t6j5i5c8r2g6g5w4v7n4l2l3g3d2g4g1w1v1c3p1s1m6m5m8m7q0k5k4j6';
  $quotetype['realtime'] = $quotetype['basic'] . 'b2b3k2k1c6m2j3';

Doing this requires changing stockapi.install for the new quotetypes (with the 0s included in the query)

andy.alexander’s picture

andy.alexander commented

I'm also noticing that it appears to be off by 1 (I'm trying to figure out why), so in the mean-time, it may be best to have the conditional be the following:

if(count($stock) != $quote_length + 1) {
andy.alexander’s picture

andy.alexander commented

Scrap my old changes to _stockapi_parse_csv, they don't seem to work nice with the changes that were made in the new dev version.

Here's a better warning system:

$lines = array();
  $pre_lines = _stockapi_str_getcsv($data);
  // Clean up the data.
  $quote_length = (drupal_strlen(stockapi_get_quotetype(variable_get('stockapi_quotetype', 'basic'))) / 2);
  foreach($pre_lines as $key => $stock) {
        // Checks whether the number of keys in the quote type matches the result, otherwise passes a warning
        if(count($stock) == $quote_length) {
$lines[$key] = $stock;
    }
    else {
            watchdog("Stock API", "Error parsing stock: " . $stock[0]);
    }
  }

I'm still getting a lot of error messages with this, but now it just doesn't parse any stocks that cause issues - this will prevent hanging over a given field.

We need to figure out a better solution for the parsing problem though.

One solution would be to redirect aberrant stock values to another function, where we could look for common exceptions - what fields are frequently going to have a comma, for example.

dakala’s picture

dakala
he/him
Primary language English
Location Fairford, Glos
commented

At the moment the code in the dev version parses the CSV with commas correctly. See the output in response to your post here: https://www.drupal.org/node/1902212#comment-9342791

If you let me know the symbols and quote types you're having the comma issues with, I can give it a go and find a better way to do the parsing.

dakala’s picture

dakala
he/him
Primary language English
Location Fairford, Glos
commented

With the code in dev, here's the output for the extended type:

Array
(
    [0] => Array
        (
            [0] => ZIOP
            [1] => ZIOPHARM Oncology
            [2] => 3.73
            [3] => 11/14/2014
            [4] => 4:00pm
            [5] => +0.44
            [6] => +13.37%
            [7] => 1798996
            [8] => 875558
            [9] => 3.49
            [10] => N/A
            [11] => 3.29
            [12] => 3.38
            [13] => 3.32 - 3.88
            [14] => 2.31 - 5.58
            [15] => -0.306
            [16] => N/A
            [17] => N/A
            [18] => 0.00
            [19] => N/A
            [20] => 382.0M
            [21] => NCM
            [22] => 17.20
            [23] => 7.00
            [24] => -0.39
            [25] => -0.45
            [26] => -0.12
            [27] => N/A
            [28] => N/A
            [29] => N/A
            [30] => 0.356
            [31] => 9.24
            [32] => 273.23
            [33] => -36.7M
            [34] => 2.8792
            [35] => 3.2842
        )

    [1] => Array
        (
            [0] => ZIPR
            [1] => ZipRealty, Inc.
            [2] => 6.74
            [3] => 8/14/2014
            [4] => 3:00pm
            [5] => 0.00
            [6] => 0.00%
            [7] => 0
            [8] => 170153
            [9] => N/A
            [10] => N/A
            [11] => 6.74
            [12] => N/A
            [13] => N/A - N/A
            [14] => 2.81 - 7.07
            [15] => -0.39
            [16] => N/A
            [17] => N/A
            [18] => 0.00
            [19] => N/A
            [20] => 147.6M
            [21] => NGM
            [22] => 9.20
            [23] => 6.75
            [24] => 0.00
            [25] => 0.00
            [26] => 0.00
            [27] => N/A
            [28] => N/A
            [29] => N/A
            [30] => 0.392
            [31] => 17.19
            [32] => 2.12
            [33] => -5.9M
            [34] => 5.3494
            [35] => 4.06
        )

    [2] => Array
        (
            [0] => ZIV
            [1] => Credit Suisse AG
            [2] => 45.21
            [3] => 11/14/2014
            [4] => 4:00pm
            [5] => +0.47
            [6] => +1.05%
            [7] => 53220
            [8] => 121600
            [9] => N/A
            [10] => 51.98
            [11] => 44.74
            [12] => 44.70
            [13] => 44.4201 - 45.27
            [14] => 33.74 - 49.20
            [15] => 0.00
            [16] => N/A
            [17] => N/A
            [18] => 0.00
            [19] => N/A
            [20] => N/A
            [21] => PCX
            [22] => N/A
            [23] => N/A
            [24] => 0.00
            [25] => 0.00
            [26] => 0.00
            [27] => N/A
            [28] => N/A
            [29] => N/A
            [30] => 0.00
            [31] => N/A
            [32] => N/A
            [33] => 0
            [34] => 42.9189
            [35] => 44.6989
        )

)

Each array has 36 elements. I can't see any formatted integers with thousand marks anywhere.

andy.alexander’s picture

andy.alexander commented

I'm getting 68 keys with the extended?

And I'm using a lot of stocks - it's only on certain stocks that these values appear. I can try to find one for you later.

joseph.olstad’s picture

joseph.olstad
Primary language French
commented
Status: Active » Closed (outdated)

Upgrade to 7.x-2.x