{
"cells": [
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"# Installing Necessary libraries and establishing imports"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {
"scrolled": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Requirement already satisfied: pandas_datareader in c:\\users\\l__ha\\anaconda3\\lib\\site-packages (0.9.0)\n",
"Requirement already satisfied: pandas>=0.23 in c:\\users\\l__ha\\anaconda3\\lib\\site-packages (from pandas_datareader) (1.1.3)\n",
"Requirement already satisfied: requests>=2.19.0 in c:\\users\\l__ha\\anaconda3\\lib\\site-packages (from pandas_datareader) (2.24.0)\n",
"Requirement already satisfied: lxml in c:\\users\\l__ha\\anaconda3\\lib\\site-packages (from pandas_datareader) (4.6.1)\n",
"Requirement already satisfied: numpy>=1.15.4 in c:\\users\\l__ha\\anaconda3\\lib\\site-packages (from pandas>=0.23->pandas_datareader) (1.19.2)\n",
"Requirement already satisfied: pytz>=2017.2 in c:\\users\\l__ha\\anaconda3\\lib\\site-packages (from pandas>=0.23->pandas_datareader) (2020.1)\n",
"Requirement already satisfied: python-dateutil>=2.7.3 in c:\\users\\l__ha\\anaconda3\\lib\\site-packages (from pandas>=0.23->pandas_datareader) (2.8.1)\n",
"Requirement already satisfied: certifi>=2017.4.17 in c:\\users\\l__ha\\anaconda3\\lib\\site-packages (from requests>=2.19.0->pandas_datareader) (2020.6.20)\n",
"Requirement already satisfied: idna<3,>=2.5 in c:\\users\\l__ha\\anaconda3\\lib\\site-packages (from requests>=2.19.0->pandas_datareader) (2.10)\n",
"Requirement already satisfied: chardet<4,>=3.0.2 in c:\\users\\l__ha\\anaconda3\\lib\\site-packages (from requests>=2.19.0->pandas_datareader) (3.0.4)\n",
"Requirement already satisfied: urllib3!=1.25.0,!=1.25.1,<1.26,>=1.21.1 in c:\\users\\l__ha\\anaconda3\\lib\\site-packages (from requests>=2.19.0->pandas_datareader) (1.25.11)\n",
"Requirement already satisfied: six>=1.5 in c:\\users\\l__ha\\anaconda3\\lib\\site-packages (from python-dateutil>=2.7.3->pandas>=0.23->pandas_datareader) (1.15.0)\n",
"Note: you may need to restart the kernel to use updated packages.\n"
]
}
],
"source": [
"pip install pandas_datareader"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [],
"source": [
"import datetime\n",
"import pandas as pd\n",
"from pandas_datareader import data"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [],
"source": [
"pd.set_option('display.max_columns', 200)\n",
"pd.set_option('display.max_rows', 1000)\n",
"pd.set_option('display.max_colwidth',150)\n",
"pd.set_option('display.width', 2000)\n",
"pd.set_option('expand_frame_repr',True)"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [],
"source": [
"tickers =['MSFT','AAPL','GOOG','TSLA','JETS','NEE','SCHH','TDOC','VGK']"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [],
"source": [
"stockdata = data.get_quote_yahoo(tickers)"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" language region quoteType quoteSourceName triggerable currency exchange shortName longName messageBoardId exchangeTimezoneName exchangeTimezoneShortName gmtOffSetMilliseconds market esgPopulated forwardPE priceToBook sourceInterval exchangeDataDelayedBy averageAnalystRating tradeable postMarketChangePercent postMarketTime postMarketPrice postMarketChange regularMarketChange regularMarketChangePercent regularMarketTime regularMarketPrice regularMarketDayHigh regularMarketDayRange regularMarketDayLow regularMarketVolume regularMarketPreviousClose bid ask bidSize askSize fullExchangeName financialCurrency regularMarketOpen averageDailyVolume3Month averageDailyVolume10Day fiftyTwoWeekLowChange fiftyTwoWeekLowChangePercent fiftyTwoWeekRange fiftyTwoWeekHighChange fiftyTwoWeekHighChangePercent fiftyTwoWeekLow fiftyTwoWeekHigh dividendDate earningsTimestamp earningsTimestampStart earningsTimestampEnd trailingAnnualDividendRate trailingPE trailingAnnualDividendYield epsTrailingTwelveMonths epsForward epsCurrentYear priceEpsCurrentYear sharesOutstanding bookValue fiftyDayAverage fiftyDayAverageChange fiftyDayAverageChangePercent twoHundredDayAverage twoHundredDayAverageChange twoHundredDayAverageChangePercent marketCap firstTradeDateMilliseconds priceHint marketState displayName price ytdReturn trailingThreeMonthReturns trailingThreeMonthNavReturns\n",
"MSFT en-US US EQUITY Nasdaq Real Time Price True USD NMS Microsoft Corporation Microsoft Corporation finmb_21835 America/New_York EDT -14400000 us_market False 29.111376 13.615639 15 0 1.6 - Buy False -0.053482 1621378373 242.95 -0.130005 -2.099991 -0.856510 1621368003 243.08 246.4100 242.9 - 246.41 242.900 19285923 245.18 242.51 243.07 10 18 NasdaqGS USD 246.27 29225858 29676933 66.479996 0.376444 176.6 - 263.19 -20.110000 -0.076409 176.6000 263.190 1.623283e+09 1.619540e+09 1.626779e+09 1.627301e+09 2.190 33.126194 0.008932 7.338 8.35 7.79 31.204107 7.531570e+09 17.853 251.060580 -7.980576 -0.031787 231.750080 11.329926 0.048889 1.830774e+12 511108200000 2 POST Microsoft 243.08 NaN NaN NaN\n",
"AAPL en-US US EQUITY Nasdaq Real Time Price True USD NMS Apple Inc. Apple Inc. finmb_24937 America/New_York EDT -14400000 us_market False 23.292910 30.113363 15 0 2.0 - Buy False -0.152178 1621378652 124.66 -0.189995 -1.419998 -1.124573 1621368001 124.85 126.9878 124.78 - 126.9878 124.780 59794196 126.27 124.64 124.68 9 10 NasdaqGS USD 126.56 104074241 98036200 46.597496 0.595476 78.2525 - 145.09 -20.239998 -0.139500 78.2525 145.090 1.620864e+09 1.619627e+09 1.627470e+09 1.627906e+09 0.820 28.062487 0.006494 4.449 5.36 5.20 24.009617 1.668760e+10 4.146 129.547710 -4.697716 -0.036262 126.811325 -1.961327 -0.015466 2.083447e+12 345479400000 2 POST Apple 124.85 NaN NaN NaN\n",
"GOOG en-US US EQUITY Nasdaq Real Time Price True USD NMS Alphabet Inc. Alphabet Inc. finmb_29096 America/New_York EDT -14400000 us_market False 24.338863 6.720574 15 0 1.5 - Strong Buy False -0.105057 1621378422 2301.01 -2.419922 -17.979980 -0.774528 1621368001 2303.43 2343.1500 2303.16 - 2343.15 2303.160 840738 2321.41 2298.00 2303.43 8 11 NasdaqGS USD 2336.91 1473080 1384700 956.419900 0.710032 1347.01 - 2452.378 -148.948000 -0.060736 1347.0100 2452.378 NaN NaN NaN NaN NaN 30.696028 NaN 75.040 94.64 87.53 26.315891 3.235800e+08 342.743 2284.058600 19.371338 0.008481 1988.048000 315.381960 0.158639 1.532502e+12 1092922200000 2 POST Alphabet 2303.43 NaN NaN NaN\n",
"TSLA en-US US EQUITY Nasdaq Real Time Price True USD NMS Tesla, Inc. Tesla, Inc. finmb_27444752 America/New_York EDT -14400000 us_market False 92.459200 24.177650 15 0 2.8 - Hold False -0.946575 1621378635 572.40 -5.469971 1.039978 0.180292 1621368002 577.87 596.2500 563.38 - 596.25 563.380 36577942 576.83 578.36 575.43 10 11 NasdaqGS USD 568.00 35966290 36904516 420.870000 2.680700 157.0 - 900.4 -322.530030 -0.358208 157.0000 900.400 NaN 1.619467e+09 1.626779e+09 1.627301e+09 NaN 579.028000 NaN 0.998 6.25 4.53 127.565120 9.633300e+08 23.901 678.758850 -100.888855 -0.148637 673.090900 -95.220890 -0.141468 5.566795e+11 1277818200000 2 POST Tesla 577.87 NaN NaN NaN\n",
"JETS en-US US ETF Delayed Quote True USD PCX U.S. Global Jets ETF U.S. Global Jets ETF finmb_281809878 America/New_York EDT -14400000 us_market False NaN NaN 15 0 NaN False -0.263454 1621377084 26.50 -0.070000 0.020001 0.075331 1621368000 26.57 27.0000 26.47 - 27.0 26.470 4105122 26.55 26.10 26.60 31 11 NYSEArca NaN 26.68 6220327 5138950 13.466000 1.027625 13.104 - 28.98 -2.410000 -0.083161 13.1040 28.980 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 26.318857 0.251142 0.009542 23.950735 2.619265 0.109361 NaN 1430400600000 2 POST NaN 26.57 16.82 21.98 22.26\n",
"NEE en-US US EQUITY Delayed Quote True USD NYQ NextEra Energy, Inc. NextEra Energy, Inc. finmb_270586 America/New_York EDT -14400000 us_market False 26.577206 3.793556 15 0 2.0 - Buy False -0.069170 1621376946 72.24 -0.050003 0.389999 0.542419 1621368002 72.29 72.6650 71.302 - 72.665 71.302 8107720 71.90 72.01 72.97 12 8 NYSE USD 71.75 8823379 7850300 15.502502 0.272991 56.7875 - 87.69 -15.400002 -0.175619 56.7875 87.690 1.615766e+09 1.619008e+09 1.626957e+09 1.627303e+09 1.435 34.260666 0.019958 2.110 2.72 2.52 28.686508 1.961450e+09 19.056 76.555145 -4.265144 -0.055713 76.753380 -4.463379 -0.058152 1.417932e+11 99153000000 2 POST NextEra Energy 72.29 NaN NaN NaN\n",
"SCHH en-US US ETF Nasdaq Real Time Price True USD PCX Schwab U.S. REIT ETF Schwab U.S. REIT ETF finmb_118204473 America/New_York EDT -14400000 us_market False NaN NaN 15 0 NaN False 0.000000 1621370484 43.58 0.000000 0.040001 0.091872 1621368000 43.58 43.8200 43.24 - 43.82 43.240 470426 43.54 43.15 43.80 14 9 NYSEArca NaN 43.51 513453 510866 10.760002 0.327849 32.82 - 44.6 -1.019997 -0.022870 32.8200 44.600 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 43.054573 0.525429 0.012204 39.544190 4.035812 0.102058 NaN 1294929000000 2 POST NaN 43.58 17.10 17.17 17.22\n",
"TDOC en-US US EQUITY Nasdaq Real Time Price True USD NYQ Teladoc Health, Inc. Teladoc Health, Inc. finmb_22548461 America/New_York EDT -14400000 us_market False -145.197920 1.359651 15 0 2.2 - Buy False -0.043043 1621378307 139.33 -0.059998 2.029999 1.477867 1621368002 139.39 143.8200 135.2 - 143.82 135.200 2908530 137.36 139.20 140.00 10 10 NYSE USD 138.00 3889425 4509500 9.649994 0.074379 129.74 - 308.0 -168.610000 -0.547435 129.7400 308.000 NaN 1.619626e+09 1.627384e+09 1.627906e+09 NaN NaN NaN -5.944 -0.96 -2.73 -51.058610 1.545260e+08 102.519 171.329150 -31.939148 -0.186420 205.762200 -66.372210 -0.322568 2.153938e+10 1435671000000 2 POST Teladoc Health 139.39 NaN NaN NaN\n",
"VGK en-US US ETF Nasdaq Real Time Price True USD PCX Vanguard FTSEEuropean ETF Vanguard FTSE Europe Index Fund ETF Shares finmb_22939806 America/New_York EDT -14400000 us_market False NaN 1.383917 15 0 NaN False 0.000000 1621372281 67.96 0.000000 0.059998 0.088362 1621368000 67.96 68.3000 67.94 - 68.3 67.940 4373977 67.90 67.50 68.31 280 40 NYSEArca USD 68.27 4176232 7660350 22.169998 0.484167 45.79 - 68.3 -0.340004 -0.004978 45.7900 68.300 NaN NaN NaN NaN 1.578 NaN 0.023240 NaN NaN NaN NaN 2.820930e+08 49.107 65.948290 2.011711 0.030504 61.802500 6.157497 0.099632 1.917104e+10 1110465000000 2 POST NaN 67.96 9.15 11.04 10.98\n"
]
}
],
"source": [
"print(stockdata)"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {},
"outputs": [],
"source": [
"from openpyxl import load_workbook "
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {},
"outputs": [],
"source": [
"book =load_workbook('C:/Users/l__ha/Desktop/Python/Investments Market Value/Investments Market Value.xlsx')"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"<ipython-input-10-1ffefa2ed293>:1: DeprecationWarning: Call to deprecated function get_sheet_by_name (Use wb[sheetname]).\n",
" mo = book.get_sheet_by_name('Market Open') #get the sheet name\n"
]
}
],
"source": [
"mo = book.get_sheet_by_name('Market Open') #get the sheet name\n",
" #you can set the range here \n",
"for row in mo['A1:MF1000']:\n",
" for cell in row:\n",
" cell.value = None"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {
"scrolled": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" trailingAnnualDividendRate trailingPE trailingAnnualDividendYield priceEpsCurrentYear sharesOutstanding bookValue marketCap displayName price\n",
"MSFT 2.190 33.126194 0.008932 31.204107 7.531570e+09 17.853 1.830774e+12 Microsoft 243.08\n",
"AAPL 0.820 28.062487 0.006494 24.009617 1.668760e+10 4.146 2.083447e+12 Apple 124.85\n",
"GOOG NaN 30.696028 NaN 26.315891 3.235800e+08 342.743 1.532502e+12 Alphabet 2303.43\n",
"TSLA NaN 579.028000 NaN 127.565120 9.633300e+08 23.901 5.566795e+11 Tesla 577.87\n",
"JETS NaN NaN NaN NaN NaN NaN NaN NaN 26.57\n",
"NEE 1.435 34.260666 0.019958 28.686508 1.961450e+09 19.056 1.417932e+11 NextEra Energy 72.29\n",
"SCHH NaN NaN NaN NaN NaN NaN NaN NaN 43.58\n",
"TDOC NaN NaN NaN -51.058610 1.545260e+08 102.519 2.153938e+10 Teladoc Health 139.39\n",
"VGK 1.578 NaN 0.023240 NaN 2.820930e+08 49.107 1.917104e+10 NaN 67.96\n"
]
}
],
"source": [
"# Create a Pandas dataframe from the data.\n",
"df = pd.DataFrame(stockdata)\n",
"ds = print(df[['trailingAnnualDividendRate', 'trailingPE', 'trailingAnnualDividendYield', 'priceEpsCurrentYear', 'sharesOutstanding', 'bookValue', 'marketCap', 'displayName', 'price']])\n",
"\n",
"# Create a Pandas Excel writer using XlsxWriter as the engine.\n",
"writer = pd.ExcelWriter('C:/Users/l__ha/Desktop/Python/Investments Market Value/Investments Market Value.xlsx', engine='openpyxl')\n",
"writer.book = book\n",
"\n",
"# Convert the dataframe to an XlsxWriter Excel object.\n",
"writer.sheets = dict((ws.title, ws) for ws in book.worksheets)\n",
"df.to_excel(writer, sheet_name='Market Open')\n",
"\n",
" \n",
"# Close the Pandas Excel writer and output the Excel file.\n",
"writer.save()"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {},
"outputs": [],
"source": [
"import time"
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {},
"outputs": [],
"source": [
"time.sleep(3)"
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {},
"outputs": [],
"source": [
"book =load_workbook('C:/Users/l__ha/Desktop/Python/Investments Market Value/Investments Market Value.xlsx')"
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"<ipython-input-15-da822840a9d2>:1: DeprecationWarning: Call to deprecated function get_sheet_by_name (Use wb[sheetname]).\n",
" mc = book.get_sheet_by_name('Market Close') #get the sheet name\n"
]
}
],
"source": [
"mc = book.get_sheet_by_name('Market Close') #get the sheet name\n",
"\n",
" #you can set the range here \n",
"for row in mc['A1:MF1000']:\n",
" for cell in row:\n",
" cell.value = None"
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {
"scrolled": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"source": [
"# Create a Pandas dataframe from the data.\n",
"df = pd.DataFrame(stockdata)\n",
"ds = print(df[['trailingAnnualDividendRate', 'trailingPE', 'trailingAnnualDividendYield', 'priceEpsCurrentYear', 'sharesOutstanding', 'bookValue', 'marketCap', 'displayName', 'price']])\n",
"\n",
"# Create a Pandas Excel writer using XlsxWriter as the engine.\n",
"writer = pd.ExcelWriter('C:/Users/l__ha/Desktop/Python/Investments Market Value/Investments Market Value.xlsx', engine='openpyxl')\n",
"writer.book = book\n",
"\n",
"# Convert the dataframe to an XlsxWriter Excel object.\n",
"writer.sheets = dict((ws.title, ws) for ws in book.worksheets)\n",
"df.to_excel(writer, sheet_name='Market Close')\n",
"\n",
" \n",
"# Close the Pandas Excel writer and output the Excel file.\n",
"writer.save()"
]
},
{
"cell_type": "code",
"execution_count": 17,
"metadata": {},
"outputs": [
{
]
}
],
"source": [
"# trigger VBA MACRO to send out the email\n",
"#Connect to existing workbook containing VBA macro\n",
"\n",
"import xlwings as xw\n",
"wb = xw.Book(\"C:/Users/l__ha/Desktop/Python/Investments Market Value/Investments Market Value Email Macro.xlsm\")\n",
"\n",
"# Run the VBA macro named 'MacroName'\n",
"wb.macro('CopyandEmail')()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.8.5"
}
},
"nbformat": 4,
"nbformat_minor": 4
}I am new to python. I have nearly complete my first project however my .ipynb file was saved as a .py file but pycharms seems unable to run it.
When I run the script I get "Process finished with exit code 1". But the script does not actually do anything. Also, I got a nameerror code "true" is not defined") on my first attempt to run it in pycharms, but I used the suggested fix to fix it and it imported null.
I do not get any error messages or anything, when running the script since the import. What could be wrong?
