MySQL variable in query

See the documentation for $wpdb->prepare(). You need to pass a String, but you’re typing set @csum directly in the parameter. Your code should look like this:

if ( is_user_logged_in() ) {
    $user = wp_get_current_user();

    $balance = $wpdb->query( 
        $wpdb->prepare(
            "SET @csum := (SELECT current_balance FROM exp_ten WHERE tenant_number=%d);
            SELECT tenant_number, transaction_amount, (@csum := @csum + transaction_amount) as narrative
            FROM exp_tran
            ORDER BY tenant_number",
            $user->ID
        )
    );
}

I don’t have any way to test, since I don’t have your database, but that should work.

Also note how I’ve used the second argument of $wpdb->prepare() to pass the user ID. There’s no point using prepare if you’re not going to pass in variables that way.