جمع آوری داده های سری زمانی برای بورس با سرور SQL

جمع آوری داده های نماد سهام در طی چند سال می تواند به شما امکان تجزیه و تحلیل سری زمانی را در مورد قیمت سهام دهد. در این نکته ما به نحوه بارگیری قیمت سهام برای همه سهام در طی یک دوره زمانی مشخص نگاه می کنیم و داده ها را برای تجزیه و تحلیل در سرور SQL بارگذاری می کنیم.

راه حل

یکی از متداول ترین سؤالات بین متخصصان داده در صنعت مالی این است که چگونه می توانم داده های تاریخی و حجم را برای بسیاری از نمادهای امنیتی از مالی یاهو جمع آوری کنم (https://finance. yahoo.com/). سایت مالی یاهو جالب است زیرا داده های سری زمانی آزاد را در مورد ده ها هزار اوراق بهادار از چندین بازار در سراسر جهان ارائه می دهد.

این نکته نحوه وارد کردن داده های سری زمانی یاهو را به یک جدول سرور SQL نشان می دهد.

• داده ها از طریق یک اسکریپت جمع آوری می شوند که از یک رابط ویژه برنامه نویسی پایتون برای جمع آوری سری های زمانی از یاهو مالی و سایر منابع اینترنتی استفاده می کند.
• خروجی از برنامه Python یک فایل CSV است که یک اسکریپت T-SQL در جدول سرور SQL وارد می کند.

برخی از منابع برای جمع آوری نمادهای تیکت

Yahoo Finance مقدار زیادی از داده های سری زمانی تاریخی را در مورد نمادها برای اوراق بهادار حفظ می کند ، اما دانستن همه نمادهای ردیابی شده ساده نیست.

Investexel.net یک فایل کتاب کار اکسل را با بسیاری از نمادهای امنیتی منتشر کرد. داده های لیست نماد آنها در سپتامبر 2017 جمع آوری شده است. به دانش من ، وب سایت از آن زمان لیست رایگان و در دسترس عموم خود را به روز نکرده است.

شرکت Eoddata داده های روزانه و همچنین تاریخچه قیمت و حجم تاریخی را برای نمادهای تیک تیک از مبادلات اصلی در سراسر جهان فراهم می کند. این نکته فرایندی را برای جمع آوری نمادهای تیک از Eoddata و سپس بارگیری داده های سری زمانی از Finance Yahoo برای نمادها نشان می دهد. شما یاد می گیرید که چگونه داده های سری زمانی را برای کمی بیش از 8000 نماد از مبادلات AMEX ، NASDAQ و NYSE که توسط Eoddata ردیابی شده اند ، جمع آوری کنید.

در اینجا یک صفحه نمایش از سایت Eoddata وجود دارد که از آن می توانید لیست آن از نمادهای Ticker را برای یک مبادله بارگیری کنید. جعبه انتخاب Exchange به شما امکان می دهد از بسیاری از مبادلات بین المللی ، از جمله سه مبادله مورد استفاده در این نکته استفاده کنید (بورس اوراق بهادار NASDAQ ، بورس اوراق بهادار نیویورک و بورس اوراق بهادار آمریکا). با کلیک بر روی لینک لیست نماد بارگیری ، یک فایل txt را با نماد و ستون های توضیحات برای نمادهای تیک در تبادل انتخاب شده بارگیری می کند.

در اینجا گزیده ای از لیست نمادها از داخل Notepad ویندوز آمده است. تصویر صفحه قبل و تصویر زیر ده نماد اول را به ترتیب حروف الفبا از بورس NASDAQ نشان می دهد، اما لیست کاملی از نمادها را برای دانلود یک بورس نشان می دهد. برای انجام دانلود باید عضویت استاندارد داشته باشید، اما برای عضویت استاندارد هزینه ای دریافت نمی شود.

پس از اینکه نتایج را از eoddata دانلود کردم، ستون دیگری را برای نشان دادن صرافی که نماد از آن انتخاب شده است اضافه کردم. ردیف های داده های نماد بر اساس حروف الفبا بر اساس نماد در مبادله مرتب شدند. دانلودهایی که با این نکته در دسترس هستند شامل یک فایل csv به نام EchangeSymbols. csv می باشد. شما می توانید از تکنیک دلخواه خود برای تجزیه ستون های داده های دانلود و بارگیری نتایج در جدول SQL Server استفاده کنید. این نکته به یک جدول SQL Server به نام ExchangeSymbols اشاره می کند که از فایل پر شده است. در متن کد این نکته، جدول در طرح dbo پایگاه داده for_csv_from_python قرار دارد.

در اینجا گزیده ای از دو مجموعه ده ردیفی از جدول SQL Server برای نمادهای دانلود شده است. تا زمانی که دانلود انجام شد، 8856 نماد توسط eoddata در سه صرافی ردیابی شدند.

• فهرست نمادها در تصویر زیر بیش از 2000 ردیف اول در جدول ExchangeSymbols گسترش می یابد. نمادها با AABA شروع می شوند و از طریق KZIA در بورس NASDAQ اجرا می شوند. این اولین مجموعه از نمادها است.
• دومین فهرست نمادها از eoddata (که در زیر گزیده نشده است) برای مطابقت با داده های سری زمانی Yahoo Finance با KZR در بورس NASDAQ شروع می شود و از طریق HCI در بورس نیویورک گسترش می یابد.
• فهرست سوم نمادها از eoddata (در زیر گزیده ای از آن نیست) برای مطابقت با Yahoo Finance با HCP در بورس نیویورک شروع می شود و از طریق UWM در بورس اوراق بهادار Amex گسترش می یابد.

یک اسکریپت پایتون برای ایجاد یک فایل csv با داده های قیمت و حجم تاریخی Yahoo Finance

این بخش یک اسکریپت پایتون را برای استخراج داده های قیمت و حجم تاریخی از Yahoo Finance برای هر یک از سه مجموعه نمادهای اشاره شده در بخش قبل، ارائه و توصیف می کند. پیشرفت های این نکته نسبت به نکته قبلی در درجه اول برای رسیدگی به عدم تطابق نمادها بین eoddata و Yahoo Finance و داده های نامناسب در یک یا چند ستون از مقادیر داده سری زمانی است. این بخش همچنین گزیده هایی از خروجی اسکریپت را نمایش می دهد تا نحوه تعامل کد با منابع داده اش را روشن کند.

داده های سری زمانی مالی یاهو در اولین تاریخ تجارت در سال 2009 آغاز می شود و تا 7 اکتبر 2019 اجرا می شود. هر ردیف داده های سری زمانی شامل یک نماد ، تاریخ معاملاتی ، ارزش قیمت (باز ، بالا ، پایین و نزدیک است) ، و یک مقدار حجم. داده های مالی یاهو در هر سه مجموعه نماد از 15 میلیون ردیف فراتر می رود! با این حال ، فرآیند کلی برای جمع آوری داده های سری زمانی نسبتاً سریع اجرا می شود و اجرای آن آسان است.

گلوله های زیر شما را از طریق فیلمنامه طی می کنند.

• فیلمنامه با چند خط نظر شروع می شود (آنها با #شروع می شوند). به طور کلی ، هر بلوک کد در اسکریپت قبل از یک یا چند خط اظهار نظر که خلاصه عملکرد کد است ، مقدم است.
  • بیانیه واردات برای pandas_datareader. data به یک برنامه افزودنی برای پایتون پیوند می زند که مخصوصاً برای پردازش داده های جدولی مناسب است ، مانند مواردی که در شات صفحه نمایش قبلی نمایش داده می شود. DataReader وسیله ای برای وارد کردن داده ها از منابع مختلف داده های وب پستی از جمله Finance Yahoo ارائه می دهد. بیانیه واردات نام مستعار وب را به DataReader اختصاص می دهد.
  • بیانیه واردات دوم به ماژول داخلی Python DateTime اشاره می کند ، که تکنیک هایی را برای پردازش مقادیر تاریخ و زمان ارائه می دهد.
  • نام file_out ثابت رشته "yahoo_prices_volumes_for_exchangesymbols_from_01012009_07102019. csv" است.
  • پس از جمع آوری پرونده CSV توسط اسکریپت ، نام پرونده به صورت دستی برای مجموعه منبع نمادها دوباره نامگذاری می شود. به عنوان مثال ، نام پرونده برای اولین مجموعه از نمادها به ‘yahoo_prices_volumes_for_exchangesymbols_from_01012009_07102019_aaba_kzia. csv 'اصلاح شده است.
  • یک امتحان ... به جز بیانیه در بیشترین ساختار کد در حلقه است.
    • در بلوک آزمایش ، انواع عملیات زیر انجام می شود.
      • یک تماس DataReader سعی می کند بلوک مقادیر سری زمانی را برای نماد کنونی کنونی بخواند.
      • اگر خواندن موفقیت آمیز باشد ، کد بعدی مقادیر بازگشت را به یک شیء DataFrame وارد می کند.
      • پردازش اضافی قبل از قرار دادن مقادیر برگشتی در پرونده CSV انجام می شود.
      • روش TO_CSV برای DataFrame یک فایل CSV را با محتویات DataFrame جمع می کند. مقدار حالت برای نماد Ticker تعیین می کند که آیا یک ردیف هدر قبل از اولین ردیف مقادیر سری زمانی برای یک نماد وجود دارد یا خیر.
        • ردیف هدر شامل نام ستون برای داده های سری زمانی است. ردیف هدر بلافاصله قبل از ردیف اول داده های سری زمانی برای اولین نماد در مجموعه ای از نمادها ظاهر می شود.
        • نمادهای دیگر بعد از نماد اول قبل از مقادیر سری زمانی خود ، ردیف هدر را نشان نمی دهند.
        • عبارت PATH_OUT+FILE_OUT نام مسیر و پرونده را برای پرونده CSV تعریف می کند.
        • خرابی در هر نقطه در بلوک آزمایش باعث می شود که کد بلوک آزمایشی تکمیل نشود و کنترل را به جز بلوک منتقل کند.
        • قوانین زیر قوانین پردازش خطا را تعریف می کند.
          • وقوع یک استثناء در قسمت بالای بند به جز بند چاپ می شود.
          • اگر عدم خواندن یک بلوک از مقادیر سری زمانی برای یک نماد تیک واجد شرایط برای تلاش دیگر باشد ، پس این نتیجه ویژه وارد می شود و انتقال را به بلوک آزمایش کنترل می کند.
          • اگر خرابی برای دهمین تلاش خواندن باشد ، مقدار J به 0 تنظیم می شود. کنترل همچنین به بالای حلقه در حالی که برای تلاش برای خواندن داده ها برای نماد تیکت بعدی منتقل می شود ، انتقال می یابد.
          • این دو متغیر درست قبل از بیانیه در حالی که در دو بیانیه تکالیف آغاز می شوند.
          • امتیاز I را در مقادیر تیک تیک متوالی پی در پی در نماد. مقدار I برای نماد اول 0 است و با مقدار کمتر از تعداد مقادیر تیک در نماد به پایان می رسد. مقدار من برای هر امتحان موفقیت آمیز 1 افزایش می یابد.
          • آهنگ های J Value پی در پی سعی می کنند یک سری زمانی با موفقیت خوانده شده و پردازش شده را برای یک نماد ذخیره کنند.
            • مقادیر J اجازه یک آزمایش مجدد برای تلاش برای خواندن و خروجی برای یک نماد از 0 شروع می شود و از طریق 9 اجرا می شود. مقدار J برای هر آزمایش مجدد یک نفر افزایش می یابد.
            • مقدار 10 برای J نشان می دهد که هیچگونه ترمیم دیگری مجاز نیست. این مقدار J منجر به تنظیم مجدد J به مقدار 0 و افزایش I با مقدار 1 تا نقطه در نماد تیک تیک بعدی بعدی می شود. کنترل برای پردازش نماد بعدی به بالای حلقه در حالی که می رود.

            شات صفحه نمایش بعدی برای سیزده نماد اول از مجموعه اول نمادها ، ورود به سیستم را نشان می دهد. این گزارش به طور پیش فرض در یک پنجره خروجی از برنامه بیکار که با پایتون 3 ارسال می شود ، چاپ می شود.

            • سوابق ورود به سیستم برای نمادها به صورت دستی یک رنگ پس زمینه اختصاص داده می شود تا نوع نتیجه برای یک نماد را نشان دهد.
              • سبز برای خواندن موفقیت آمیز و خروجی مقادیر سری زمانی در یک آزمایش واحد است.
              • زرد برای ردیف هایی است که نمادهایی را با بیش از 1 نشان می دهد تا به یک نتیجه موفق از مقادیر سری زمانی خود برسند.
              • قرمز برای ردیف هایی است که هیچ خواندن موفقیت آمیز را به تصویر نمی کشد و از طریق ده تلاش متوالی نمی نویسد.

              

              پس از نتیجه گیری اجرای اسکریپت Python قبلی برای مجموعه ای از نمادها ، پرونده CSV به صورت دستی تغییر نام داد. فیلمنامه پایتون برای هر یک از سه مجموعه نماد اجرا شد.

              • پرونده CSV برای مجموعه دوم نمادها به yahoo_prices_volumes_for_exchangesymbols_from_01012009_07102019_kzr_hci. csv تغییر نام داده شد.
              • پرونده CSV برای مجموعه سوم نمادها به yahoo_prices_volumes_for_exchangesymbols_from_01012009_07102019_hcp_uwm. csv تغییر نام داده شد.
              • جفت بعدی عکس های صفحه نمایش از جلسه Notepad ++ اولین و آخرین ردیف داده برای پرونده CSV ایجاد شده توسط اسکریپت Python قبلی برای اولین مجموعه از نمادها نشان می دهد.

              تصویر برای ده ردیف داده اول دارای یک ردیف از هدرهای ستون است که بلافاصله قبل از ده ردیف داده اول است.

              • ده ردیف داده در صفحه بالای صفحه برای نماد AABA است که اولین نماد در مجموعه اول نمادها است.
              • این ردیف ها با اولین تاریخ تجارت در سال 2009 ، که جمعه 2 ژانویه 2009 است ، آغاز می شود.
                • ارزش های تاریخ از طریق تاریخ معاملات پی در پی پیشرفت می کند. به یاد بیاورید که در تاریخ های آخر هفته هیچ معاملاتی مانند 2009-01-03 و 2009-01-04 وجود ندارد.
                • پرونده های CSV برای مجموعه های دوم و سوم نمادها همان طرح را برای دو عکس صفحه قبلی دنبال می کنند.

                

                

                اسکریپت T-SQL و فرآیند مهاجرت محتوای پرونده CSV به جدول سرور SQL

                در این بخش یک فرآیند دو مرحله ای برای وارد کردن و تبدیل پرونده های CSV با داده های قیمت و حجم شرح داده شده است.

                مرحله اول مقادیر فیلد CSV را به یک سرور SQL با شش ستون از مقادیر رشته و یک ستون از مقادیر تاریخ وارد می کند. استفاده از فیلدهای ورودی Sting برای محتوای پرونده CSV انتخاب شده به از بین بردن امکان داده های نامناسب برای نوع داده های باریک تر مشخص شده در سقط جنین ردیف های پرونده CSV به ردیف های جدول سرور SLQ کمک می کند.

                • مرحله دوم مقادیر رشته را به مقادیر پول و عدد صحیح تبدیل می کند به جز زمینه نماد که به طور طبیعی یک نوع داده رشته است.
                • کد T-SQL برای وارد کردن پرونده های CSV برای هر یک از سه مجموعه نماد در زیر ظاهر می شود.

                ابتدا اسکریپت دو جدول برای دریافت محتوای پرونده CSV ایجاد می کند.

                • جدول yahoo_prices_volumes_for_mssqltips_nvarchar یک نوع داده رشته را برای همه ستون ها به جز ستون تاریخ که دارای نوع داده تاریخ است ، اعلام می کند.
                  • جدول yahoo_prices_volumes_for_mssqltips دارای انواع داده های ستون بر اساس نوع داده ها در یک ستون است.
                  • ابتدا ردیف های جدول yahoo_prices_volumes_for_mssqltips_nvarchar در آماده سازی برای درج مجموعه ای از ردیف ها کوتاه می شوند.
                  • دوم ، یک عبارت درج فله محتویات yahoo_prices_volumes_for_exchangesymbols_from_01012009_0710202019_aaba_kzia. csv پرونده را به dbo. yahoo_prices_volumes_for_mssqltips_nvarchargar منتقل می کند.
                  • سوم ، محتویات جدول dbo. yahoo_prices_volumes_for_mssqltips_nvarchar در جدول dbo. yahoo_prices_volumes_msssqltips وارد می شوند. توجه داشته باشید که یک جدول بازیگران محتوای حجم را از جدول yahoo_prices_volumes_for_mssqltips_nvarchar تغییر می دهد. این تحول ، یک نگاه داده در امور مالی یاهو را به دست می آورد که در آن مقادیر ستون حجم به جای عدد عدد شناور به جای عدد صحیح برای مقادیر حجم مرتبط با برخی از نمادها تایپ می شوند.
                  • تأیید مقادیر سری زمانی

                  نکات آینده روی داده های موجود در جدول yahoo_prices_volumes_for_mssqltips در پایگاه داده for_csv_from_python حرکت می کند. با این وجود ، ممکن است ارائه برخی از سؤالات برای جدول در این نکته مفید باشد تا احساس آنچه را که از وب سایت های مالی یاهو بارگیری شده و در این نکته بارگیری شده است ، به شما ارائه دهد. شما به طور کلی می خواهید بار داده میلیون ها ردیف را با برخی از پرس و جوهای اعتبار سنجی تأیید کنید که داده هایی را که در SQL Server وارد کرده اید تأیید کنید.

                  عکس صفحه زیر سه نمایش داده ساده را نشان می دهد که داده های بارگیری شده را به روش های اساسی کشف می کنند.

                  اولین پرس و جو تعداد کل ردیف ها را در جدول yahoo_prices_volumes_for_msssqltips شمارش می کند. همانطور که مشاهده می کنید ، بیش از 15 میلیون ردیف از Yahoo Finance بارگیری شد.

                  • پرس و جو دوم تعداد نمادهای مجزا را در داده های بارگیری شده شمارش می کند. فقط 8،089 نماد مجزا در جدول قرار دارند. همانطور که در نکات بعدی یاد خواهید گرفت ، همه نمادها تعداد یکسان از ردیف داده ها را ندارند. این نتیجه از برخی از نمادها که قبل از دیگران در یک مبادله ذکر شده اند و از همه نمادها که در هر تاریخ تجارت پس از معامله در ابتدا تجارت نمی کنند ، نیست.
                  • پرس و جو سوم تعداد تاریخ های مجزا را در جدول شمارش می کند. همه تاریخ های 1/1/2009 تا 10/7/2019 در جدول نیست. به عنوان مثال ، اوراق بهادار در روزهای آخر هفته و همچنین حدود ده تعطیلات در سال تجارت نمی کنند.
                  • نمایش صفحه نمایش زیر گزیده ای از 13 نماد اول به ترتیب حروف الفبا از جدول مبادلات بارگیری شده از سایت Eoddata. همانطور که مشاهده می کنید ، جدول شامل 8،856 نماد از سه مبادله مختلف (NYSE ، AMEX و NASDAQ) است.

                  

                  شات صفحه نمایش بعدی یک پرس و جو و گزیده ای از سیزده ردیف اول را از مجموعه نتایج آن نشان می دهد. این پرس و جو نمادهای eoddata (مبادلات) را که در امور مالی یاهو نیز قرار دارند ، برمی گرداند (yahoo_prices_volumes_for_mssqltips). توجه کنید که تعداد کل ردیف ها 8،089 است. این مقدار نتیجه حاصل از مجموعه اولیه سه نمایش داده شده با عنوان count_of_total_retued_distinct_symbols است.

                  

                  آخرین عکس صفحه نمایش در این نکته ، پرس و جو دیگری و گزیده ای از سیزده ردیف اول از نتایج آن را نشان می دهد. این پرس و جو نمادهای Eoddata را که با هیچ نمادی در رابط وب سایت مالی یاهو برای پایتون مطابقت ندارد ، برمی گرداند. در مجموعه نتایج 767 ردیف کل وجود دارد. مبلغ 767 ردیف از پرس و جو زیر و 8،089 ردیف از پرس و جو قبلی با تعداد کل نمادهای برگشتی برای سه مبادله از وب سایت Eoddata مطابقت دارد از زمان تهیه این نکته.

                  

                  مراحل بعدی

                  

                  می توانید نمونه های کد را برای این نکته امتحان کنید. تمام اسکریپت های ارجاع شده به همراه یک فایل CSV دارای تمام نمادهای ارجاع شده از Eoddata در پرونده بارگیری این نکته موجود است.