DbTradeAlert for Android: Schedule Quote Updates – Part 3

First post in this series: Introduction to DbTradeAlert

Previous post: DbTradeAlert for Android: Schedule Quote Updates – Part 2

2. Provide Signals so They Reach a User Even With His Phone in the Pocket

Currently signals only show up in the respective security’s reports. To actually alert the user DDbTradeAlert will use Android’s notification infrastructure like for example mail apps do.

Again this can be broken down to separate steps:

  1. Create the SQL to find triggered signals
  2. Build and send notifications

2.1. Create the SQL to Find Triggered Signals

Finding the triggered signals actually needs two queries: one to find the securities’ max prices and one to find the triggered signals. DbHelper’s updateSecurityMaxPrice() generates this SQL:

SELECT s._id, max_price, max_price_date, s.symbol, days_high, SUBSTR(last_price_date_time, 0, 11)
 FROM security s
	LEFT JOIN quote q ON q.security_id = s._id
 WHERE COALESCE(max_price, 0) < COALESCE(days_high, 0)
	AND COALESCE(max_price_date, '') < SUBSTR(last_price_date_time, 0, 11)

This finds all securities where Quote.DAYS_HIGH is greater than Security.MAX_PRICE. As a sanity check Security.MAX_PRICE_DATE must also be before Quote.LAST_PRICE_DATE_TIME.

SUBSTR(last_price_date_time, 0, 11) returns the date part from last_price_date_time. COALESCE(max_price, 0) returns max_price if that isn’t NULL and otherwise 0 – remember that the DB may contain NULL for any field except LAST_PRICE and _ID.

If any securities to update are found their MAX_PRICE and MAX_PRICE_DATE fields are updated by using Security.ID as a key and of course in a transaction. This has to be done in a separate step because SQLite doesn’t support JOINs in UPDATEs.

The SQL used by DbHelper.readAllTriggeredSignals() to find triggered signals is a bit longer:

/* Lower target signal */ 
 SELECT 'low' AS actual_name, days_low AS actual_value, 'L' AS signal_name, lower_target AS target_value, s.symbol
 FROM security s
	LEFT JOIN quote q ON q.security_id = s._id
 WHERE (last_price_date_time >= date('now','-1 day'))
	AND (
		lower_target IS NOT NULL
		AND days_low IS NOT NULL
		AND days_low <= lower_target ) 
 UNION ALL /* Upper target signal */ 
 SELECT 'high' AS actual_name, days_high AS actual_value, 'U' AS signal_name, upper_target AS target_value, s.symbol 
 FROM security s
 	LEFT JOIN quote q ON q.security_id = s._id 
 WHERE (last_price_date_time >= date('now','-1 day'))
	AND (
		upper_target IS NOT NULL
		AND days_high IS NOT NULL
		AND days_high >= upper_target
 UNION ALL /* Trailing stop loss signal */
 SELECT 'low' AS actual_name, days_low AS actual_value, 'T' AS signal_name, max_price * (100 - trailing_target) / 100 AS target_value, s.symbol
 FROM security s
	LEFT JOIN quote q ON q.security_id = s._id
 WHERE (last_price_date_time >= date('now','-1 day'))
	AND (
		trailing_target IS NOT NULL
		AND days_low IS NOT NULL
		AND days_low <= max_price * (100 - trailing_target) / 100
 ORDER BY s.symbol ASC

This consists of a UNION of 3 queries, each finding a type (lower, trailing, upper) of triggered signal (you’d only have to provide field names in the 1st query). Each query first checks if the quotes are up to date, then if the respective values are not NULL, and finally if the target was hit.

2.2. Build and Send Notifications

Notifications require an icon so you’ll need to add one before building them. Android Studio doesn’t provide anything suitable this time but the SDK does at:

Like the Refresh button’s icon this one has to be usable on various devices so you need to copy “emo_im_money_mouth.png” four times to the respective subfolder in your project. In my case they are below:

And the source folders are:

  • drawable-hdpi
  • drawable-mdpi
  • drawable-xhdpi
  • drawable-xxhdpi

Android Studio will pick the new icons up immediately – awesome!

After that sendNotificationForTriggeredSignals() can build and send the notifications:

public class QuoteRefresherService extends IntentService {
    // ...

    private String buildNotificationLineFromCursor(Cursor cursor) {
        String result = null;
        String actualName = cursor.getString(0);
        float actualValue = cursor.getFloat(1);
        String signalName = cursor.getString(2);
        float signalValue = cursor.getFloat(3);
        String symbol = cursor.getString(4);
        result = String.format(Locale.getDefault(),
                "%s(): %s = %01.2f; %s = %01.2f", symbol, actualName,
                actualValue, signalName, signalValue);
        return result;
    } // buildNotificationLineFromCursor()

    protected void onHandleIntent(Intent intent) {
        String baseUrl = "http://download.finance.yahoo.com/d/quotes.csv";
        String url = baseUrl
                + "?f=" + DbHelper.QuoteDownloadFormatParameter
                + "&s=" + getSymbolParameterValue();
        String quoteCsv = "";
        try {
            boolean isManualRefresh = intent.getBooleanExtra(INTENT_EXTRA_IS_MANUAL_REFRESH, false);
            if (isManualRefresh || areExchangesOpenNow()) {
                if (isConnected()) {
                        quoteCsv = downloadQuotes(url);
                        DbHelper dbHelper = new DbHelper(this);
                        // Notify user of triggered signals even if app is sleeping
                } else {
                    sendLocalBroadcast(BROADCAST_EXTRA_ERROR + "no Internet!");
                    Log.d(CLASS_NAME, BROADCAST_EXTRA_ERROR + "no Internet!");
                // ...
            } else {
                    "onHandleIntent(): exchanges closed and not a manual reefresh - skipping alarm");
        } catch (IOException e) {
              // ...
            Log.e(CLASS_NAME, exceptionMessage, e);
    } // onHandleIntent()

    // ...

    private void sendNotificationForTriggeredSignals(DbHelper dbHelper) {
        final String methodName = "sendNotificationForTriggeredSignals";
        Cursor cursor = dbHelper.readAllTriggeredSignals();
        if (cursor.getCount() > 0) {
            Context context = getApplicationContext();
            NotificationCompat.Builder builder = new NotificationCompat.Builder(context)
            // Specify which intent to show when user taps notification
            Intent watchlistListIntent = new Intent(this, WatchlistListActivity.class);
            PendingIntent watchlistListPendingIntent
                    = PendingIntent.getActivity(context, 0, watchlistListIntent, 0);
            // Build back stack
            TaskStackBuilder stackBuilder = TaskStackBuilder.create(this);
            // Create notification
            if (cursor.getCount() == 1) {
                String s = buildNotificationLineFromCursor(cursor);
                builder.setContentTitle("Target hit").setContentText(s);
                Log.v(CLASS_NAME, String.format("%s(): Notification = %s", methodName, s));
            } else {
                builder.setContentTitle(cursor.getCount() + " Targets hit");
                NotificationCompat.InboxStyle inboxStyle = new NotificationCompat.InboxStyle();
                while (cursor.moveToNext()) {
                    String s = buildNotificationLineFromCursor(cursor);
                    Log.v(CLASS_NAME, String.format("%s(): Notification = %s", methodName, s));
            // Show notification
            NotificationManager notificationManager = (NotificationManager) context
            // Show new notification or update pending one
            final int notificationId = 1234;
            notificationManager.notify(notificationId, builder.build());
                String.format("%s(): created %d notifications", methodName, cursor.getCount()));
    } // sendNotificationForTriggeredSignals()

After reading triggered signals the code first sets up a NotificationCompat.Builder object specifying the newly added icon with a circle of Win color behind it. Notification.DEFAULT_ALL will cause the device to use default sound, vibration and notification lights. And the amount of notifications will be visible separately.

The code then sets the intent to show when a user taps the notification with setContentIntent(). And it creates the backstack is so the device’s Back button works as expected.

If only one signal was triggered a single line of text created by buildNotificationLineFromCursor() and the title complete the notification.

If multiple signals were triggered the builder’s style will be NotificationCompat.InboxStyle which allows for multiple lines of text. Again buildNotificationLineFromCursor() creates those lines.

Finally a NotificationManager instance fires off the notification. Note that notificationId can be any number but should be unique. It’s used to update a notification with new content.

Everything happens in QuoteRefresherService.onHandleIntent() to inform the user even if his device is asleep.

The screenshots show the initial icon in the top left corner visualizing the notification – the phone will vibrate and sound depending on settings too – and the expanded notification. You see Visual Studio’s emulator because on a real device the USB related notifications would obscure DbTradeAlert.

Notification icon top left

Notification icon top left

Opened notification

Opened notification

The notification shows a trailing stop loss triggered for NOVN.VX. The trailing target is at 92.07 CHF (sample data: 90 % of the stock’s maximum price of 102.30 CHF) and the day’s low was at 79.55 CHF. It’s visible in the watchlist’s report as well (“T” and red background color in signal TextView).

Android Marshmallow added a twist to notifications: users can turn them off. They can do that for each app in Settings | Sound & notification | App notifications and turn off its notifications, prevent notifications from peeking – which DbTradeAlert doesn’t use – or even allow them in “Do not disturb” mode. Each individual app’s Settings screen also got a Notifications item for configuration.

To check if an app is blocked from sending notifications use NotificationManagerCompat.areNotificationsEnabled() and NotificationManagerCompat.getImportance() let’s you know whether your app’s notifications are priorized. Android provides no way to nag the user for enabling notifications or change their priority because the user has already decided to block explicitly this app’s notifications.

If the device uses a pin or pattern lock there is a fourth setting: hide sensitive notification content. In that case you should create different types of notifications by calling NotificationCompat.Builder.setVisibility() with one of the visibility types like NotificationCompat.VISIBILITY_SECRET. DbTradeAlert doesn’t give away any secrets so I didn’t bother with different visibility types.

Next post: Add Security and Watchlist Management

Additional Resources

This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s