SQL Queries - Create charts by using NSE Data

    Retrieve all columns

  • This query returns all the rows and columns of NSE equity watch
    • {% for c in range(ColsCount) %}
    {{Headers[c]}}
    {% endfor %}
    {% for i in range(rowscount) %}
    {% for c in range(ColsCount) %}
    {{Totalrows[i][c]}}
    {% endfor %}
    {% endfor %}

     

     

    Open Price between 1000 and 3000

     

  • Returns the rows where open price between 1000 and 3000
  • From the resulst set it returns upto first 5 companies

    • Query:select Symbol, Open from companies where Open between 1000 and 3000 order by Open Desc Limit 5

     

    {% for c in range(SecondHeaderlen) %}
    {{SecondHeader[c]}}
    {% endfor %}
    {% for i in range(Secondrowslen) %}
    {% for c in range(SecondHeaderlen) %}
    {{secondrows[i][c]}}
    {% endfor %}
    {% endfor %}

    Comments:

     

     

     

    Top 5 Companies after excluding first two, based on High Price

     

    Query:"Select Symbol, High from companies Order by High Desc Limit 5 OFFSET 2"

  • First it sorts the HIGH column in Descending order
  • From the result set it eliminates first 2 rows and returns subsequent 5 rows

    •  

    {% for c in range(ThirdHeaderlen) %}
    {{ThirdHeader[c]}}
    {% endfor %}
    {% for i in range(Thirdrowslen) %}
    {% for c in range(ThirdHeaderlen) %}
    {{Thirdrows[i][c]}}
    {% endfor %}
    {% endfor %}

     

    Comments:

     

     

    LTP Greater than Average LTP

     

    Query: "select max(LTP), min(LTP), round(avg(LTP),2),Count(LTP),Count(*) of companies"

  • This query returns Max,Min,Average,Count,Count(*) from LTP column
    • Max
    Min
    Average
    Count
    Count*
    {% for c in range(5) %}
    {{stat[0][c]}}
    {% endfor %}

     

    Query: select Symbol, LTP from companies where LTP > {avgprice} Order by LTP desc Limit 5 '.format(avgprice = stat[0][2])

  • Returns top 5 companies of LTP, having status of greater than average LTP

    •  

    {% for c in range(FourthHeaderlen) %}
    {{FourthHeader[c]}}
    {% endfor %}
    {% for i in range(Fourthrowslen) %}
    {% for c in range(FourthHeaderlen) %}
    {{Fourthrows[i][c]}}
    {% endfor %}
    {% endfor %}

     

    Comments:

     

    Low price greater than > 1000, First five companies

     

     

    Query: select Symbol, Low from companies where Low > 1000 Order by Low desc Limit 5

  • It returns the top five rows of Low price, where Low price is greater than 1000

    •  

    {% for c in range(FifthHeaderlen) %}
    {{FifthHeader[c]}}
    {% endfor %}
    {% for i in range(Fifthrowslen) %}
    {% for c in range(FifthHeaderlen) %}
    {{Fifthrows[i][c]}}
    {% endfor %}
    {% endfor %}

     

     

     

Hi Everyone,
through this program we can accomplish below mentioned tasks:
(i)import the data from NSE by using selenium through web scrapping process
(ii)save the imported data into Sqlite3 Database
(iii)Retrive the data by using SQL queires
(iv)Create the charts by using Matplotlib
(v)Export the data into web frame work by using Flask
I did my level best to accomplish this task. If any changes required please bring to my notice. Hope this template is useful to everyone. This is Dynamic template, data will refresh\update when you import the data from NSE. Thank you.....

 

Pavan Kumar Gundlapalli