import pandas as pd  # check out from library - this is for reading in the ticker and the data frame
import xlsxwriter as writer # this is for writing out to the excel
import yfinance as yf # his is for getting data
import os  # I needed this for the output; without it I could not write

# Begin with a few flag variables
# Allow pathname to be input or to be current path name

# use_current_path = 1   # 1 is defined path and 0 is path of current file
# index_or_stocks = 1

current_directory = os.getcwd()
defined_directory = current_directory

print(" In the program; first defining path and files ")

use_current_path = 0

#use_current_path = input("\n Flag for Using Inputting Path; 0 - Use the Current Path; 1 - Input Path ..... ")
#use_current_path = int(use_current_path)

if use_current_path == 1:
   input_path = input("\n Enter the path name -- e.g. C:\\sandbox ...... ")

if use_current_path == 1: 
   bookName = "C:\\sandbox\\Stock and Economic Analysis.xlsm"   # This is the template input file you should have two slashes
   pathName = r"C:\\sandbox\\"  # need two slashes for creating a path in python
   bookName = input_path + "\\Stock and Economic Analysis.xlsm"   # This is the template input file you should have two slashes
   pathName = input_path   # need two slashes for creating a path in python

if use_current_path == 0: 
   bookName = current_directory+"\\Stock and Economic Analysis.xlsm"   # This is the template input file you should have two slashes
   pathName = current_directory  # need two slashes for creating a path in python
   
sheetName = "Read Stocks"   # define the sheet name for below

# Now work with the input file which will get the number of tickers, the tickers 

# now read the template file and find the sheet name
# define which sheet name to use to get the tickers
# puts everyting into a data frame
# First Read the debug code for pausing

workbook_data = pd.read_excel(bookName,header=None,sheet_name=sheetName)   # data frame (like multi-dimensional array, for data in sheet)

row_number = 15   # This is row 15; will subtract 1 because python starts at zero
col_number = 6  # this is for column B
debug_code = workbook_data.iloc[row_number - 1,col_number-1]   # This should be and can be a range name

print(" The current Path is " + current_directory)
if debug_code == 1: input (" Pause ... ")

# now read flag for stocks or indicies from row number 13

workbook_data = pd.read_excel(bookName,header=None,sheet_name=sheetName)   # data frame (like multi-dimensional array, for data in sheet)

row_number = 12   # This is row 12; will subtract 1 because python starts at zero
col_number = 6  # this is for column B
index_or_stocks = workbook_data.iloc[row_number - 1,col_number-1]   # This should be and can be a range name

index_or_stocks = int(index_or_stocks)

print ("\n The File and Sheet Name for Inputs with Defintion of Flag .... \n " + bookName + " with sheet " + sheetName)
print(" Flag for Selecting the Index Page or Stocks Page .....  " + str(index_or_stocks) + "\n 0 is Index; 1 is Stocks ")
# if debug_code == 1: input(" Pause .................")

# os._exit(0)   # This is how you can put an optional exit in your program with the os library installed

# now switch to a different sheet to read the rows and columns must be the same in both sheets

if index_or_stocks == 0:
   sheetName = "Yahoo Index"   # define the sheet name for below
else:
   sheetName = "Yahoo Stocks"   # define the sheet name for below   sheet_name = 

print(" Flag for Selecting the Index Page or Stocks Page .....  " + str(index_or_stocks) + " " + sheetName)
if debug_code == 1: input(" Pause .................")

# now get the sheet name code
# Read in the total rows from the spreadsheet in row 6 and column D -- D6

workbook_data = pd.read_excel(bookName,header=None,sheet_name=sheetName)

# now sue the sheet name and get information on the tickers
# Read in the total rows from the spreadsheet in row 6 and column D -- D6

row_number = 6   # will subtract 1 because python starts at zero
col_number = 4   # this is for column D

total_rows = workbook_data.iloc[row_number - 1,col_number-1]   # This should be and can be a range name

# print ("\n The Sheet Name for Inputting Tickers " + sheetName + "\n Has Total Tickers of: " + str(total_rows))
# if debug_code == 1: input(" Press any key to continue ... ") # This is for testing

row_number = 4   # will subtract 1 because python starts at zero
col_number = 4   # this is for column D

time_period = workbook_data.iloc[row_number - 1,col_number-1]   # This should be and can be a range name

print ("\n The Time Period Code in Sheet Name -- " + sheetName + " is the Period Code " + time_period + "\n")
# if debug_code == 1: input(" Press any key to continue ... ") # This is for testing

# This is the output file that goes into the sandbox directory
workbookName = 'stock_output.xlsx'  # If you use xls it does not work. This file name is called in the VBA Code

# Now with the input file and the input parameters defined, define the output file
# For the output file, you must not have the file open when running this program
# The ouput file has separate sheets for each ticker in a time series format with the name of each sheet being the ticker

# end_date = datetime.today()   # I don't use this but if you want to download specific datae you could
output_file = os.path.join(pathName, workbookName)  # Use this to put together the output file

print ("\n The Output File with Tickers in Seperate Sheets " + "\n (a temporary file that should not be open at this point): " + output_file)

# This next part puts the some output for the file name in the first sheet

data = {'col0': ['Output File'],'col1': [output_file],'col2' : [str(total_rows)]}
df = pd.DataFrame(data)
# print (df)  # please note that you subtract 1 from the row and the column
if debug_code == 1: input(" This was Data Press any key to continue ... ") # This is for testing

#df.to_excel(output_file,index=False)

# How in the hell did you know that you are supposed to indent in a loop

start_row = 10  # This is the start of the list - Implies that you should not Insert Lines
col_number = 4   # this is for column D

# index = true puts in the dates in the first column
# You can use 1mo, 1wk, 1d or minutes in the download below
# You must make sure that the output file is NOT open in excel

rows_in_loop = total_rows - 0

for i in range(rows_in_loop):
   row_count = start_row + i
   print ("\n In Loop -- Reading for Ticker Number " + str(row_count))
   tickerName = workbook_data.iloc[row_count - 1,col_number-1]   # This should be and can be a range name
   print ("\n Getting Data for the Ticker -- " + str(tickerName))
   df_yahoo = yf.download(tickerName,period="max",auto_adjust=False,interval=time_period)   # Need to first get the data
   df_yahoo.xs(tickerName,axis=1,level='Ticker')  # This puts the data on one line
   adj_close_df = pd.DataFrame() 
   adj_close_df = df_yahoo['Adj Close']
   print (adj_close_df)  # prints some of the data for each stock
   with pd.ExcelWriter(output_file, mode='a', engine='openpyxl', if_sheet_exists='overlay') as writer:
    adj_close_df.to_excel(writer, sheet_name=tickerName)   # This must be indented

# index = true puts in the dates in the first column

# outputName = "C:\sandbox\output.xlsx"  # this is for testing, the file is created below
# f = open(output_file,"r")
# f = open("D:\\myfiles\welcome.txt", "r")

# input (" Printing Output File ... ")
# adj_close_df.to_excel(output_file,sheet_name=tickerName)

# Extra Crap that did not work
# df_yahoo = yf.download(tickerName,period="max",auto_adjust=False,interval="1d")   # Need to first get the data
# df_yahoo.xs(tickerName,axis=1,level='Ticker')  # This puts the data on one line
# adj_close_df.to_excel(output_file,sheet_name=tickerName)                      
# df = pd.DataFrame(df_yahoo)
#data = {'col0': [tickerName]}
# data = df_yahoo
#df = pd.DataFrame(data)
# print (df)  # please note that you subtract 1 from the row and the column
# outputName = pathName + tickerName + ".csv"
# need to define a data frame when using pandas -- this is like a matrix I think
# df.to_excel(writer,sheetName=outputName)
# df.to_excel(outputName)
#workbook = writer.workbook
#worksheet=workbook.add_worksheet
# writer = pd.ExcelWriter(outputName,engine='xlsxwriter')
#pd.ExcelWriter(outputName)
#df.to_excel(outputName,engine=writer,index=False)
#workbook = writer.book
#worksheet = writer.sheets['test']
#writer.save
#writer.close

print ("\n\n C'est Terminer")
print ("Fertig")
print ("Terminado")