Greetings, first time poster here.
My app is related to the Stock Market. Users can search for one or more stocks using the input search. Data will dynamically appear in both graph and table format, using a @callback. If a user searches for a symbol, the corresponding table/visual data will appear, thanks to function returns. Same user can search for another symbol in my “multi=True” input search. New data will be called and appended to my dashboard objects for each new value in my @callback, in this case new values pertain to a companies Symbol, or ticker, in the Stock Market.
Works good until the bottom table function. I think its an error with how I’m calling the data from my dB??? Not to sure anymore, I’ve tried so many methods and ways where I feel stuck in the mud. Could I get some Dash wizards, guru’s, or ninjas to help me out on this one? Take a look at my code here:
import dash
import dash_table as dt
import dash_bootstrap_components as dbc
import dash_core_components as dcc
import dash_html_components as html
from dash.dependencies import Input, Output
import plotly.express as px
import plotly.graph_objs as go
from dash.exceptions import PreventUpdate
import pandas as pd
from connect import *
from dash_tables import *
def postgresql_to_dataframe_v1(conn, select_query, param, column_names):
"""
Tranform a SELECT query into a pandas dataframe
"""
cursor = conn.cursor()
try:
cursor.execute(select_query, param) # ADJUSTED LINE
except (Exception, psycopg2.DatabaseError) as error:
print("Error: %s" % error)
cursor.close()
return 1
# Naturally we get a list of tupples
tupples = cursor.fetchall()
cursor.close()
# We just need to turn it into a pandas dataframe
df = pd.DataFrame(tupples, columns=column_names)
return df
stock_ids = execute_query(conn, "SELECT DISTINCT id FROM security_price;")
stock_ids = [i[0] for i in stock_ids]
options = [{'label': i, 'value': i} for i in stock_ids]
app = dash.Dash(__name__, external_stylesheets=[dbc.themes.LUX], meta_tags=[
{"name": "viewport", "content": "width=device-width, initial-scale=1"}])
body = html.Div(
[dbc.Jumbotron(
[
dbc.Container(
[
html.H1("SP500", className="display-4"),
html.P(
"Compare equities in the SP500 "
"before you buy.",
className="lead",
),
html.P(
"Enter the symbols that interest you "
"and view how they compare",
),
],
fluid=True,
)
],
fluid=True,
)
, dbc.Row(dbc.Container(dcc.Dropdown(id="dynamic-dropdown", options=options, multi=True, placeholder="Select a Symbol", style={'margin-left': '20px', 'margin-right':'45px'}),fluid=True))
, dbc.Row(dbc.Col(html.Div(children = [dcc.Graph(id='output-graph', config={'displayModeBar': False}, animate=True)], style={'padding': 10})))
, dbc.Row([
(html.Div(dbc.Container(dt.DataTable(id='table_stats', style_table={'margin-left': '20px', 'margin-right':'45px'}))))], justify="center")
, dbc.Row(dbc.Col(html.Div(dbc.Alert("This is one column", color="primary", style={'margin-top' : '25px' ,'margin-bot' : '25px'}))),style={'margin-left' : '25px' ,'margin-right' : '25px'}, justify="center")
])
app.layout = html.Div([body])
@app.callback(
dash.dependencies.Output('output-graph', 'figure'),
[dash.dependencies.Input('dynamic-dropdown', 'value')])
def tickers(symbols):
if symbols == None:
raise PreventUpdate
trace1 = [] #lines for stocks
d = {} #dates
p = {} #prices
stock_info = {}
df = pd.DataFrame()
for stock in symbols:
stock_info[stock] = get_dict_resultset(f"SELECT date, close FROM security_price WHERE security_price.id = '{stock}' ;")
d[stock] = [rec['date'] for rec in stock_info[stock]]
p[stock] = [rec['close'] for rec in stock_info[stock]]
trace1.append(go.Scatter(x=d[stock],
y=p[stock],
mode='lines',
opacity=0.7,
name=stock,
textposition='bottom center'))
traces = [trace1]
data = [val for sublist in traces for val in sublist]
figure = {'data': data,
'layout': go.Layout(
colorway=["#5E0DAC", '#FF4F00', '#375CB1', '#FF7400', '#FFF400', '#FF0056'],
paper_bgcolor='rgba(0, 0, 0, 0)',
plot_bgcolor='rgba(0, 0, 0, 0)',
margin={'b': 15},
hovermode='x',
autosize=True,
title={'text': 'Stock Prices', 'font': {'color': 'black'}, 'x': 0.5},
),
}
df = pd.DataFrame()
return figure
@app.callback(
dash.dependencies.Output('table_stats', 'data'),
[dash.dependencies.Input('dynamic-dropdown', 'value')])
def statsTable(symbols):
if symbols == None:
raise PreventUpdate
else:
placeholders = ", ".join(['%s' for _ in symbols])
sql = f"""SELECT id, companyname, marketcap
, to_char(100.0*week52change,'999D99%'), week52high, week52low
, to_char(dividend_yield * 100, '99D99%'), next_earnings_date
, pe_ratio, ROUND(beta,2)
FROM security_stats
WHERE security_stats.id IN ({placeholders})
"""
final_df = postgresql_to_dataframe_v1(conn, sql, symbols, stats_col)
return dt.DataTable(data=final_df.to_dict('rows'), columns=stats_col)
Everything works fine, EXCEPT for my table in statsTable() function. I keep getting errors, I’ve tride multipe methods and now it feels like I’m doing circles when trying to make a simple table to show 
The last error I got was Error: list index out of range in my postgresql_to_dataframe_v1(conn, sql, symbols, stats_col) call.
How can I fix my code or scrap it to create a new function that will create a DataTable for stocks inputted by the user!