
import pandas as pd


data = pd.read_csv("AAPL.csv", index_col=0, parse_dates=True)


data.head()


data['MA10'] = data['Close'].rolling(10).mean()


data.tail()


exp1 = data['Close'].ewm(span=12, adjust=False).mean()
exp2 = data['Close'].ewm(span=26, adjust=False).mean()
data['MACD'] = macd = exp1 - exp2
data['Signal line'] = exp3 = macd.ewm(span=9, adjust=False).mean()


data.tail()


high14 = data['High'].rolling(14).max()
low14 = data['Low'].rolling(14).min()
data['%K'] = pct_k = (data['Close'] - low14)*100/(high14 - low14)
data['%D'] = pct_d = data['%K'].rolling(3).mean()


data.tail()


data = data.loc['2020-01-01':]
data = data.iloc[::-1]


data.head()


writer = pd.ExcelWriter("technical.xlsx", 
                        engine='xlsxwriter', 
                        date_format = 'yyyy-mm-dd', 
                        datetime_format='yyyy-mm-dd')

workbook = writer.book

# Create a format for a green cell
green_cell = workbook.add_format({
    'bg_color': '#C6EFCE',
    'font_color': '#006100'
})

# Create a format for a red cell
red_cell = workbook.add_format({
    'bg_color': '#FFC7CE',                            
    'font_color': '#9C0006'
})

# **
# ** MA
# **
sheet_name = 'MA10'
data[['Close', 'MA10']].to_excel(writer, sheet_name=sheet_name)
worksheet = writer.sheets[sheet_name]

# Set column width of Date
worksheet.set_column(0, 0, 15)


for col in range(1, 3):
    # Create a conditional formatted of type formula
    worksheet.conditional_format(1, col, len(data), col, {
        'type': 'formula',                                    
        'criteria': '=B2>=C2',
        'format': green_cell
    })

    # Create a conditional formatted of type formula
    worksheet.conditional_format(1, col, len(data), col, {
        'type': 'formula',                                    
        'criteria': '=B2<C2',
        'format': red_cell
    })


# Create a new chart object.
chart1 = workbook.add_chart({'type': 'line'})

# Add a series to the chart.
chart1.add_series({
        'name': 'AAPL',
        'categories': [sheet_name, 1, 0, len(data), 0],
        'values': [sheet_name, 1, 1, len(data), 1],
})

# Create a new chart object.
chart2 = workbook.add_chart({'type': 'line'})

# Add a series to the chart.
chart2.add_series({
        'name': sheet_name,
        'categories': [sheet_name, 1, 0, len(data), 0],
        'values': [sheet_name, 1, 2, len(data), 2],
})

# Combine and insert title, axis names
chart1.combine(chart2)
chart1.set_title({'name': sheet_name + " AAPL"})
chart1.set_x_axis({'name': 'Date'})
chart1.set_y_axis({'name': 'Price'})

# Insert the chart into the worksheet.
worksheet.insert_chart('E2', chart1)


# **
# ** MACD
# **

sheet_name = 'MACD'
data[['Close', 'MACD', 'Signal line']].to_excel(writer, sheet_name=sheet_name)
worksheet = writer.sheets[sheet_name]

# Set column width of Date
worksheet.set_column(0, 0, 15)

for col in range(1, 4):
    # Create a conditional formatted of type formula
    worksheet.conditional_format(1, col, len(data), col, {
        'type': 'formula',                                    
        'criteria': '=C2>=D2',
        'format': green_cell
    })

    # Create a conditional formatted of type formula
    worksheet.conditional_format(1, col, len(data), col, {
        'type': 'formula',                                    
        'criteria': '=C2<D2',
        'format': red_cell
    })

# Create a new chart object.
chart1 = workbook.add_chart({'type': 'line'})

# Add a series to the chart.
chart1.add_series({
        'name': 'MACD',
        'categories': [sheet_name, 1, 0, len(data), 0],
        'values': [sheet_name, 1, 2, len(data), 2],
})

# Create a new chart object.
chart2 = workbook.add_chart({'type': 'line'})

# Add a series to the chart.
chart2.add_series({
        'name': 'Signal line',
        'categories': [sheet_name, 1, 0, len(data), 0],
        'values': [sheet_name, 1, 3, len(data), 3],
})

# Combine and insert title, axis names
chart1.combine(chart2)
chart1.set_title({'name': sheet_name + " AAPL"})
chart1.set_x_axis({'name': 'Date'})
chart1.set_y_axis({'name': 'Value'})

# To set the labels on x axis not on 0
chart1.set_x_axis({
    'label_position': 'low',
    'num_font':  {'rotation': 45}
})

# Insert the chart into the worksheet.
worksheet.insert_chart('F2', chart1)


# **
# ** Stochastic
# **

sheet_name = 'Stochastic'
data[['Close', '%K', '%D']].to_excel(writer, sheet_name=sheet_name)
worksheet = writer.sheets[sheet_name]

# Set column width of Date
worksheet.set_column(0, 0, 15)

for col in range(1, 4):
    # Create a conditional formatted of type formula
    worksheet.conditional_format(1, col, len(data), col, {
        'type': 'formula',                                    
        'criteria': '=C2>=D2',
        'format': green_cell
    })

    # Create a conditional formatted of type formula
    worksheet.conditional_format(1, col, len(data), col, {
        'type': 'formula',                                    
        'criteria': '=C2<D2',
        'format': red_cell
    })


# Create a new chart object.
chart1 = workbook.add_chart({'type': 'line'})

# Add a series to the chart.
chart1.add_series({
        'name': '%K',
        'categories': [sheet_name, 1, 0, len(data), 0],
        'values': [sheet_name, 1, 2, len(data), 2],
})

# Create a new chart object.
chart2 = workbook.add_chart({'type': 'line'})

# Add a series to the chart.
chart2.add_series({
        'name': '%D',
        'categories': [sheet_name, 1, 0, len(data), 0],
        'values': [sheet_name, 1, 3, len(data), 3],
})

# Combine and insert title, axis names
chart1.combine(chart2)
chart1.set_title({'name': sheet_name + " AAPL"})
chart1.set_x_axis({'name': 'Date'})
chart1.set_y_axis({'name': 'Value'})

# To set the labels on x axis not on 0
chart1.set_x_axis({
    'label_position': 'low',
    'num_font':  {'rotation': 45}
})

# Insert the chart into the worksheet.
worksheet.insert_chart('F2', chart1)

# End of sheets


# Close
writer.close()

	Open	High	Low	Close	Adj Close	Volume
Date
2020-01-27	77.514999	77.942497	76.220001	77.237503	76.576187	161940000
2020-01-28	78.150002	79.599998	78.047501	79.422501	78.742477	162234000
2020-01-29	81.112503	81.962502	80.345001	81.084999	80.390747	216229200
2020-01-30	80.135002	81.022499	79.687500	80.967499	80.274246	126743200
2020-01-31	80.232498	80.669998	77.072502	77.377502	76.714989	199588400

	Open	High	Low	Close	Adj Close	Volume	MA10
Date
2021-01-20	128.660004	132.490005	128.550003	132.029999	132.029999	104319500	129.415000
2021-01-21	133.800003	139.669998	133.589996	136.869995	136.869995	120529500	130.442000
2021-01-22	136.279999	139.850006	135.020004	139.070007	139.070007	114459400	131.257001
2021-01-25	143.070007	145.089996	136.539993	142.919998	142.919998	157282400	132.344000
2021-01-26	143.600006	144.300003	141.369995	142.080002	142.080002	50388565	133.654001

	Open	High	Low	Close	Adj Close	Volume	MA10	MACD	Signal line
Date
2021-01-20	128.660004	132.490005	128.550003	132.029999	132.029999	104319500	129.415000	1.168345	1.733879
2021-01-21	133.800003	139.669998	133.589996	136.869995	136.869995	120529500	130.442000	1.653950	1.717893
2021-01-22	136.279999	139.850006	135.020004	139.070007	139.070007	114459400	131.257001	2.191061	1.812527
2021-01-25	143.070007	145.089996	136.539993	142.919998	142.919998	157282400	132.344000	2.894026	2.028827
2021-01-26	143.600006	144.300003	141.369995	142.080002	142.080002	50388565	133.654001	3.344794	2.292020

	Open	High	Low	Close	Adj Close	Volume	MA10	MACD	Signal line	%K	%D
Date
2021-01-20	128.660004	132.490005	128.550003	132.029999	132.029999	104319500	129.415000	1.168345	1.733879	58.792896	25.533726
2021-01-21	133.800003	139.669998	133.589996	136.869995	136.869995	120529500	130.442000	1.653950	1.717893	78.931506	49.802857
2021-01-22	136.279999	139.850006	135.020004	139.070007	139.070007	114459400	131.257001	2.191061	1.812527	94.209365	77.311256
2021-01-25	143.070007	145.089996	136.539993	142.919998	142.919998	157282400	132.344000	2.894026	2.028827	88.401934	87.180935
2021-01-26	143.600006	144.300003	141.369995	142.080002	142.080002	50388565	133.654001	3.344794	2.292020	83.912378	88.841226

	Open	High	Low	Close	Adj Close	Volume	MA10	MACD	Signal line	%K	%D
Date
2021-01-26	143.600006	144.300003	141.369995	142.080002	142.080002	50388565	133.654001	3.344794	2.292020	83.912378	88.841226
2021-01-25	143.070007	145.089996	136.539993	142.919998	142.919998	157282400	132.344000	2.894026	2.028827	88.401934	87.180935
2021-01-22	136.279999	139.850006	135.020004	139.070007	139.070007	114459400	131.257001	2.191061	1.812527	94.209365	77.311256
2021-01-21	133.800003	139.669998	133.589996	136.869995	136.869995	120529500	130.442000	1.653950	1.717893	78.931506	49.802857
2021-01-20	128.660004	132.490005	128.550003	132.029999	132.029999	104319500	129.415000	1.168345	1.733879	58.792896	25.533726

XlsxWriter¶

Moving Average¶

MACD¶

Stochastic Oscillator¶

Time period¶

Excel¶